Queryable Linq Query Differences In Entity Framework

Question

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

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

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.

Answer 1

var aCnt = from a in dbContext.Approvals
    where a.id == id
    select a.Transactions.Count;

EF compiles query by itself, the above query will be compiled as select count transactions

Unlike,

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

these will select all the records from transaction and then computes the count