Plazza Sele Plazza Sele - 26 days ago 5
C# Question

Select Distinct from multiple tables linq to sql

I have tow tables, tblItem and tblInsertLines, in tblInsertLines I have the same ItemId but with differnt ProdDate and ExpireDate, I want to get a distinct list of all items but select the first row from tblInsertLines as the first row contains the oldest ProdDate.
Any help will be appreciated. I use this code.

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

Answer

You can try

    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();
Comments