选择不同于多个表linq到sql
本文关键字:linq sql 不同于 选择 | 更新日期: 2023-09-27 18:16:27
我有两个表,tblItem和tblInsertLines,在tblInsertLines中,我有相同的ItemId,但有不同的ProdDate和ExpireDate,我想获得所有项目的不同列表,但选择第一行从tblInsertLines作为第一行包含最古老的ProdDate。任何帮助都将不胜感激。我使用这个代码。
public static List<Item> getItemList()
{
using (var db = new AWarehouseDataClassesDataContext())
{
var list = (from i in db.tblItems
join e in db.tblInsertLines on i.ItemId equals e.ItemId
orderby i.NameE
select new Item
{
code = i.Code,
itemId = i.ItemId,
lastUpdate = i.LastUpdate,
nameA = i.NameA,
nameE = i.NameE,
qty = i.Qty,
prodDate = e.ProdDate,
expireDate = e.ExpireDate,
updatedBy = i.UpdatedBy
}).Distinct();
return list.ToList();
}
}
你可以试试
var list= (from i in db.tblItems
join e in db.tblInsertLines on i.ItemId equals e.ItemId
where e.counter > 0
orderby i.NameE
group new { i, e } by e.ItemId into g
select new Item
{
code = g.First().i.Code,
itemId = g.Key,
lastUpdate = g.First().i.LastUpdate,
nameA = g.First().i.NameA,
nameE = g.First().i.NameE,
qty = g.First().i.Qty,
prodDate = g.Min(x=>x.e.ProdDate),
expireDate = g.First().e.ExpireDate,
updatedBy = g.First().i.UpdatedBy
}).ToList();