Categories
c# entity-framework linq sql

Queryable Linq Query Differences In Entity Framework

I have a very simple many to many table in entity framework connecting my approvals to my transactions (shown below).

enter image description here

I am trying to do a query inside the approval object to count the amount of transactions on the approval, which should be relatively easy.

If I do something like this then it works super fast.

int count;
EntitiesContainer dbContext = new EntitiesContainer ();
var aCnt = from a in dbContext.Approvals
where a.id == id
select a.Transactions.Count;
count = aCnt.First();

However when I do this

count = Transactions.Count;

or this

count = Transactions.AsQueryable<Transaction>().Count();

its exceedingly slow. I have traced the sql running on the server and it does indeed seem to be trying to load in all the transactions instead of just doing the COUNT query on the collection of Transactions.

Can anyone explain to me why?

Additional :
Here is how the EF model looks in regards to these two classes

enter image description here

UPDATE :

Thanks for all the responses, I believe where I was going wrong was to believe that the collections attached to the Approval object would execute as IQueryable. I’m going to have to execute the count against the dbContext object.

Thanks everyone.