Ytukyg

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]

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