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

 

 一、两个表实体类准备

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

 

结果