c#-正确的Join / GroupJoin实现

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

…我正在使用TableA作为我的主表,并希望加入一个字段,即TableB中的CustomerID以检索[TableB].[LastName].

应该没什么困难,除了我很难使结果正常工作.无论TableB中是否有匹配的CustomerID,TableA都有我想要的记录.听起来像是左连接-因此,阅读here时,我模仿了@tvanfosson的建议：

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

源是动态的,因此一种方法可以构建动态查询：

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

问题：我收到以下错误：

Could not format node ‘OptionalValue’
for execution as SQL.

我相信它在以下代码行中：

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

我在这里做错了什么？我必须编写GroupJoin作为扩展方法.

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

这当然不是理想的(您必须使用’ToList’实现groupjoin),并且可能会有更好的解决方案,但这对我有用.您可能需要花点时间才能使其适合您,但我希望这对您有所帮助.