How to find the count of an EntityCollection without having to loa

  • Thread starter Thread starter Juan Dent
  • Start date Start date
J

Juan Dent

Hi,

I have a navigation property on a class Administrator that represents a
collection of Departments (thus is an instance of EntityCollection). when I
do this:

------
admin.Department.Count
------

I get 0. I realized if I Loaded the collection then I would get the right
count but that is memory intensive. Suppose the collection were to have 1GB
of Department data!!

So, there must be a way to obtain the count without loading everything. But
also, I wouldn't consider a valid answer if it included using Entity SQL,
because the whole abstraction layer woud disappear. So there must be an
elegant method similar to Count but that works on the DB server side, without
loading the collection.

Don't you think?
 
Hello Juan Dent,

Based on my understanding, the objective is that we are trying to get the
count of a navigation collection without loading the collection from the
data base. If I have misunderstood the question, please feel free to
correct me.

In my opinion, when we access the count from the entity instance's property
as using admin.Department.Count, it always returns the local cached result.
This is the reason why we must call the Load() method firstly before we get
the right number.

To query directly against the database, we need to use LINQ to Entities
query statement. (not the Entity SQL). These query statements can be
written in two kind of ways, the operator-based and the method-based. I
take NorthWind database for example and write the following code snippet. I
also add some comments inline. Hope it is what you are looking for.

using (NorthwindEntities context = new NorthwindEntities())
{
ObjectQuery<Customers> customers = context.Customers;

//Query the server database directly using operator based statement
//return 6 on my side.
var queryWithOperator = from customer in customers
where customer.CustomerID == "ALFKI"
select new
{
customer.Orders.Count
};
Debug.Print(queryWithOperator.First().Count.ToString());

//Query the server database directly using method based statement
//return 6 on my side.
var queryWithMethod = context.Customers.Where(c => c.CustomerID ==
"ALFKI").Select(c => new
{
c.Orders.Count
}
);
Debug.Print(queryWithMethod.First().Count.ToString());

//Query by reading the instance's property when the navigation
collection is not loaded
//return 0 on my side.
Debug.Print(context.Customers.First().Orders.Count.ToString());

//Query by reading the instance's property when the navigation
collection loaded
//return 6 on my side.
if (!context.Customers.First().Orders.IsLoaded)
{
context.Customers.First().Orders.Load();
}
Debug.Print(context.Customers.First().Orders.Count.ToString());
}

Please let me know if my suggestion works for you or not. If you need
future help on this, please feel free to let me know. I will try my best to
follow up.

Have a nice day!

Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top