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);
Sort are my own extensions which apply two
Where filters and an
OrderBy filter respectively. Finally the code uses
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
Count as well as
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
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);
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
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 :
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