none
EF DbContext.Set<T> filtered record only RRS feed

  • Question

  • Hi,
    I'm new to EF using 4.2 I just want to know what is the best way where I can restrict EF to load only filtered data instead of pulling all the data from the db and then applying filters over it.

    I can see DbContext.Set<T>() or DbContext.Set<T>().AsQueryable(); not sure about "Where" function but it seems to be working in the same way i.e already loads all the data for any given table and then applies predicates over them wouldn't this be a major performance hit? Or am I missing something here? I don't want EF to fetch all the data from table but only filtered one. how to go about it?

    thanks


    • Edited by sheikho Monday, February 20, 2012 10:45 AM
    Monday, February 20, 2012 10:12 AM

Answers

  • Hi sheikho;

    Whe you execute DbContext.Set<T>() or DbContext.Set<T>().AsQueryable(); It creates a SQL query that will be sent to the server that selects all columns and all records something like this, SELECT *FROM T. To filter the results on the server and return the results of that filtering you can add a Where clause something like this, DbContext.Set<T>().Where( x => x.SomeColumn == 1), this query when enumerated over will return records from table T and whos column SomeColumn value is equal to 1 and no other records.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Proposed as answer by Alan_chenModerator Tuesday, February 21, 2012 8:05 AM
    • Marked as answer by sheikho Tuesday, February 21, 2012 9:46 AM
    Monday, February 20, 2012 3:22 PM

All replies

  • Hi sheikho;

    Whe you execute DbContext.Set<T>() or DbContext.Set<T>().AsQueryable(); It creates a SQL query that will be sent to the server that selects all columns and all records something like this, SELECT *FROM T. To filter the results on the server and return the results of that filtering you can add a Where clause something like this, DbContext.Set<T>().Where( x => x.SomeColumn == 1), this query when enumerated over will return records from table T and whos column SomeColumn value is equal to 1 and no other records.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Proposed as answer by Alan_chenModerator Tuesday, February 21, 2012 8:05 AM
    • Marked as answer by sheikho Tuesday, February 21, 2012 9:46 AM
    Monday, February 20, 2012 3:22 PM
  • Hi sheikho,

    @Fernando is right!

    You can use SQL Profiler to watch the generated "T-SQL" or

    var result = from e in db.MyEntities
                 where e.Id = 1
                 select e;
    
    var sql = ((System.Data.Objects.ObjectQuery)result).ToTraceString();

    There is a good method to filter in EF4.2:
    http://blogs.msdn.com/b/adonet/archive/2011/01/28/using-dbcontext-in-ef-feature-ctp5-part-3-finding-entities.aspx

    ===============

    The Find method on DbSet uses the primary key value to attempt to find an entity tracked by the context. If the entity is not found in the context then a query will be sent to the database to find the entity there. Null is returned if the entity is not found in the context or in the database.

    ===============

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 21, 2012 8:05 AM
    Moderator
  • Hi Fernando, thanks for the reply.

    I first tried

    IQueryable<T> query= DbContext.Set<T>();

    query.Where(x=>x.SomeColumn==1); this didn't work out as I couldn't see any where clause into my query through SQL profiler. However

    DbContext.Set<T>().Where(x=>x.SomeColumn==1); does infact append the where clause and fetchs the filtered records only. so far so good.

    thanks


    • Edited by sheikho Tuesday, February 21, 2012 9:54 AM
    Tuesday, February 21, 2012 9:46 AM
  • @Alan

    Thanks for the info and have a great day.

    Fernando


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, February 21, 2012 2:05 PM
  •  

    Not a problem, glad to help.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, February 21, 2012 2:07 PM
  • Hi, i had the same problem, i solved it by changing the filter type fom:

    Func<T, bool> to Expression <Func<T, bool>>

    var filterA = new Func<T, bool>((x) => x.ID == ID);
    
    // TO
    
    Expression<Func<T, bool>> exprA = x => x.ID == ID;

    Wednesday, November 20, 2019 9:02 PM