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.

diceguyd30 · Accepted Answer · 2010-11-08 16:27:03Z

3

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.

answered Nov 8, 2010 at 16:27

diceguyd30

2,79221 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

IAbstract Over a year ago

This looks promising. I did see where it looks like there is just a failure to materialize the data - I'm beginning to understand that. After removing DefaultIfEmpty(), the query goes through but the Owner result is blank. I stepped into the results and the Owner last name is in there, but buried. So I know I'm close...thanks.

IAbstract · Accepted Answer · 2010-11-08 19:16:51Z

2

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...

edited Nov 8, 2010 at 19:16

answered Nov 8, 2010 at 17:09

IAbstract

19.9k17 gold badges104 silver badges153 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:10:52Z

-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.

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Nov 7, 2010 at 18:51

Brian Mains

50.8k35 gold badges155 silver badges260 bronze badges

1 Comment

IAbstract Over a year ago

The problem is that I have to write my Join as an extension method. I cannot write as LINQ statement.

Collectives™ on Stack Overflow

Proper Join/GroupJoin implementation

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related