（Linq）如何从最新的行中找到最新的行 [英] (Linq) how to find the rows from a manytomany with most up-to-date

问题描述

那里，



我有一个实体关系数据表：vendor（1） - >库存（很多）< - product（1），我试图找到VendorId（A0001）所有具有最新库存记录的产品。





SQL表：

hi there,

I have a entity relationship data table: vendor(1)--> inventory(many)<-- product(1), and I try to find the VendorId("A0001")'s all products with the most up-to-date inventory records.


SQL Table:

InvId	   createDate	              vendorId     productId
1	       2016-03-11 21:11:09.677	  A0001	       1
2	       2016-03-11 21:26:09.647	  A0002	       1
3	       2016-03-11 21:26:09.647	  A0001	       1
4	       2016-03-11 21:26:43.707	  A0001	       5
5	       2016-03-11 21:45:03.013	  A0001	       1
6	       2016-03-11 21:26:09.647	  A0003	       2

任何人都可以帮我解决linq声明吗？< br $> b $ b

我尝试过：



var parents = joda.inventory。 AsEnumerable（）。Where（row => row.vendorId == vendorId）;



var childProdcut = joda.inventory.AsEnumerable（）。Where（row => parents.Any（p => p .productId == row.productId&& row.moveOut！=Y））。OrderBy（row => row.productId）.First（）;



结果有重复的productIds。

can anyone help me with the linq statement?

What I have tried:

var parents = joda.inventory.AsEnumerable().Where(row => row.vendorId == vendorId);

var childProdcut = joda.inventory.AsEnumerable().Where(row => parents.Any(p => p.productId == row.productId && row.moveOut != "Y")).OrderBy(row => row.productId).First();

The result has duplicated productIds.

推荐答案

我有点恼火，因为你已经显示了一列 moveOut 在你尝试了什么中，但不在你的桌子上 - 但如果不完全是你想要的话，以下内容至少应该给你一个想法。



要点：按 productId 对行进行分组，然后在每个productId-group中按行 createDate 以便最新出现，然后取第一个并获得 InvId ：



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

结果结果是一个枚举（或者一个List，如果你在末尾添加.ToList（））匿名类型，它包含 productId 和 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.

这篇关于（Linq）如何从最新的行中找到最新的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！