Proper implementation of Join / GroupJoin

Question

I am trying to work with the Join and GroupJoin method. The problem seems simple. Considering TableAand TableBhow data such as:

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

... I use TableAas my main table and want to join a single field, CustomerIDin TableBfor retrieval [TableB].[LastName].

It should not be difficult, except that it is difficult for me to get results for the right job. There are records in Table A that I want, regardless of the corresponding CustomerID in TableB. Sounds like a left join - therefore, while reading here , I emulated 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, so one method creates 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;
}

Problem: I get the following error:

node 'OptionalValue' SQL.

, :

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

? GroupJoin .

Answer 1

... @ . :

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
              });

...

Answer 2

, Owner = owner.Select(o => o.LastName).DefaultIfEmpty() - , . , , :

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,
                  });

, , ( groupjoin "ToList" ), , . , , , , .

Answer 3

: http://msdn.microsoft.com/en-us/library/bb397895.aspx

:

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) }

gj, DefaultIfEmpty - .

- , DefaultIfEmpty : . ? http://blog.appelgren.org/2008/05/15/linq-to-sql-aggregates-and-empty-results/

.