Efcore 2.2- where clause runs after selection and returns false results

Question

I have this simple query:

Expression<Func<Tips, bool>> lastTipsPredicate = x => x.Status == (int)EnumGringo.LU_Status.active;

IQueryable<Tips> lastQueryBase(DbSet<Tips> t) => t.OrderByDescending(x => x.CreateDate).Take(6);
IEnumerable<Tips> latestTips = await base.GetAllByCondition(lastTipsPredicate, lastQueryBase);

This is my base repository:

public virtual async Task<IEnumerable<TEntity>> GetAllByCondition(Expression<Func<TEntity, bool>> predicate, Func<DbSet<TEntity>, IQueryable<TEntity>> baseQuery = null)
        {
            IQueryable<TEntity> q = context.Set<TEntity>();

            if (baseQuery != null)
            {
                q = baseQuery(context.Set<TEntity>());
            }

            return await q.Where(predicate).ToListAsync();
        }

This generate this sql query(From profiler):

exec sp_executesql N'SELECT [t].[ID], [t].[CreateDate], [t].[Description], [t].[InsertedByGringo], [t].[IsRecommended], [t].[IsSubscribe], [t].[LanguageType], [t].[SeoId], [t].[Slug], [t].[Status], [t].[Title], [t].[UserID], [t].[ViewCount]
FROM (
    SELECT TOP(@__p_0) [x].[ID], [x].[CreateDate], [x].[Description], [x].[InsertedByGringo], [x].[IsRecommended], [x].[IsSubscribe], [x].[LanguageType], [x].[SeoId], [x].[Slug], [x].[Status], [x].[Title], [x].[UserID], [x].[ViewCount]
    FROM [Tips] AS [x]
    ORDER BY [x].[CreateDate] DESC
) AS [t]
WHERE [t].[Status] = 1',N'@__p_0 int',@__p_0=6

Which returns only 5 records and not 6 as I expected, 1 record is filtered out because it has status!=1.

While this query is the correct one and returns the last 6 records:

SELECT top 6 [t].[ID], [t].[CreateDate], [t].[Description], [t].[InsertedByGringo], [t].[IsRecommended], [t].[IsSubscribe], [t].[LanguageType], [t].[SeoId], [t].[Slug], [t].[Status], [t].[Title], [t].[UserID], [t].[ViewCount]
FROM Tips as [t]
WHERE [t].[Status] = 1
ORDER BY [t].CreateDate DESC

How can I generate the second query instead of the first one?

Answer 1

Efcore 2.2- where clause runs after selection and returns false results

It's neither EF Core nor LINQ problem, but the way your repository method builds the LINQ query.

If you want to apply filtering (Where) first and then optionally the rest, then you should change the baseQuery func input type from DbSet<TEntity> to IQueryable<TEntity>, and the implementation as follows:

public virtual async Task<IEnumerable<TEntity>> GetAllByCondition(
    Expression<Func<TEntity, bool>> predicate,
    Func<IQueryable<TEntity>, IQueryable<TEntity>> baseQuery = null)
{
    var q = context.Set<TEntity>()
        .Where(predicate); // <-- (1)

    if (baseQuery != null)
        q = baseQuery(q); // <-- (2)

    return await q.ToListAsync();
}