How to use Entity context in entity class for performance?

Question

Suppose I have project MyData, then I add MyDb.edmx with EF wizard. In My database, I have table Person. Then I will get an entity Person in EF model.

Then I want to extend this Person by partial class in a separated file person.cs like:

namespace MyData
{ 
    public partial class Person
    {
        [DataMember]
        public int MyTotal
        {
            get
            {
                int count = 0;
                using (MyEntities ctx = new MyEntities())
                {
                    //run some linq to get calc based on other entities
                }
                return count;
            }
        }
}

It works fine. Then at client I can get the entity Person instance with property MyTotal. Problem is: When I want to list of Person at client side(such silverlight), the performance is very bad because for each instance of Person, it will cause on SQL DB connection and run one SQL for MyTotal.

If I have more ten one such kind of extending property, the performance will be very bad. How to resolve this problem?

Answer 1

First off, I would strongly recommend avoiding putting this in a property. Properties should have little to no code - in a case like this, where you're going to be performing significant logic, it would make more sense to use a method (ie: GetMyTotal()), since that will make it more obvious to the user that you're going to running a potentially long running process at this point.

That being said, you have a few options here. My personal preference would be to prefetch this information. You could change your Entity to pull results from a Stored Procedure instead of a table. This would allow you to pre-calculate all of these values, on the server, and only pull a single result across the wire.

This also has the advantage of allowing you to "cache" this value on the client, which means you can keep it a property (since the total value will already be in the entity).