LINQ两个集合联合查询

LINQ两个集合联合查询

var pushGroup = getThisOwnerInfo.GroupBy(d => new {
//AfterNo = d["F_XXXX_AfterOrderNo"] + "",
//MaterialId = Convert.ToInt64(d["F_XXXX_MATERIALID"] + ""),
MaterialNumber = d["FMATERIALNUMBER"] + "",
UnitId = Convert.ToInt64(d["FUNITID"] + ""),
KeeperID = Convert.ToInt64(d["F_XXXX_KeeperId"] + "")
}
).Select(s => new
{
    Mater = s.Key,
    Qty = s.Sum(d => Convert.ToInt32(d["F_XXXX_CanGenaralQty"]))
}).ToList();


var getOwnerStock= GetStockInfo.Where(s => Convert.ToInt64(s["FOWNERID"]) == ownerId
&& Convert.ToInt64(s["FSTOCKID"]) == stockId
&& Convert.ToInt32(s["FBASEQTY"])>0
).ToList();
var getOwnerStockGroup= getOwnerStock.GroupBy(d => new {
//MaterialId = Convert.ToInt64(d["F_XXXX_MATERIALID"] + ""),
MaterialNumber = d["FMATERIALNUMBER"] + "",
UnitId = Convert.ToInt64(d["FBASEUNITID"] + ""),
KeeperID = Convert.ToInt64(d["FKEEPERID"] + "")
}
).Select(s => new
{
    Mater = s.Key,
    StockQty = s.Sum(d => Convert.ToInt32(d["FBASEQTY"]))
}).ToList();

集合一

集合二

写法一

 var getHaveStock = (from a in pushGroup
                    join b in getOwnerStockGroup
                    on a.Mater equals b.Mater
                    into result
                    from c in result.DefaultIfEmpty()
                    select new
                    {
                        Mater = a.Mater,
                        Qty = a.Qty,
                        StockQty = c==null?0: c.StockQty
                    }).ToList();
getHaveStock = getHaveStock.Where(s => s.StockQty > s.Qty).ToList();

写法二

   var getHaveStock1 = (from a in pushGroup
						join b in getOwnerStockGroup
						on a.Mater equals b.Mater
						// where b.StockQty>a.Qty
						into result
						from c in result.DefaultIfEmpty()
						where (c==null?0: c.StockQty) > a.Qty
						select new
						{
							Mater = c.Mater,
							Qty = a.Qty,
							StockQty = c.StockQty
						}).ToList();

结果序列是 将左侧集合中元素 和右侧数据集中的一个或多个匹配元素相关联，如果在右侧元素中找不到与左侧元素数据集中元素相匹配的元素，则join子句会为该项产生一个空数组。

写法三

var list = (pushGroup.Join(getOwnerStockGroup, T1 => T1.Mater, T2 => T2.Mater, (T1, T2) => new {
    Mater = T1.Mater,
    Qty = T1.Qty,
    StockQty = T2.StockQty
})).ToList();