LINQ Outer joins Dynamic OrderBy

Question

In the Linq expression, like the following (narrows down to the relevant parts for the question):

var sites = from s in DataContext.Sites join add in DataContext.Address on s.PrimaryAddress equals add into sa from a in sa.DefaultIfEmpty() select new { s.Id, s.SiteName, PrimaryAddress = a }; 

The problem is that the control ultimately based on the GridView / LinqDataSource combination cannot correctly sort the class associated with the PrimaryAddress (ignores it). We see the same behavior for all related classes like this. Is there any way GridView can handle this? Or, alternatively, is there a way with expressions that we can process in code in dynamic OrderBy?

Additional notes: when adding .OrderBy(s => s.PrimaryAddress) we get Cannot order by type 'Address' , when doing .OrderBy(gs => gs.PrimaryBusinessAddress.AddressLine1) we get Specified method is not supported. . When sorting by LinqDataSource.OrderBy itself, it is too late ... it only sorts the records on the current page, not the general set.

For clarity, this is the address format in the grid:

 public partial class Address { public override string ToString() { return string.Format("{0} {1}{2} {3}", AddressLine1, City, !string.IsNullOrEmpty(State) && State != "None" ? ", " + State : string.Empty, !string.IsNullOrEmpty(Country) ? string.Format("({0})", Country) : string.Empty); } } 

If we could sort by AddressLine1 + City + State + Country , that would be good enough, but I'm not sure how to do it using the expression tree ... no matter which OrderBy we specify through the expressions, it returns to sorting by s.SiteName (default sorting in the grid). There is a very limited number of merged classes like this that are displayed in our grid control; having a switch key and an expression for each of them will not be a problem at all. Any thoughts on a solution or a completely different approach?

Answer 1

I use Linq for Entities in code, but I often use LINQ to SQL in LINQPad, and I am pretty familiar with it. I'm not quite sure that I understand where you ran into difficulties, but I think the following should work:

 var sites = from s in DataContext.Sites orderby s.PrimaryAddress.AddressLine1, s.PrimaryAddress.City, s.PrimaryAddress.State, s.PrimaryAddress.Country select new { s.Id, s.SiteName, s.PrimaryAddress }; 

Let me know if there is something that I do not understand.

Update

I am not sure why this does not work for you. I just did the following in LINQPad (LINQ to SQL mode):

 from p in person orderby p.clue_type.clue_type_id, p.clue_type.clue_type select new { p.person_id, p.clue_type } 

All results have clue_type = null. LINQ to SQL simply treats null references as values ​​with all-null properties. Here is the generated SQL:

 SELECT TOP (10) [t0].[person_id], [t2].[test], [t2].[clue_type_id], [t2].[clue_type] FROM [person] AS [t0] LEFT OUTER JOIN ( SELECT 1 AS [test], [t1].[clue_type_id], [t1].[clue_type] FROM [clue_types] AS [t1] ) AS [t2] ON [t2].[clue_type_id] = [t0].[clue_type_id] ORDER BY [t2].[clue_type_id], [t2].[clue_type] 

Pay attention to the LEFT OUTER JOIN. Would it not do what you ask for?

Update 2

Creating a dynamic query can be quite complicated, depending on how dynamically you create it. Here is one solution if you want to be able to order on any of the properties that you return, based on the string value that is passed to your method:

 public class SiteDisplayInfo { public int Id {get;set;} public string SiteName {get;set;} public string PrimaryAddress {get;set;} public static readonly Dictionary<string, Func<IQueryable<Site>, IOrderedQueryable<Site>>> OrderByFuncs = new Dictionary<string, Func<IQueryable<Site>, IOrderedQueryable<Site>>> { {"Id", q => q.OrderBy(s => s.Id)}, {"SiteName", q => q.OrderBy(s => s.SiteName)}, {"PrimaryAddress", q => q.OrderBy(s => s.PrimaryAddress.AddressLine1) .ThenBy(s => s.PrimaryAddress.City)} }; } ... public IEnumerable<SiteDisplayInfo> GetSites(string orderByString) { IQueryable<Site> sites = DataBase.Sites; if (orderByString != null && SiteDisplayInfo.OrderByFuncs.ContainsKey(orderByString)) { sites = SiteDisplayInfo.OrderByFuncs[orderByString](sites); } var query = from s in sites select new SiteDisplayInfo { Id = s.Id, SiteName = s.SiteName, PrimaryAddress = s.PrimaryAddress.AddressLine1 + s.PrimaryAddress.City }; return query.ToList(); } 

There are several other ways to do something like this, but it gives you an idea.

Answer 2

I just tried a similar setup with what you have, this should work:

 var sites = from s in dc.Sites join addr in dc.Addresses on s.PrimaryAddress equals addr into sa from a in sa.DefaultIfEmpty() orderby a.AddressLine1, a.City, a.State, a.Country /* insert orderby here */ select new { s.Id, s.SiteName, PrimaryAddress = a }; 

If you want to change the order orientation, just add a descent to each column:

 orderby a.AddressLine1 descending, a.City descending, a.State descending, a.Country descending 

Although this approach works, it is not entirely dynamic. You will need to rewrite the query to sort in a different column orientation or order.

If this is what you want, I would recommend using either a method approach with some additional fixtures or a fully dynamic approach using dynamic OrderBy, which allows you to specify the column name as a literal. For the latter, check out this topic and this blogpost .

Answer 3

When adding .OrderBy (s => s.PrimaryAddress), we get I can’t order by type 'Address'

This is because PrimaryAddress exists only in code. The LINQ statement is about to go out and get all the lines to create the address, and then combine them into code. Have you tried some form of .OrderBy (a => a.Address)? Namely, referring to the columns of the subtable instead of your derived name.