C#两个表多条件关联写法

合集 - C#开发(10)

1.金蝶云星空调用《其他入库单》的新增界面给序列号子单据体数据赋值2023-10-152.C# out参数out多个参数2023-10-233.C#中列表List的创建与使用2023-10-24

4.C#两个表多条件关联写法2023-10-30

5.C# list<T>去重2023-11-036.金蝶云星空表单插件获取基础资料的内码2023-12-017.C# Convert.ToBoolean()字符串转布尔类型问题2023-12-188.C# 两个日期比较大小2023-12-149.金蝶云星空表单插件获取复选框的值2023-12-1810.C#之linq和lamda表达式GroupBy分组拼接字符串2024-02-03

收起

 

 一、两个表实体类准备

 public class OtherIn
        {
            public string AfterOrderNo { get; set; }
            public long MaterialId { get; set; }
            public string MaterNumber { get; set; }
            public long Qty { get; set; }
            public string Note { get; set; }
        }

        public class AfterInfo
        {
            public string AfterOrderNo { get; set; }
            public long MaterialId { get; set; }
            public string MaterNumber { get; set; }
            public long AfterQty { get; set; }
            public string Note { get; set; }
        }

 

二、实体类数据初始化

List<OtherIn> getOtherInGroup = new List<OtherIn>();
            getOtherInGroup.Add(new OtherIn 
            { 
                AfterOrderNo= "SH231025001",
                MaterialId=100001,
                MaterNumber="CTG001",
                Qty=2
            });
            getOtherInGroup.Add(new OtherIn
            {
                AfterOrderNo = "SH231025001",
                MaterialId = 100002,
                MaterNumber = "CTG002",
                Qty = 3
            });
            getOtherInGroup.Add(new OtherIn
            {
                AfterOrderNo = "SH231025002",
                MaterialId = 100002,
                MaterNumber = "CTG002",
                Qty = 4
            });
            List<AfterInfo> getAfterGroup = new List<AfterInfo>();
            getAfterGroup.Add(new AfterInfo 
            {
                AfterOrderNo = "SH231025002",
                MaterialId = 100002,
                MaterNumber = "CTG002",
                AfterQty = 5
            });
            getAfterGroup.Add(new AfterInfo
            {
                AfterOrderNo = "SH231025001",
                MaterialId = 100001,
                MaterNumber = "CTG001",
                AfterQty = 1
            });

 

三、第一种：sql的左关联 

FROM a JOIN b on 组合键关联 into 临时表  from 临时表（左关联不上右边默认为空）

  var getExcessGroup = from a in getOtherInGroup
                                 join b in getAfterGroup on new { a.AfterOrderNo, a.MaterialId } equals new { b.AfterOrderNo,b.MaterialId }
                                 into result
                                 from c in result.DefaultIfEmpty()
                                 select new
                                 {
                                     AfterOrderNo = a.AfterOrderNo,
                                     MaterialId=a.MaterialId,
                                     Qty = a.Qty,
                                     AfterQty = c == null ? 0 : c.AfterQty
                                 };

 结果

 

四、第二种相当于sql的 INNER JOIN

剔除了关联不上的

FROM a FROM b where 多条件关联

 var getExcessGroup2 = from a in getOtherInGroup
                                  from b in getAfterGroup
                                  where a.AfterOrderNo == b.AfterOrderNo && a.MaterialId == b.MaterialId
                                  select new
                                  {
                                      AfterOrderNo = a.AfterOrderNo,
                                      MaterialId = a.MaterialId,
                                      Qty = a.Qty,
                                      AfterQty = b == null ? 0 : b.AfterQty
                                  };

 结果

 FROM JOIN INTO

var getExcessGroup1 = from a in getOtherInGroup
                                 join b in getAfterGroup on new { a.AfterOrderNo, a.MaterialId } equals new { b.AfterOrderNo, b.MaterialId }
                                 into result
                                 from c in result
                                 select new
                                 {
                                     AfterOrderNo = a.AfterOrderNo,
                                     MaterialId = a.MaterialId,
                                     Qty = a.Qty,
                                     AfterQty = c == null ? 0 : c.AfterQty
                                 };

 

  FROM JOIN 省略into

var getExcessGroup11 = from a in getOtherInGroup
                                  join b in getAfterGroup on new { a.AfterOrderNo, a.MaterialId } equals new { b.AfterOrderNo, b.MaterialId }
                                  select new
                                  {
                                      AfterOrderNo = a.AfterOrderNo,
                                      MaterialId = a.MaterialId,
                                      Qty = a.Qty,
                                      AfterQty = b.AfterQty
                                  };

 

结果