How can I run a paged query in EF Core if Skip and Take are always evaluated locally?

c# entity-framework-core linq

Question

I have an ASP.NET Core Web API which uses Entity Framework Core (version 2.0.2) to return a paged list of a data model called PhotoAlbum. To do this it builds up an IQueryable<PhotoAlbum> like this:

    var query = _context.PhotoAlbums
        .Include(album => album.SpotlightPhotoView)
        .ApplySecurity(user)
        .ApplyFilter(filter)
        .Sort(sortInfo);

Where ApplySecurity, ApplyFilter and Sort are my own extensions which apply two Where filters and an OrderBy filter respectively. Finally the code uses Skip and Take to return a specific subset of the matched data.

I was interested to see in my logs the following warnings:

The LINQ expression '"Take(__p_2)"' could not be translated and will be evaluated locally.

The LINQ expression '"Skip(__p_1)"' could not be translated and will be evaluated locally.

The LINQ expression '"where [album].Featured"' could not be translated and will be evaluated locally.

The LINQ expression '"where (([album].Complete OrElse False) OrElse False)"' could not be translated and will be evaluated locally.

Having read up on these warnings I now know that certain Linq methods in EF Core cannot be converted to SQL. These include aggregation functions like Sum and Count as well as Skip and Take.

So my first question is: What is the recommended solution if you want to achive a paged query? Is a stored procedure the option option?

My second question is: Why do I have the warnings around the Where clauses?

To ellaborate on this second question, the code which applies the [album].Featured filter looks like this...

    query = query.Where(album => album.Featured);

The code which applies the [album].Complete filter looks like this...

    query = query
        .Where(album =>
            album.Complete || filter.IncludeIncomplete
            && album.Published || filter.IncludeUnpublished);

...where filter is a simple model with a set of boolean properties defining how to filter.

And here's the actual SQL executed according to my logs (with a few columns removed from the SELECT for readability):

SELECT [album].[AlbumID], [album].[AlbumDate], [album.SpotlightPhotoView].[PhotoViewID]
FROM [PhotoAlbum] AS [album]
LEFT JOIN [PhotoView] AS [album.SpotlightPhotoView] ON [album].[SpotlightPhotoViewID] = [album.SpotlightPhotoView].[PhotoViewID]
WHERE ([album].[Complete] = 1) AND ([album].[Featured] = 1)
ORDER BY [album].[AlbumDate] DESC

It seems to have applied the Featured and Complete filters fine in spite of the warnings.

1
3
9/16/2019 4:55:44 PM

Popular Answer

I suspect the custom methods and their inputs generate expressions that can't be translated to SQL. where (([album].Complete OrElse False) OrElse False) definitely can't - there's no OrElse in SQL. OrElse is a VB.NET keyword.

where [album].Featured is another suspicious warning. Filtering by boolean properties is definitely supported in all versions. Is Featured a calculated property without the proper configuration perhaps?

Apart from that, GROUP BY and aggregate functions were added in EF Core 2.1, the latest LTS (Long Term Support) version.

Take and Skip definitely work in EF Core 2.2.6 and EF Core 3.0 Preview 8. Trying this query in LinqPad 6 :

Posts.OrderBy(p=>p.PostId).Skip(100).Take(100)

Generates this SQL query :

SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
FROM [Posts] AS [p]
ORDER BY [p].[PostId]
OFFSET @__p_0 ROWS FETCH NEXT @__p_0 ROWS ONLY
0
9/16/2019 4:04:50 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