I have a table called alerts and is related to a table called coin (coin can have many alerts). I would like to return a list of alerts but also return a subquery count of how many alerts that coin has.

Here is my query. I would love some feedback on how it can be improved.

            .Include(x => x.Coin)
            .Where(x => !x.IsComplete)
            .OrderByDescending(x => x.CreatedDateUtc)
            .Select(x => new AlertDto(x, context.Alerts.Where(y => y.CoinId == x.CoinId && !y.IsComplete).Count()))

Here is the SQL generated code. It does one for the main alerts.

exec sp_executesql N'SELECT TOP(@__p_0) [x].[AlertId], [x].[CoinId], [x].[CreatedDateUtc], [x].[EmailAddress], [x].[IsComplete], [x].[IsFake], [x].[IsUp], [x].[Threshold], [x].[ThresholdPercent], [x.Coin].[CoinId], [x.Coin].[Icon], [x.Coin].[LastPriceBtc], [x.Coin].[LastPriceUsd], [x.Coin].[LastUpdated], [x.Coin].[Name], [x.Coin].[Rank], [x.Coin].[Symbol]
FROM [Alerts] AS [x]
INNER JOIN [Coins] AS [x.Coin] ON [x].[CoinId] = [x.Coin].[CoinId]
WHERE [x].[IsComplete] = 0
ORDER BY [x].[CreatedDateUtc] DESC',N'@__p_0 int',@__p_0=100

A 100(topcount) more query db of these:

exec sp_executesql N'SELECT COUNT(*)
FROM [Alerts] AS [y0]
WHERE ([y0].[CoinId] = @_outer_CoinId) AND ([y0].[IsComplete] = 0)',N'@_outer_CoinId int',@_outer_CoinId=864
  • \$\begingroup\$ Which ef-core version is this? And what happens inside the AlertDto constructor? \$\endgroup\$ – Gert Arnold Nov 22 '17 at 8:38
  • \$\begingroup\$ constructor just takes the entity and translate it to a Dto version of the entity, nothing fancy. I have since moved on from this code, created a view that returns all totals, so one call and then i loop through finding the total for a specific alert total. I am using EF Core \$\endgroup\$ – Zoinky Nov 22 '17 at 13:57
  • \$\begingroup\$ OK, but which ef-core version? I tried similar code in 2.01 and it executed one query including a count. \$\endgroup\$ – Gert Arnold Nov 22 '17 at 14:00
  • \$\begingroup\$ You could disable client evaluation, just for diagnostics, to see which part causes the n+1 queries. \$\endgroup\$ – Gert Arnold Nov 22 '17 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.