I have 2 tables named markets and exchanges in mysql.
There are approximately 1.5 million records in the exchanges table.
I would like to get records from exchanges table with the highest update date of each market.
As a result of the query, I want to return a list with only one recent update for each market.
I created a query like the following with linq. But he responds very slowly.
Approximately 7-8 minutes.
How do I fix this problem?
Thanks for your answers.
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();
Markets Entity Class;
[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; }
}
Exchanges Entity Class;
[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; }
}
The query is slow because it uses currently unsupported constructs which cause client evaluation. Client evaluation is quite inefficient with big data, which is one of the reasons it will be removed in EF Core 3.0.
So the trick is to find LINQ construct which is supported (translated to SQL and executed server side). Since every EF Core version supports different constructs, I can't say exactly which one is supported in 2.0, but in latest 2.2 and in general, try following the next simple rules:
GroupBy
where possible.And always check for client evaluation warnings. In 3.0+ they will be runtime exceptions anyway, so you have to find a translatable construct.
Here are some of the functionally equivalent LINQ queries which work in 2.2.6:
(1) With Max
criteria
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) Same as (1), but with !Any
(SQL NOT EXISTS
) criteria:
where !market.Exchanges.Any(e => e.LastUpdatedDateTime < exchange.LastUpdatedDateTime)
(3) Same as (1), but with join
to Max
subquery:
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
}