ado.net entity framework using count in a where clause

Question

Hi,

I am trying to execute the following query but I get the wrong results back.

        foreach (var item in (from project in db.Projects
                              where project.Id == pProjectId
                              from task in project.Tasks
                              from taskItem in task.TaskItems
                              where taskItem.Velocities.Count() == 0 // not finished yet
                              select new
                              {
                                  ProjectId = pProjectId,
                                  PriorityId = task.Priorities.Id,
                                  TaskId = task.Id,
                                  ResourceId = taskItem.Resources.Id,
                                  EstimatedDuration = taskItem.EstimatedDuration,
                                  TaskItemId = taskItem.Id
                              }))
        {
        }

I am trying to generate objects from all the taskItems that don't have velocity related objects. The table structure is that every taskItem may have many velocities. Right before this call I give velocities to some of the items, yet they are not filtered by this where clause. Am I doing something obvious wrong?

Edit: I think (after staring at the code for a while) that I need to specify some kind of grouping. I don't actually require any of the Velocity record details, but rather just a count of them that relate to the taskItems

Answer 1

You need to have joins to reflect the relationships, currently you are doing a cartesian (cross) join (i.e. all combinations of project, task and taskItem).

Try something like:

var res = from project in db.Projects
          where project.Id == pProjectId
          join task in project.Tasks on task.projectId equals project.projectId
          join ttaskItem in task.TaskItems on taskItem.taskId equals task.taskId
          where taskItem.Velocities.Count() == 0 // not finished yet
          select new {
            ProjectId = pProjectId,
            PriorityId = task.Priorities.Id,
            TaskId = task.Id,
            ResourceId = taskItem.Resources.Id,
            EstimatedDuration = taskItem.EstimatedDuration,
            TaskItemId = taskItem.Id
          };

Answer 2

You could try moving the taskItem.Velocities.Count() inside the select new { }

Then do a select on the resultant list where the velicties count == 0

Answer 3

Have you saved the changes to the entity model before the query?

The Entity model is querying your database to retrieve those values, if you are adding data to the entities BUT had not saved them to the db, the query can't know about those new values.

Try db.SaveChanges() before the query.