How to query related entities (1:N relation) in entity framework

What is a better way to get related entities in scenarios like this: {class diagram here}?

I wrote two ways (please also note the difference in class diagram)... Project has property with ICollection of Tasks, but Member doesn't. Both ways are working and I would like to know, which one is correct (better/faster). Or if none is good, then what is a correct way in these simple scenarios?

Code:开发者_JAVA百科

    using (var db = new EntitiesContext())
        {
            // way A
            Project project = db.Projects.Include("Tasks").First();
            List<Task> projectTasks = project.Tasks.ToList();
            count = projectTasks.Count;

            // way B
            Member member = db.Members.First();
            IQueryable<Task> memberTasks = from t in db.Tasks
                                           where t.AssignedTo.Id == member.Id
                                           select t;
            count = memberTasks.Count();
        }

I am using EF 4.1 Code First.

Btw: Don't worry about the result (getting count) too much. This is just a piece of test code, I would of course like to query more useful informations in future.

---

When performing these queries, your performance bottlenecks will generally fall under one of two categories:

1. Getting too much data that you don't need.
2. Making too many round-trips to the database.

Your first example may suffer from the first of these. If you look at the data that gets returned from the database, all the data for the Project will be repeated for every Task connected with it. If your Project has a lot of data associated with it, that can cause a lot of overhead. If it's fairly lean, it won't cost that much more.

Your second example creates a second round-trip to get the tasks. The additional round-trip introduces extra overhead, but it means that less duplicated data will be returned overall. A single extra round-trip probably isn't a big deal, but if you're doing this for multiple projects, you could easily end up with dozens of unnecessary round-trips.

So deciding which way to go will really be a balancing act, based on what your data typically looks like and what you really want from it. In this particular case, you'd be better off with:

count = db.Tasks.Count(t => t.AssignedTo.Id == db.Members.FirstOrDefault().Id)

... which will create a single optimized query that just returns the count, with no superfluous data or extra round-trips. So you can see how the answer to questions like this will really depend on what you're trying to get out of the database, exactly.

Reponse to Comment

If you're trying to get all the tasks related to something, your query should only be getting tasks. There are lots of ways to do this:

var memberTasks = db.Tasks.Where(t => t.AssignedTo.Id == memberId).ToList();

or (if you don't know the member's ID yet):

var memberTasks = db.Tasks.Where(t => t.AssignedTo.[your criterion]))
                      .ToList();

or (if you want the tasks for multiple members at once):

var tasksByMemberId = (from t in db.Tasks
                       where t.AssignedTo.[your criterion])
                       select new {MemberId = t.AssignedTo.Id, t})
                      .ToLookup(e => e.MemberId, e => e.t);

I could go on. The point is that all of these queries specifically get the Tasks out, without worrying the Member's data.

Adding an additional layer of data shouldn't change things much, either:

var projectTasks = db.Tasks.Where(t => t.Iteration.Project.Id == projectId).ToList();

---

Method A is cleaner and results in optimized SQL query pulling in your related data all at once. Method B is similar to lazy loading the related data as you need it instead.

I prefer A when I need the data now, and B when I may not even access the related data unless the user takes some action in the program.

---

I think those two cannot be compared because both examples have their usage. The first is definitely the way to go if you have navigation property because in such case you will return all data in one roundtrip to database. The second approach is useful if you don't have navigation property available.

---

Both ways are working and I would like to know, which one is correct (better/faster).

Using Linq to Entities in general Navigation properties are the way to go - the data is joined in automatically for you (if you use Include).

Your first query is not optimized for getting a count currently, you can just write:

count = db.Projects.Include("Tasks").First().Tasks.Count();

Otherwise you are loading all related task entities from the DB.

---

Why don't you use this:

project.Tasks.Load();