LINQ Outer Joins Dynamic OrderBy

Question

In a Linq Statement like the following (narrowed down to 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 we have is that a control ultimately based on a GridView/LinqDataSource combination cannot sort correctly on the PrimaryAddress joined class (ignores it). We see this same behavior for all joined classes like this. Is there any way for GridView to handle this? Or alternatively, is there a way via Expressions that we can handle it in code in a 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 doing the sort on the LinqDataSource.OrderBy itself, it's too late...it only sorts the records on the current page and not the overall set.

For clarity, this is the format of the Address 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 unsure of how to do this via an expression tree...no matter what OrderBy we specify via expressions, it reverts to sorting by s.SiteName (the default sort on the grid). There are a very limited number of joined classes like this that show in our grid control, having an switch and Expression case for each would not be a problem at all. Any thoughts on a solution, or completely different approach?

Answer 1

When adding .OrderBy(s => s.PrimaryAddress) we get Cannot order by type 'Address'

That's because PrimaryAddress only exists in the code. The LINQ statement is going to be going out and getting all the rows to make an address, then merging them together in code. Have you tried some form of .OrderBy(a => a.Address) ? Namely, referencing the columns of the sub-table instead of your derived name.

Answer 2

I'm used to using Linq to Entities in code, but I use LINQ to SQL often enough in LINQPad that I've become fairly familiar with it. I'm not entirely sure I understand where you're running 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's something that I'm not understanding.

Update

I'm not sure why this isn't working 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
}

The results all had clue_type = null. LINQ to SQL just treats null references as values with all-null properties. Here's 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]

Notice the LEFT OUTER JOIN. Will this not do what you're asking for?

Update 2

Making the query dynamic might be fairly difficult, depending on how dynamic you're making it. Here's one solution if you want to be able to order by any of the properties that you're returning, based on a string value that gets passed into 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 a few other ways to do something similar, but this gives you a general idea.

Answer 3

I just tried a similar setup to 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 orientation of the orderby, just add descending to every column:

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

Although this approach works, it's not exactly dynamic. You would have to rewrite the query to sort in another orientation or column order.

If this is something you want, I would advise using either the method approach with some additional plumbing, or the fully dynamic approach using a dynamic OrderBy that allows you to specify the column name as a literal. For the latter check this topic and this blogpost.