C# – LINQ Outer Joins Dynamic OrderBy

cgridviewlinqlinq-to-sqllinqdatasource

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?

Best Answer

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.

Related Topic