5

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.

Improve this question
5
  • What type is Transactions of in this context: count = Transactions.Count;? Commented Aug 8, 2014 at 10:54
  • @GrantWinney Complete queries, in my ToString() function on my Approval object I spit out how many transactions are attached. I could use either one of those three snippets. Commented Aug 8, 2014 at 10:56
  • @RomanKo Transactions is an ICollection that is part of the Approval object I am editing. Entity framework picked up the foreign key relationship and created it by default. Commented Aug 8, 2014 at 10:57
  • What is the actual type of the Transactions object? That is, call Transactions.GetType() and tell us the type name. Commented Aug 8, 2014 at 11:08
  • Please see above, its just a Transaction object. EntityFramework creates an ICollection object to store them against the Approval object. Commented Aug 8, 2014 at 11:09

4 Answers 4

Reset to default
5 
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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I thought that when a IQueryable method was called it only executed the sql query?
IQueryable method returns Enumerable type, So it will first fetch all records then computes count.
That doesn't sound right, I thought the power of the IQueryable methods was that it was queried on the database and not as an enumerable object?
@ChristopherTownsend: Using AsQueryable enables deferred execution / analysis only if the underlying object actually implements IQueryable - otherwise, AsQueryable only creates a view that forwards operations to the underlying IEnumerable.
1

When you access the a.Transactions property, then you load the list of transactions (lazy loading). If you want to get the Count only, then use something like this:

dbContext.Transactions.Where(t => t.Approvals.Any(ap => ap.Id == a.Id)).Count();

where a is given Approval.

Improve this answer

Comments

1

Your first method allows the counting to take place on the database server level. It will ask the database not to return the records, but to return the amount of records found. This is the most efficient method.

This is not to say that other methods can't work as efficiently, but with the other two lines, you are not making it clear in the first place that you are retrieving transactions from a join on Approvals. Instead, in the other two lines, you take the Transactions collection just by itself and do a count on that, basically forcing the collection to be filled so it can be counted.

Improve this answer

2 Comments

I see, but shouldn't it see that the collection of Transactions is joined to the Approval object that I am in ?
How should it see that? I can't see you using any reference to the Approval object in your code ... there's no 'a.' in front of Transactions like in the first query.
1

Your first snippet causes a query to be executed on the database server. It works that because the IQueryable instance is of type ObjectQuery provided by the Entity Framework which performs the necessary translation to SQL and then execution.

The second snippet illustrates working with IEnumerable instances. Count() works on them by, in worst case, enumerating the entire collection.

In the third snippet you attempt to make the IEnumerable an IQueryable again. But the Enumerable.AsQueryable method has no way of knowing that the IEnumerable it is getting "came" from Entity Framework. The best it can do is to wrap the IEnumerable in a EnumerableQuery instance which simply dynamically compiles the expression trees given to all LINQ query operators and executes them in memory.

If you need the count to be calculated by the database server, you can either formulate the requisite query manually (that is, write what you already did in snippet one), or use the method CreateSourceQuery available to you if you're not using Code First. Note that it will really be executed on the database server, so if you have modified the collection and have not yet saved changes, the result will be different to what would be returned by calling Count directly.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.