开发者

Entity Framework - Pulling back related data

I have an ASP.net MVC controller action that is retrieving a list of items from my entity model. For this entity, there are a few properties that aren't in the entity itself. I created a partial class to add these properties:

public partial class Person
{
    public int Extra
    {
        get
        {
            using( var ctx = new DBEntities() )
            {
                return ctx.OtherTable.Count(p => p.PersonID == this.PersonID);
            }
        }
    }
}

As you can see, the property that I need access to comes from another table. In my MVC page...I need to return a large number of people (100+ per page). In order to display this Extra field, each Person entity is going to be hitting the database separately...which has been extremely inefficient. I have one query to return all the people, and then for each person it has a query for each property I have like this. This can end up being 300 calls to the database, which is taking a long time to execute.

What is a better way to do this? I would ideally like to execute one query that returns all the People and the extra data, but I would also like the extra data to be part of the Person entity, even if it is in a separate table in the database.

Update

To add a little more context from the comments.

I am returning the People from a repository class. I was told in another question that the repository should only be dealing with the entities themselves. So the code that retrieves the people is like:

class PersonRepository
{
    public IQueryable<Person> GetPeople() {
        return from p in db.People
               where p ...
               se开发者_运维技巧lect p;
    }
}

I don't really have the option to join in that case.


You could do joins:

var result = 
    from p in ctx.Persons
    from a in ctx.OtherTable
    where p.PersonID == personID
    select new SomeViewModel 
    {
        Name = p.Person.Name,
        OtherTableValue = a.OtherValue
    };


I'm not sure about how your database design is done. But why can't you join the data from the two related tables and hit the data once rather then multiple times? Even if that is slow for you, you can also cache this data and be able to access it during the lifetime of the session.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