Linq join从第二个表中获取最新值

I have two tables, let it be A and B. A is the primary table and B is the secondary. My need is to get when I join A and B the latest row from B for corresponding value of A and order by B's Primary key by using linq Table A A_id Value 1 a1 2 b1

我有两个表，让它成为A和B.A是主表，B是次表。我需要的是当我将A和B中的最新行加入A的相应值并通过使用linq来按B的主键排序时获得表A A_id值1 a1 2 b1

Table B
B_Id   values    A_id
1    123        1
2    456        1
3    789        2
4    321        2

Result should be 

A_id  name B_Id
2   b1     4
1   a1     2

My current code only returns in the reverse order. I know code has some syntax error please don't consider it

我当前的代码只以相反的顺序返回。我知道代码有一些语法错误请不要考虑它

from a in db.A
join b in db.b on a.A_Id equals b.B_Id into tem 
from b in tem.Take(1) orderby chat.Ch_IdNo descending

2 个解决方案

#1

I prefer to use Max instead of OrderByDescending/First, though I'm not sure which is more performant, or which would be best with SQL or EF.

我更喜欢使用Max而不是OrderByDescending / First，尽管我不确定哪个性能更高，哪个最适合SQL或EF。

So you can group join TableA to TableB and then find the maximum (latest?) B_Id from the joined group and return that.

因此，您可以将TableA连接到TableB，然后从连接组中找到最大（最新？）B_Id并返回。

var ans = from a in TableA
          join b in TableB on a.A_id equals b.A_id into bj
          let maxB_Id = bj.Max(b => b.B_Id)
          select new { a, b = bj.First(b => b.B_Id == maxB_Id) };

In lambda syntax:

在lambda语法中：

var ansl = TableA.GroupJoin(TableB, a => a.A_id, b => b.A_id, (a, bj) => new { a, b = bj.First(b => b.B_Id == bj.Max(b2 => b2.B_Id)) });

NOTE: This recomputes the Max per member of bj, while the let uses a hidden select to pass the value on. You can modify the lambda to use a full body to avoid this:

注意：这会重新计算bj的每个成员的最大值，而let使用隐藏的选择来传递值。您可以修改lambda以使用完整的主体来避免这种情况：

var ansl = TableA.GroupJoin(TableB, a => a.A_id, b => b.A_id, (a, bj) => {
                                var maxB_Id = bj.Max(b2 => b2.B_Id);
                                return new { a, b = bj.First(b => b.B_Id == maxB_Id) };
                            });

If you are okay with OrderByDescending you can use group join for that as well:

如果您对OrderByDescending没问题，您也可以使用组连接：

var ans2 = from a in TableA
           join b in TableB on a.A_id equals b.A_id into bj
           select new { a, b = bj.OrderByDescending(b => b.B_Id).First() };

In lambda syntax:

在lambda语法中：

var ans2l = TableA.GroupJoin(TableB, a => a.A_id, b => b.A_id, (a, bj) => new { a, b = bj.OrderByDescending(b => b.B_Id).First() });

#2

Although you didn't define it, I think with the phrase "the latest row from B for corresponding value of A" you mean that if you take all rows from B for a certain A_id, the latest one is the one with the highest value for B_Id. In your example: the row with B_Id 2 for A_Id 1, and the row with B_Id 4 for A_Id 2.

虽然您没有定义它，但我认为使用短语“B中对应A值的最新行”表示如果从B获取某个A_id的所有行，则最新的行是具有最高值的行。禁止。在您的示例中：A_Id为1的B_Id 2行，A_Id 2为B_Id 4的行。

Let's do it in two steps: first I get the latest table B items per A_id, then I join them with table A:

让我们分两步完成：首先我得到每个A_id最新的表B项，然后我用表A加入它们：

var latestTableB = tableB
    .GroupBy(b => b.A_id)
    .Select(group => group.OrderByDescending(groupElement => groupElement.B_Id)
     .First();

In words: group all elements of table B into groups with same A_id. Then sort all elements in the group by descending B_Id. The first element of the result will be the one with the latest B_Id

在单词中：将表B的所有元素分组为具有相同A_id的组。然后通过降序B_Id对组中的所有元素进行排序。结果的第一个元素是具有最新B_Id的元素

Join this with the elements of table A:

加入表A的元素：

var result = latestTableB.Join(tableA,
   tableBElement => tableBElement.A_Id,
   tableAElement => tableAElement.A_Id,
   (b, a) => new
   {
        A_Id = b.A_Id,
        name = a.Value,
        B_Id = b.B_Id,
   });

In words: join the elements from latestTableB with the elements from tableA. From every element in latestTableB take the A_Id; from every element in tableA take the A_Id. When they macth, use the matching b and a from latestTableB and tableA to create one new object with the desired properties

用文字表示：将latestTableB中的元素与tableA中的元素连接起来。从latestTableB中的每个元素取A_Id;从表A中的每个元素取A_Id。当他们macth时，使用来自latestTableB和tableA的匹配b和a来创建一个具有所需属性的新对象

TODO: concatenate these two statements into one

TODO：将这两个语句连接成一个