Entity Framework Core + Count with Group By

entity-framework-core linq

Question

I have a table which contains ~600k records and 33 columns. In my project I am using EF Core (2.0.1) to retrieve data from database. I am having issues with below code:

 var theCounter = (from f in _context.tblData.Take(100000)
         group f by f.TypeId into data
         select new DataDto { ID = data.Key, Count = data.Count() }).ToList();

This code is a part of REST API and when I am testing it from SOAP UI, I am gettin timeout error. When I tested the code for

Take(1000)

There are around 300 unique TypeIds. it works fine. Any ideas how I can make it work?

-- EDIT 1:

Here is what I see when debugging the code:

Microsoft.EntityFrameworkCore.Query:Warning: Query: '(from TblData <generated>_1 in DbSet<TblData> select [<generated>_1]).Take(__p_0)' uses a row limiting operation (Skip/Take) without OrderBy which may lead to unpredictable results.
Microsoft.EntityFrameworkCore.Query:Warning: Query: '(from TblData <generated>_1 in DbSet<TblData> select [<generated>_1]).Take(__p_0)' uses a row limiting operation (Skip/Take) without OrderBy which may lead to unpredictable results.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'GroupBy([f].TypeId, [f])' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'GroupBy([f].TypeId, [f])' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Count()' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (131ms) [Parameters=[@__p_0='?'], CommandType='Text', CommandTimeout='30']
SELECT [t2].[Id], [t2].[at], [t2].[add], [t2].[AddDate], [t2].[aftc], [t2].[aftcd], [t2].[aid], [t2].[afl], [t2].[prdid], [t2].[cid], [t2].[TypeId], [t2].[env], [t2].[ext], [t2].[extddcode], [t2].[fn], [t2].[fn], [t2].[fic], [t2].[gid], [t2].[grp], [t2].[hnm], [t2].[IP], [t2].[icid], [t2].[ln], [t2].[lg], [t2].[pcid], [t2].[ret], [t2].[rts], [t2].[rnam], [t2].[sled], [t2].[seq], [t2].[sid], [t2].[styp]
FROM (
    SELECT TOP(@__p_0) [t1].[Id], [t1].[at], [t1].[add], [t1].[AddDate], [t1].[aftc], [t1].[aftcd], [t1].[aid], [t1].[afl], [t1].[prdid], [t1].[cid], [t1].[TypeId], [t1].[env], [t1].[ext], [t1].[extddcode], [t1].[fn], [t1].[fn], [t1].[fic], [t1].[gid], [t1].[grp], [t1].[hnm], [t1].[IP], [t1].[icid], [t1].[ln], [t1].[lg], [t1].[pcid], [t1].[ret], [t1].[rts], [t1].[rnam], [t1].[sled], [t1].[seq], [t1].[sid], [t1].[styp]
    FROM [TblData] AS [t1]
) AS [t2]
WHERE [t2].[TypeId] IS NOT NULL
ORDER BY [t2].[TypeId]

I think it is not translated properly. Any ideas why?

-- EDIT 2:

I have changed my queries to:

    var query = _context.TblData
        .Select(a => new {ID = a.Id, TypeId= a.TypeId})
        .Distinct();

    var q1 = query.GroupBy(p => p.TypeId)
        .Select(g => new DataDto {TypeId= g.Key, Count = g.Count()});

    return await q1.ToListAsync();

But it was translated to:

SELECT DISTINCT [a0].[Id], [a0].[TypeId] AS [TypeId]
FROM [tblData] AS [a0]
ORDER BY [a0].[TypeId]

When I checked directly in the database this query takes 14 seconds to execute. Any idea why it was not translated to something like:

SELECT DISTINCT [a0].[Id], COUNT([TypeId]) AS [TypeId]
FROM [tblData] AS [a0]
GROUP BY COUNT([a0].[Id])
ORDER BY [a0].[TypeId]
1
0
11/24/2018 3:21:56 PM

Popular Answer

I had to upgrade EF Core version to 2.1 and LINQ is now translated properly into SQL.

1
11/24/2018 3:57:24 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow