CODEWITHSUNDEEP
c#entity-frameworklinq
stonemusic
stonemusic

Reputation: 363

Entity Framework LINQ query to return a list and subset of collection satisfying certain condition

I have an entity TimesheetHeader which has a User object, and start date and end date corresponding to the week of timesheet entry, and an ICollection of TimesheetEntry.

TimesheetEntry is another entity which contains a Project entity, date and hours object (each TimesheetEntry basically records the number of hours the user worked on a project on a day).

I have to generate a report to find out the various users and breakdown of hours worked on each day on a particular project.

Currently my logic is to first get a list as follows: 

context.TimesheetHeader.Where(u => u.status.statusName != "deleted" && DbFunctions.TruncateTime(u.StartDate) >= dateStart && u.TimesheetEntry.Any(te => te.projectId == report.project)).ToList();

But this basically returns a TimesheetHeader and all its TimesheetEntry if there is at least one TimesheetEntry corresponding to the project.

Then I have to loop and filter the result.

Is there a better solution to this where I can directly get the result from the query itself, where I get the TimesheetHeader and only the subset of relevant TimesheetEntry corresponding to the project

Upvotes: 0

Views: 1431

Answers (2)

jimSampica
jimSampica

Reputation: 12420

Does this work? I also suggest pluralizing your collections so it's more clear which end of the relationship you're on.

context.TimesheetHeaders .Where(u => u.status.statusName != "deleted") .Where(u => DbFunctions.TruncateTime(u.StartDate) >= dateStart) .Where(u => u.TimesheetEntries.Any(te => te.projectId == report.project)) .Select(u => new { TimeSheetHeader = u, TimeSheetHeaderEntries = u.TimesheetEntries.Where(te => te.projectId == report.project) })

Upvotes: 1

Mukesh Modhvadiya
Mukesh Modhvadiya

Reputation: 2178

As Gert Arnold answered here you can try as below by adding where clause to your include :

context.TimesheetHeader.Where(u => u.status.statusName != "deleted" && 
 DbFunctions.TruncateTime(u.StartDate) >= dateStart).Select(t => { t, TimesheetEntries = t.TimesheetEntry.Where(te => te.projectId == report.project)).AsEnumerable()
 .Select(x => x.t)
 .ToList();

Or look at EntityFramework-Plus. It might be useful.

Upvotes: 1

Related Questions