Linq完全外部联接，数据表中有NULL记录C# [英] Linq full outer join with NULL records C# from datatables

问题描述

请有人帮忙吗?我需要在Extn_In_Call_Records = Extn_Number上返回一个表，并且如果任何一方都不匹配，仍然会返回一个SQL外部联接，就像SQL Full Outer联接一样.我已经花了几个小时研究这个，但无法正常工作！！如果删除联合，我可以使代码在下面工作，但随后它仅返回匹配的结果.数据表是从MYSQL填充的.任何帮助都会很棒.

Please can someone help? I need to return a table on Extn_In_Call_Records = Extn_Number and if either side does not match still return a calue just like a SQL Full Outer join. I have spent hours looking at this but cant get it to work!! I can get the code to work below if I remove the union but then it only returns matched results. The Datatable is being populated from MYSQL. Any help woud be great.

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

推荐答案

每个 LINQ-完全外部联接，执行完整外部联接的最简单方法是将两个左联接合并在一起. LINQ中的左联接(使用扩展方法语法)采用以下形式:

Per LINQ - Full Outer Join, the easiest way to perform a full outer join is to union two left joins. A left-join in LINQ (using the extension method syntax) takes the form:

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

这篇关于Linq完全外部联接，数据表中有NULL记录C#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！