Add a where clause to context.Set<T> in EntityFrameworkCore

Question

in aspnet core EntityFrameworkCore I'm developing a generic class to handle db operations and I have a Read() method that retrieves data from db like this:

            using (MyDbContext context = new MyDbContext())
            {
                var result = await context.Set<T>().ToListAsync();
                return result;
            }

Now I wold like to filter the data adding a where or something similiar, in order to be able to filter one or more Entitiy's properties, for exeample:

TypeId = 2

How can I get this?

have you used the .Where(...) extension method for IQueryable? — Daniel A. White
I'm developing a generic class to handle db operations - I often wonder why people do this — Caius Jard
thank you @DanielA.White I didn't get your point, where should I use the .where() ? And I would like to send the query with where to the db and not filtering received data after obteining the whole table from the db. Thanks again — Diego
context.Set<T> is used for add,update,delete. create a linq query using dbcontext to extract your where iqueryable or ienumerable data. — Golden Lion

Richard Deeming Richard Deeming · Accepted Answer · 2021-11-03T14:55:12

As Daniel mentioned in the comments, you need to use the Where extension method.

For example:

public async Task<List<T>> Read(Expression<Func<T, bool>> filter = null)
{
    using (MyDbContext context = new MyDbContext())
    {
        var result = context.Set<T>().AsQueryable();
        if (filter != null) result = result.Where(filter);
        return await result.ToListAsync();
    }
}

Entity Framework will convert the filter to a SQL query and perform the filtering in the database, rather than loading the entire set into memory before filtering it.

NB: This is a rather leaky abstraction. If you use any constructs in your filter parameter which aren't supported by Entity Framework, then you will get an exception at runtime.

Add a where clause to context.Set<T> in EntityFrameworkCore

1 Answers