How could I get a Relative Entity's count using EF6

Question

I have a Entity called "Client", and another Entity called "Card".

A Client may have many Cards.

My Client Entity looks like this:

public class Client{
    public virtual ICollection<Card> Cards {get; set;}
}

Now I want to show the Client data in a DataGrid in WPF, and I want to get Cards Count data,so I add a property to Client Entity, which like this:

public class Client{
    public virtual ICollection<Card> Cards {get; set;}

    public int CardCount
    {
        return Cards.Count;
    }
}

And then I query the data with Linq and Bind to view

var query = from n in db.Clients select n;

When I run the Application, I got a Exception just right on the return Cards.Count; line;

System.ObjectDisposedException
The ObjectContext instance has been disposed and can no longer be used for operations that require a connection.

So how could I correctly get the cards count?

Balázs · Accepted Answer · 2017-02-03T13:00:37.953

1

There is a way simpler method than the other answers here show. Please also realize that solutions such as

var client = db.Clients.FirstOrDefault(c=> c.Id = someid); //get a client
if (client != null)
{
    cardCount = client.Cards.Count;
}

will cause an issue called Select N+1 problem. Read up on it if interested, but in a nutshell, it means the following:

Because you are not only interested in one exact client, but you want to display N clients, you need to do one (1) query to get just the clients. Then, by doing the FirstOrDefault stuff, you are actually doing one (1) extra db roundtrip to the database per Client record, which results in an additional N * 1 = N roundtrips. What this means that, if you were to just query the Clients without any related data, you could get however many client records you like, in just one query. But by fetching related data to each of them one-by-one, you are doing way too many db roundtrips.

Here is a way to solve this issue, by using joins and projections. You can get all the data you need in a single DB access:

using (var context = GetDbContext())
{
    return context.Clients.Select(cli => new YourViewModel
    {
        Name = cli.FullName,
        // Other prop setters go here
        CardCount = cli.Cards.Count
    }).Skip((page - 1) * pageSize).Take(pageSize).ToList();
}

You might be wondering, what's the difference afterall? Well, here, you are not working with materialized objects, as others call them here, but with a DbContext. By applying the proper LINQ operators to it (note, that this works not just with DbContext, but also with any IQueryable (well obviously not if you call AsQueryable() on an already in-memory collection but whatever)), LINQ to Entities can construct a proper SQL to join the tables and project the results and therefore you fetch all required data in one go. Note that LINQ to Entities IS ABLE to translate the cli.Cards.Count into a proper SQL Count statement.

edited Feb 03 '17 at 13:00

answered Feb 03 '17 at 10:20

Balázs

2,929
2
19
34

there Also a problem here, I use the Client Entity Instance Bind directly to a Datagrid,How do I show the CardsCount Column Data if I don't have a property in my Client Entity – Gabriel Shell Feb 03 '17 at 10:28
Simple, you shouldn't do that. You should always implement proper wrappers for your data to have proper control over what is queried and how. This is the only way to have decent control over performance. However, if you don't need to access the props by yourself and the only thing concerned with the results is the `DataGrid`, then you could use an anonymous type and return the results as a *nongeneric* collection, I am pretty sure `DataGrid` can bind to that as well. – Balázs Feb 03 '17 at 10:30
I try it and a Exception occured:The method 'Skip' is only supported for sorted input in LINQ to Entities. – Gabriel Shell Feb 03 '17 at 12:36
Indeed, though it is not mandatory; I just put it there to also point out that you should not return unbounded result sets. You can either leave the paging completely if you just want to display all records, or sort on the PK (or, actually anything you like) by default. – Balázs Feb 03 '17 at 12:41
I add OrderBy before the Skip, and the Exception disappear, but come up a different Exception : "At least one argument did not be assigned value" at the query line. If I replace "cli.Cards.Count" to 0 , the Exception disappear, I haven't found the solution yet – Gabriel Shell Feb 03 '17 at 12:52
I cannot reproduce that behavior. Are you sure you are using EF **6** ? – Balázs Feb 03 '17 at 13:06
I am Using EF 6.1.3 , My database is MS Access ，Using Nuget package "JetEntityFrameworkProvider 1.2.6" to Connect the Database. Code First. – Gabriel Shell Feb 03 '17 at 13:18
I follow the instruction here to create my database:https://msdn.microsoft.com/en-us/data/jj193542(v=vs.113) – Gabriel Shell Feb 03 '17 at 13:22

