I'm a bit irritated by the fact that you've shown a column
moveOut
in "What you have tried" but not in your table - but the following should at least give you an idea, if its not exactly what you want.
Key points: Grouping the rows by
productId
, then in each productId-group order the rows by their
createDate
so that the latest appears first, then taking the first one and getting the
InvId
of it:
var results = joda.inventory.AsEnumerable()
.Where(row => row.vendorId == vendorId)
.GroupBy(row => row.productId)
.Select(grp => new
{
productId = grp.Key,
invId = grp.OrderByDescending(row => row.createDate).First().InvId
});
The result
results
is an enumeration (or a List, if you append a .ToList() at the end) of an "anonymous type" which holds the
productId
and the
invId
. If you need help with dealing with an "anonymous type" please leave a comment.