使用C#从数据表中进行Linq全外连接，包括NULL记录

Question

使用C#从数据表中进行Linq全外连接，包括NULL记录

4

请问有人可以帮忙吗？我需要返回一个表，其中Extn_In_Call_Records = Extn_Number，如果任何一边不匹配，仍然返回一个值，就像SQL的全外连接。我花了几个小时看这个问题，但是无法使它工作!! 如果我移除union，我可以让下面的代码工作，但是它只返回匹配的结果。Datatable是从MYSQL中填充的。任何帮助都将是伟大的。

            //Full Table
        DataTable fullext = new DataTable();
        fullext.Columns.Add("Extn_In_Call_Records", typeof(string));
        fullext.Columns.Add("Total_Calls", typeof(int));
        fullext.Columns.Add("Extn_Number", typeof(string));
        fullext.Columns.Add("Phys_Switch_Name", typeof(string));


        //End Full Table


        try
         {

            //Full Result


             var result = from callrc in callrecdt.AsEnumerable()
                          join physex in physextns.AsEnumerable()
                          on callrc["Extn_In_Call_Records"] equals physex["Extn_Number"]
                           .Union
                          from physex in physextns.AsEnumerable()
                          join callrc in callrecdt.AsEnumerable()
                          on physex["Extn_Number"] equals callrc["Extn_In_Call_Records"] 



                          select fullext.LoadDataRow(new object[] {
                       callrc["Extn_In_Call_Records"],
                       callrc["Total_Calls"],
                       physex["Extn_Number"] == null ? "" : physex["Extn_Number"],
                       physex["Phys_Switch_Name"] == null ? "" : physex["Phys_Switch_Name"]
                       }, false);
             result.CopyToDataTable();
             fullresult.DataSource = fullext;

查看结果

Extn_In_Call_Records    Total_Calls   Extn_Number      Phys_Switch_Name
null                    20                0                Hospital
null                    310               1                Hospital
4                       132               4                Hospital
2004                    null                null           Hospital
2006                    2               2006           Hospital

- Matthew Ringsell

请参考以下两个链接了解 LINQ 中“outer”概念的一般用法(https://dev59.com/3nNA5IYBdhLWcg3wBo9m?rq=1)，以及全外连接的实现方式(https://dev59.com/zG035IYBdhLWcg3wVeXn?rq=1)。 - user2864740

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Zack Butcher · Accepted Answer

根据LINQ - Full Outer Join，执行完全外连接的最简单方法是将两个左连接联合起来。在LINQ中，使用扩展方法语法进行左连接的形式如下：

var leftJoined = from left in lefts
                 join right in rights
                   on left.Key equals right.Key
                 into temp
                 from newRight in temp.DefaultIfEmpty(/* default value for right */)
                 select new
                 {
                     /* use left and newRight to construct the joined object */
                 }

在您的情况下，您想要做的是：

// initialize some default elements to use later if
// they're of the same type then a single default is fine
var defaultPhysex = new {...};
var defaultCallrc = new {...};

var left = from callrc in callrecdt.AsEnumerable()
           join physex in physextns.AsEnumerable()
             on callrc["Extn_In_Call_Records"] equals physx["Extn_Number"]
           into temp
           from physex in temp.DefaultIfEmpty(defaultPhysex)
           select new 
           {
               // callrc is accessible here, as is the new physex
               Field1 = ...,
               Field2 = ...,
           }

var right = from physex in physextns.AsEnumerable()
            join callrc in callrecdt.AsEnumerable()
              on callrc["Extn_In_Call_Records"] equals physx["Extn_Number"]
            into temp
            from callrc in temp.DefaultIfEmpty(defaultCallrc)
            select new 
            {
                // physex is accessible here, as is the new callrc
                Field1 = ...,
                Field2 = ...,
            }

var union = left.Union(right);