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

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 getOtherInGroupjoin b in getAfterGroup on new { a.AfterOrderNo, a.MaterialId } equals new { b.AfterOrderNo,b.MaterialId }into resultfrom 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 getOtherInGroupfrom b in getAfterGroupwhere a.AfterOrderNo == b.AfterOrderNo && a.MaterialId == b.MaterialIdselect new{AfterOrderNo = a.AfterOrderNo,MaterialId = a.MaterialId,Qty = a.Qty,AfterQty = b == null ? 0 : b.AfterQty};

结果

写法二： FROM JOIN INTO

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

var getExcessGroup1 = from a in getOtherInGroupjoin b in getAfterGroup on new { a.AfterOrderNo, a.MaterialId } equals new { b.AfterOrderNo, b.MaterialId }into resultfrom c in resultselect new{AfterOrderNo = a.AfterOrderNo,MaterialId = a.MaterialId,Qty = a.Qty,AfterQty = c == null ? 0 : c.AfterQty};

结果

写法三： FROM JOIN 省略into

var getExcessGroup11 = from a in getOtherInGroupjoin 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};

结果