适当的加入/群组加入实施 [英] Proper Join/GroupJoin implementation

问题描述

我一直在试图与加入和群组加入法工作。这个问题似乎很简单。鉴于表A 和表B 的数据地图，使得：

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

...我使用表A 作为我的主表，并想加入的一个字段，客户ID 的表B 检索 [表B]。[姓氏] 。

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

应该不是什么难事，但我有困难取得成果才能正常工作。 TableA的有，我想不管匹配的客户ID表B中的记录。听起来像左连接 - 所以，这里阅读，我模仿什么@tvanfosson建议：

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:

无法格式化节点OptionalValue   执行的SQL。

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

我相信这是在下面一行code：

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.

推荐答案

首先... @布赖恩让我开始走上了正轨。这里是解决方案：

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

这篇关于适当的加入/群组加入实施的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！