Proper Join/GroupJoin implementation

Question

I have been trying to work with the Join and GroupJoin method. The problem seems simple. Given TableA and TableB as datamaps such that:

class MyDataContext : DataContext
{
    public Table<tblA> TableA;
    public Table<tblB> TableB;
}

...I am using TableA as my primary table and want to join on one field, CustomerID in TableB to retrieve [TableB].[LastName].

Should not be difficult, except that I am having difficulty getting results to work properly. TableA has records that I want regardless of a matching CustomerID in TableB. Sounds like a left join - so, reading here, I mimicked what @tvanfosson suggested:

// appropriately rewritten for my needs - so I thought...
private static IQueryable GetRecordsByView1(IQueryable<tblA> source)
{
    var records = source.GroupJoin(myContext.TableB,
                  info => info.CustomerID,
                  owner => owner.CustomerID,
                  (info, owner) => new
                  {
                      info.CustomerID,
                      Owner = owner.Select(o => o.LastName).DefaultIfEmpty(),
                      Store = info.Store,
                  })
                  .Select(record => new
                  {
                      record.CustomerID,
                      record.Owner,
                      record.Store,
                  });

    return records;
}

source is dynamic, such that one method builds a dynamic query:

public static void QueryStores()
{
    IQueryable<tblA> source = myContext.TableA;

    if (criteriaA)
        source = source.Where(// something);

    if (criteriaB)
        source = source.Where(// something);

    // after processing criteria logic, determine type of view
    switch (byView)
    {
        case View1:
        {
            source = GetRecordsByView1(source);
            break;
        }

        //other case blocks
    }

    myGridView.DataSource = source;
}

The problem: I am receiving the following error:

Could not format node 'OptionalValue' for execution as SQL.

I believe it is in the following line of code:

Owner = owner.Select(o => o.LastName).DefaultIfEmpty()

What am I doing wrong here? I have to write GroupJoin as an extension method.

Answer 1

Check out this left outer join example: http://msdn.microsoft.com/en-us/library/bb397895.aspx

The relevant sample:

var query = from person in people
join pet in pets on person equals pet.Owner into gj
from subpet in gj.DefaultIfEmpty()
select new { person.FirstName, PetName = (subpet == null ? String.Empty : subpet.Name) }

You can join the results into gj, then use DefaultIfEmpty to create the outer join situation, and still get the results you want.

Someone else got a similar error using DefaultIfEmpty here: Max or Default? http://blog.appelgren.org/2008/05/15/linq-to-sql-aggregates-and-empty-results/

HTH.

Answer 2

You are correct that Owner = owner.Select(o => o.LastName).DefaultIfEmpty() is the line causing your problems. The best workaround that I have come up with is something like this:

var records = source.GroupJoin(myContext.TableB,
                  info => info.CustomerID,
                  owner => owner.CustomerID,
                  (info, owner) => new { info, owner }).ToList();
records.Select(x => new
                  {
                      x.info.CustomerID,
                      Owner = x.owner.First() == null ? new string[] {} : x.owner.Select(o => o.LastName).ToArray(),
                      Store = x.info.Store,
                  })
                  .Select(record => new
                  {
                      record.CustomerID,
                      record.Owner,
                      record.Store,
                  });

It certainly isn't ideal (you have to materialize the groupjoin with 'ToList'), and there might be a better solution, but that has worked for me. You might have to play around a bit to get it to work just right for you, but I hope this helps you along your way.

Answer 3

First...@Brian got me started on the right track. Here is the solution:

var records = source
              .GroupJoin(myContext.TableB,
              info => info.CustomerID,
              owner => owner.CustomerID,
              (info, owner) => new
              {
                  info,
                  Owner = owner.Select(o => o.LastName).First()
              })
              .Select(record => new
              {
                  record.info.CustomerID,
                  record.Owner,
                  record.info.Store
              });

This provides me with the exact results desired...