score 0 · Answer 2 · answered Feb 03 '17 at 10:24

0

You can get the count without loading the entities like this:

using (var context = new MyContext()) 
{ 
     var client = context.Client.Find(clientId); 

     // Count how many cards the client has  
     var cardsCount = context.Entry(client) 
                      .Collection(b => b.Cards) 
                      .Query() 
                      .Count(); 
}

More information on MSDN page.

answered Feb 03 '17 at 10:24

Mihail Stancescu

4,088
1
16
21

Since he is using a `DataGrid`, I assume he would like to display details of multiple clients, in which case this will cause a select n+1 issue. – Balázs Feb 03 '17 at 10:26
This can be used in a view model and show the cards count there. I don't think it's a good design to have that property in the entity itself. – Mihail Stancescu Feb 03 '17 at 10:28
Neither is it a good idea to have it in the viewModel. A viewModel shouldn't contain *any* logic by definition. And no matter *where* you do this, the only place you can avoid multiple roundtrips to the database is if you pick this value right along with the other data. Neither placing it inside the entity nor into the viewmodel will prevent select n+1, this is what I am trying to tell you.... – Balázs Feb 03 '17 at 10:33
You are right, but this only does a query like this `select count(*) from cards where clientId = @clientId` and returns an `int`. And if you have this in a view model you will only make this query when needed, not every time the entity is used. – Mihail Stancescu Feb 03 '17 at 10:36
... this is because a get-only property will not be evaluated by EF and it has no way of figuring out the logic inside it, so it won't be able to construct an SQL to get the count. Neither does it have a way to find out that you will evaluate the same property for multiple returned results. (in other words it cannot batch the `count`s up). Long story short, no matter where you put this logic, the bottom line is that getting this piece of information one-by-one is a bad implementation. Imagine establishing a connection 10k times if you need to get this data for 10k records. – Balázs Feb 03 '17 at 10:36
"this only does a query like this"... That would work if you only needed this information for 1 record. The thing that I am trying to point out is that since he is using a `DataGrid` he will *inevitably* need it for way more than just 1 record, and this is what causes serious performance issues. If you display 10k records and the requirement is to display the card count in line with the client then you will need this info for each and every record and therefore you will need to do this query for each and every client displayed, one-by-one. – Balázs Feb 03 '17 at 10:39
Since this is getting too long, I will just point you to this answer. You should examine other suggested questions/answers along with it: http://stackoverflow.com/a/97253/4205470 – Balázs Feb 03 '17 at 10:41
Yes, you are correct in the scenario of a list of records and your solution is also correct. Thanks for pointing it out! – Mihail Stancescu Feb 03 '17 at 10:42

score -1 · Answer 3 · edited May 23 '17 at 11:45

-1

You get an ObjectDisposedException if you do not materialize the retreived query. In the following case, the query gets executed only when you Access the first time the list from GetNonMaterialized and not before leaving the method. Fact of this the db is disposed because of lost of scope.

public IEnumerable<Client> GetNonMaterialized()
{
    return from n in db.Clients select n;
}

In the following case the query is executed before leaving the method.

public IEnumerable<Client> GetMaterialized()
{
    return (from n in db.Clients select n).ToList();
}

Always be sure that the query is executed before exiting the scope of a ObjectContext.

If you want to know whether the query is executed and when enalbe Logging of EF.

How can I turn off Entity Framework 6.1 logging?

edited May 23 '17 at 11:45

Community

1
1

answered Feb 03 '17 at 09:41

Michael Mairegger

6,833
28
41

Where should I place the GetMaterialized method? and how could I use it? I am new to WPF, so please give me more detail. Thank you~ – Gabriel Shell Feb 03 '17 at 10:00
This will not work. By calling `ToList` you only force the non-lazy properties to be populated, any related, lazily loaded entities or collections of them will *not* be loaded. The only difference between your two code fragments is that with the first one, you are not even able to fetch the nonlazy props if the context is disposed before access, but with the second one, you can at least get the base properties. – Balázs Feb 03 '17 at 10:23

How could I get a Relative Entity's count using EF6

3 Answers3