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.
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