c# - EF Core 和 MySql 查询太慢

Question

我在 mysql 中有 2 个名为 markets 和 exchanges 的表。
exchanges 表中大约有 150 万条记录。
我想从交易所表中获取每个市场更新日期最高的记录。作为查询的结果，我想返回一个列表，其中每个市场只有一个最近的更新。
我使用 linq 创建了如下查询。但是他 react 很慢。
大约7-8分钟。我该如何解决这个问题？

感谢您的回答。

var query = (from exchange in _context.Exchanges
             join market in _context.Markets on exchange.MarketId equals market.Id
             where market.TypeId == 1
             group exchange by exchange.MarketId into grp

             let maxdate = grp.Max(x => x.LastUpdatedDateTime)

             from exchanges in grp
             where exchanges.LastUpdatedDateTime == maxdate
             select new DtoGetAllMarketsWithLastExchanges
             {
                 Id = exchanges.MarketId,
                 Code = exchanges.Markets.Code,
                 Name = exchanges.Markets.Name,
                 LastBuyPrice = exchanges.LastBuyPrice,
                 LastSellPrice = exchanges.LastSellPrice,
                 SeoUrl = exchanges.Markets.SeoUrl,
                 Icon = exchanges.Markets.Icon,
                 LastUpdateDate = exchanges.LastUpdatedDateTime,
                 Rate = exchanges.Rate
             }).ToList();

市场实体类；

[Table("Markets")]
public partial class Markets : BaseEntity
{
    public int TypeId { get; set; }

    public string Code { get; set; }

    public string Name { get; set; }

    public byte Unit { get; set; }

    public int OrderNumber { get; set; }

    public string Icon { get; set; }

    public string SeoUrl { get; set; }

    public virtual List<Exchanges> Exchanges { get; set; }
}

交换实体类；

[Table("Exchanges")]
public partial class Exchanges : BaseEntity
{
    public int MarketId { get; set; }

    public double LastBuyPrice { get; set; }

    public double LastSellPrice { get; set; }

    public double Rate { get; set; }

    public DateTime CreatedDateTime { get; set; }

    public DateTime LastUpdatedDateTime { get; set; }

    [ForeignKey("MarketId")]
    public virtual Markets Markets { get; set; } 

}

Answer 1

查询很慢，因为它使用了当前不受支持的结构，这会导致 client evaluation .大数据的客户评估效率很低，这也是它 will be removed in EF Core 3.0 的原因之一.

所以诀窍是找到支持的 LINQ 构造(转换为 SQL 并在服务器端执行)。由于每个 EF Core 版本都支持不同的构造，我不能确切地说出 2.0 支持哪一个，但在最新的 2.2 中，一般来说，请尝试遵循以下简单规则:

• 尽可能使用导航属性而不是手动连接
• 尽可能避免 GroupBy。

并始终检查客户评估警告。在 3.0+ 中，它们无论如何都是运行时异常，所以你必须找到一个可翻译的结构。

以下是在 2.2.6 中工作的一些功能上等效的 LINQ 查询:

(1) 使用Max 条件

from market in _context.Markets
where market.TypeId == 1
from exchange in market.Exchanges
where exchange.LastUpdatedDateTime == market.Exchanges.Max(e => (DateTime?)e.LastUpdatedDateTime)
select new DtoGetAllMarketsWithLastExchanges
{
    Id = market.Id,
    Code = market.Code,
    Name = market.Name,
    LastBuyPrice = exchange.LastBuyPrice,
    LastSellPrice = exchange.LastSellPrice,
    SeoUrl = market.SeoUrl,
    Icon = market.Icon,
    LastUpdateDate = exchange.LastUpdatedDateTime,
    Rate = exchange.Rate
}

(2) 与 (1) 相同，但具有 !Any(SQL NOT EXISTS)条件:

where !market.Exchanges.Any(e => e.LastUpdatedDateTime < exchange.LastUpdatedDateTime)

(3) 与 (1) 相同，但使用 join 到 Max 子查询:

from market in _context.Markets
where market.TypeId == 1
from exchange in market.Exchanges
join lastExchange in _context.Exchanges
    .GroupBy(e => e.MarketId)
    .Select(g => new { MarketId = g.Key, Date = g.Max(e => e.LastUpdatedDateTime) })
on new { exchange.MarketId, Date = exchange.LastUpdatedDateTime }
equals new { lastExchange.MarketId, lastExchange.Date }
select new DtoGetAllMarketsWithLastExchanges
{
    Id = market.Id,
    Code = market.Code,
    Name = market.Name,
    LastBuyPrice = exchange.LastBuyPrice,
    LastSellPrice = exchange.LastSellPrice,
    SeoUrl = market.SeoUrl,
    Icon = market.Icon,
    LastUpdateDate = exchange.LastUpdatedDateTime,
    Rate = exchange.Rate
}