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.
精彩评论