Better Understanding EntityFramework querying and DbSet impact-CodePudding

So, I am a newbie to EntityFramework and would like some clarification on some things. I understand the DbContext such as pointing to a SQL database with a connection string. Within the DbContext, you delcare DbSet<perEachTable>that you want / plan to expose and get data from, from said SQL database.

Now, you get into LINQ, and I understand some of link such as enumerable lists of things and looking for certain qualifying where condition entries.

Now, my bigger question and how EF queries. You have a DbContext pointing to SQL-Server. It has a table of say 250k Customers. It has its primary key based on an Id (or CustomerId) which is expected. You want to query and lookup a customer based on the user's email address (common type of expected query). -- The customer table has an index on the email.

In the LINQ queries I have seen, it is referring to the DbSet of whatever table and runs a where clause, such as

using (var context = new DBCustomers())
{
    var query = context.Customer
                       .where(c => c.EMail == "[email protected]")
                       .FirstOrDefault<Customer>();
}

My mental thinking is that the entire list of DBCustomers is being pulled down from its DbSet context. Then it runs the link WHERE clause to look for email. I dont think I want to be pulling down 250k customers down every time just to LINQ iterate through them.

How is it that the WHERE does not actually use the entire dataset of customers (especially as/when it continues to grow) and just optimizes based the indexes available and does NOT actually pull everything down.

Is this just a magic black-box being applied and EF just hands you the entry (or few if other more open query) the final entries that qualified.

I have also seen instances of EF using fully written sql statements and parameterized which is what I am more accustomed to doing. Letting the SQL-engine return the data based on the explicit criteria that best matches qualifying indexes.

Appreciate clarification on the underlying operations.

CodePudding user response：

I'm not an expert about EF but, no not the whole table is requested.

It doesn't return an IEnumerable but an IQueryable it will generate an SQL query based on the linq statement and executes it on the sql-server when it is iterated by (for example a foreach loop).

You can get the SQL query by using:

var query = context.Customer.Where(x => x.Id == 1);
Console.WriteLine(query.ToString());

CodePudding user response：

EF will construct a SQL query from your query object and execute that on the server. That way only the result of the SQL query is returned.