MVC 1.0 + EF: Does db.EntitySet.where(something) still return all rows in table?

Question

In a repository, I do this:

public AgenciesDonor FindPrimary(Guid donorId) {
    return db.AgenciesDonorSet.Include("DonorPanels").Include("PriceAdjustments").Include("Donors").First(x => x.Donors.DonorId == donorId && x.IsPrimary);
}

then down in another method in the same repository, this:

AgenciesDonor oldPrimary = this.FindPrimary(donorId);

In the debugger, the resultsview shows all records in that table, but:

oldPrimary.Count();

is 1 (which it should be).

Why am I seeing all table entries retrieved, and not just 1? I thought row filtering was done in the DB.

If db.EntitySet really does fetch everything to the client, what's the right way to keep the client data-lite using EF? Fetching all rows won't scale for what I'm doing.

score 0 · Answer 1 · answered Nov 17 '09 at 21:28

0

You will see everything if you hover over the AgenciesDonorSet because LINQ to Entities (or SQL) uses delayed execution. When the query is actually executed, it is just retrieving the count.

If you want to view the SQL being generated for any query, you can add this bit of code:

var query = queryObj as ObjectQuery; //assign your query to queryObj rather than returning it immediately

if (query != null)
{
    System.Diagnostics.Trace.WriteLine(context);
    System.Diagnostics.Trace.WriteLine(query.ToTraceString());
}

answered Nov 17 '09 at 21:28

mkedobbs

4,327
2
23
28

But why would the query ever return all rows in the table, when there is only 1 row that matches the lamba predicates? That's what's bothering me. It's almost like EntitySet snapshots a copy of the entire table into EF, and then EF somehow does the filtering outside of the database. This won't work for my needs, as I have hundreads of thousands of rows. – Dale Nov 17 '09 at 22:29
1

You are causing the statement to be enumerated in the debugger, which does not reflect what is actually happening in the code. When the code executes, only one row will be retrieved from the database. – mkedobbs Dec 09 '09 at 21:46

score 0 · Answer 2 · answered Jan 09 '11 at 21:42

Entity Set does not implement IQueryable, so the extension methods that you're using are IEnumerable extension methods. See here:

http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/121ec4e8-ce40-49e0-b715-75a5bd0063dc/

I agree that this is stupid, and I'm surprised that more people haven't complained about it. The official reason:

The design reason for not making EntitySet IQueryable is because there's not a clean way to reconcile Add\Remove on EntitySet with IQueryable's filtering and transformation ability.

MVC 1.0 + EF: Does db.EntitySet.where(something) still return all rows in table?

2 Answers2