How to query by where clause with EF code first

Question

class User {
    public int Id {set;get;}
    public int ExternalId {set;get;}
}

I have this class defined. I am using code first EF.

public class FooContext : DbContext {
    public DbSet<User> Users { set; get; }
}

So when I do the following:

db.Users.ToList().where(x=>x.ExternalId == externalId);

I am curious if this does the following:

select whatever from Users where ExternalId = 'id I passed in';

or it loads all the users to the memory and do the Linq in memory?

What's the best practice to query a table with a condition (where clause)?

Solution

The ToList() call is going to cause EF to pull all of the user records back from the DB. Your call to the Where extension method will be run against the set in memory. If you change the order of your calls, it will only select the needed records from the db, like this:

db.Users.Where(x=>x.ExternalId == externalId).ToList();

This is because Entity Framework uses delayed execution. The call to the database doesn't happen until the actual records are enumerated in your calling code. Until you use the results, the query is just an expression tree waiting to be executed. If you look at the Where() method it's return type is IQueryable. When you call ToList() you are essentially converting from an expression tree (IQueryable) to a generic list of users. This requires EF to run the db query to return the results.