在Entity Framework Core中高效地加入另一张表中的最新记录

Question

在Entity Framework Core中高效地加入另一张表中的最新记录

asp.netentity-frameworklinqasp.net-coreentity-framework-core

5

我从使用PHP w/MySQL转到ASP .NET Core。

问题:

为了方便说明，假设有以下两个表：T: {ID, Description, FK}和States：{ID, ID_T, Time, State}。它们之间存在1:n的关系（ID_T引用T.ID）。

我需要获取T中所有FK特定值的记录（假设为1），以及相关的最新记录在States中（如果有）。

在SQL中可以这样写：

SELECT T.ID, T.Description, COALESCE(s.State, 0) AS 'State' FROM T
LEFT JOIN (
    SELECT ID_T, MAX(Time) AS 'Time'
    FROM States
    GROUP BY ID_T
) AS sub ON T.ID = sub.ID_T
LEFT JOIN States AS s ON T.ID = s.ID_T AND sub.Time = s.Time
WHERE FK = 1

我正在努力编写一个在LINQ（或流畅API）中有效等效的查询。到目前为止，我得到的最佳工作解决方案是：

from t in _context.T
where t.FK == 1
join s in _context.States on t.ID equals o.ID_T into _s
from s in _s.DefaultIfEmpty()
let x = new
{
    id = t.ID,
    time = s == null ? null : (DateTime?)s.Time,
    state = s == null ? false : s.State
}
group x by x.id into x
select x.OrderByDescending(g => g.time).First();

当我执行查询时，在输出窗口中检查得到的 SQL 查询内容如下：

SELECT [t].[ID], [t].[Description], [t].[FK], [s].[ID], [s].[ID_T], [s].[Time], [s].[State]
FROM [T] AS [t]
LEFT JOIN [States] AS [s] ON [T].[ID] = [s].[ID_T]
WHERE [t].[FK] = 1
ORDER BY [t].[ID]

不仅选择了比我需要的更多的列（在实际方案中有更多列）。查询中没有分组，所以我想它会从数据库中选择所有内容（而且 States 将会非常庞大），分组/过滤是在数据库外部进行的。

问题：

你会怎么做？

有没有 LINQ / Fluent API 中的高效查询？

如果没有，可以使用哪些解决方法？

原始 SQL 破坏了抽象出特定数据库技术的概念，并且在当前的 Entity Framework Core 中使用起来非常笨重（但也许这是最好的解决方案）。

对我来说，这看起来是使用数据库视图的一个很好的例子 - 再次强调，Entity Framework Core 并不真正支持它（但也许这是最好的解决方案）。

- Lukas Z.

3

“效率”和“实体框架”这两个词不搭配。 - nurdyguy

3

在 EF Core 中，group by 操作是在内存中完成的。EF Core 会按照分组键对结果进行排序，以实现流式处理和高效的 group by ，不会将任何 group by 操作发送到服务器。有关此问题的跟踪信息，请参阅 https://github.com/aspnet/EntityFramework/issues/2341。 - Smit

@Smit：谢谢。我已经找到了这篇文章：https://jonhilton.net/2016/11/09/is-entity-framework-core-production-ready/我会尝试切换到EF6。 - Lukas Z.

2个回答

2

好的。阅读这篇文章（现在已经有近一年的时间了），Smit对原问题的评论以及其他来源，似乎EF Core目前还不太适合生产使用。它无法将分组转换为SQL，因此在客户端执行，这可能会（在我这种情况下）造成严重问题。这与观察到的行为相对应（生成的SQL查询不进行分组并选择所有组中的内容）。在Linqpad中尝试LINQ查询总是会被翻译成单个SQL查询。

我已经降级到EF6，遵循这篇文章。这需要对我的模型代码和一些查询进行一些更改。在我的原始LINQ查询中将.First()更改为.FirstOrDefault()后，它可以正常工作，并且将翻译为只选择所需列的单个SQL查询。但是，生成的查询比实际需要的要复杂得多。

使用NetMage答案中的一个查询（经过小修补），结果是一个几乎与我自己原始SQL查询相同的SQL查询（只有一个比COALESCE更复杂的结构）。

var latestState = from s in _context.States
                  group s by s.ID_T into sg
                  select new { ID = sg.Key, Time = sg.Time.Max() };

var ans = from t in _context.T
          where t.FK == 1
          join sub in latestState on t.ID equals sub.ID into subj
          from sub in subj.DefaultIfEmpty()
          join s in _context.States
              on new { ID_T = t.ID, sub.Time } equals new { s.ID_T, s.Time }
              into sj
          from s in sj.DefaultIfEmpty()
          select new { t.ID, t.Description, State = (s == null ? false : s.State) };

在LINQ中，它不像我的原始SQL查询那样优雅，但语义上是相同的，并且在DB方面做了更多或更少相同的事情。

在EF6中，使用任意原始SQL查询和AFAIK数据库视图也更加方便。

这种方法最大的缺点是必须针对完整的.NET框架进行目标设置，EF6与.NET Core不兼容。

- Lukas Z.

1

我不确定State的类型，但显然你可以在LINQ查询中使用??并将其转换为SQL，但也许它可以应用于表为空的情况，因为不幸的是?.还没有被翻译。我已经将其添加到我的答案中。 - NetMage

@NetMage：谢谢！我会在周一尝试并最终更新我的答案。 - Lukas Z.

1

每当我担心复杂查询的性能时，我会在数据库中手动创建一个视图，并手动创建一个EF对象。这听起来可以节省很多时间。如果您告诉MigrationsConfig在哪里找到视图，您可以使用Code-First完成此操作 - 指令在此处：http://laboremus.ug/?p=1708 ...并且您还可以将该视图连接到其他对象。 - C.List

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- NetMage · Accepted Answer

如果您尝试将更直接的翻译应用于LINQ，会发生什么？

var latestState = from s in _context.States
                  group s by s.ID_T into sg
                  select new { ID_T = sg.Key, Time = sg.Time.Max() };

var ans = from t in _context.T
          where t.FK == 1
          join sub in latestState on t.ID equals sub.ID_T into subj
          from sub in subj.DefaultIfEmpty()
          join s in _context.States on new { t.ID, sub.Time } equals new { s.ID, s.Time } into sj
          from s in sj.DefaultIfEmpty()
          select new { t.ID, t.Description, State = (s == null ? 0 : s.State) };

显然，??运算符将转换为COALESCE并且可以正确处理空表，因此您可以将select替换为：

          select new { t.ID, t.Description, State = s.State ?? 0 };