CODEWITHSUNDEEP
linqentity-framework
Anders
Anders

Reputation: 12736

Linq expression for filtered collection of collections?

I'm hoping this will be a rather simple question for anyone who's good at Linq. I'm struggling to come up with the right Linq expression for the following. I'm able to hack something to get the results, but I'm sure there's a proper and simple Linq way to do it, I'm just not good enough at Linq yet...

I have a database accessed through Entity Framework. It has a number of Tasks. Each Task has a collection of TimeSegments. The TimeSegments have Date and Employee properties.

What I want is to be able to get the tasks for a certain employee and a certain month and the timesegments for each task for that same month and employee.

Again, the tasks do not in themselves have month nor date information, but they do by the TimeSegments associated with each task.

Very simplified it looks sort of like this:

    public class Model //Simplified representation of the Entity Framework model
    {
        public List<Task> Tasks { get; set; }
    }

    public class Task
    {
        public int Id { get; set; }
        public List<TimeSegment> TimeSegments { get; set; }
        public Customer Customer { get; set; }
    }

    public class TimeSegment
    {
        public int Id { get; set; }
        public string Date { get; set; }
        public Employee Employee { get; set; }
    }

    public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

So how do I do this as simply as possible with Linq? I.e. tasks and associated timesegments for a certain month and employee. I would also like to be able to get it by Customer BTW...

Upvotes: 0

Views: 550

Answers (2)

Keith
Keith

Reputation: 5391

What I want is to be able to get the tasks for a certain employee and a certain month and the timesegments for each task for that same month and employee.

This will select tasks from an instance of Model where the task has at least one time segment that in the requested month for the requested employee (untested):

Model model = new Model();
tasks = model.Tasks.Where(t => t.TimeSegments.Any(ts => ts.Employee.Id = requestedId && Convert.ToDate(ts.Date).Month == requestedMonth));

Upvotes: 1

Steven
Steven

Reputation: 172835

This is the simplest thing I could come up with:

var tasksWithSegments =
    from segment in model.TimeSegments
    where segment.Date.Month == month
    where segment.Employee.Id == employeeId
    group segment by segment.Task into result
    select new
    {
        Task = result.Key,
        TimeSegments = result.ToArray()
    };

Please note that you might have to add some properties to your model, such as Model.TimeSegment and TimeSegment.Task.

The trick with LINQ queries often is to start at the right collection. In this case the ideal starting point is TimeSegments.

ps. I'm not sure whether Date.Month == month will actually work with EF, but I think it will (with EF 4.0 that is).

Update:

Could you show how to extend this query and get the tasks for a particular Customer as well?

I'm not sure what you mean, but you can for instance filter the previous queryable like this: 

var tasksWithSegmentsForCustomers =
    from taskWithSegments in tasksWithSegments
    where taskWithSegments.Task.Customer.Id == customerId
    select taskWithSegments;

Can I get the return type to be a list of Tasks with a list of TimeSegments if I have this in a method?

Again, not sure what you exactly want, but if you want two separate lists that have no relation, you can do this:

List<Task> tasks = (
    from taskWithSegments in tasksWithSegments
    select taskWithSegments.Task).ToList();

List<TimeSegments> segments = (
    from taskWithSegments in tasksWithSegments
    from segment in taskWithSegments.Segments
    select segment).ToList();

Of course, if this is what you need, than it might be easier to rewrite the original query to something like this:

List<TimeSegment> segments = (
    from segment in model.TimeSegments
    where segment.Date.Month == month
    where segment.Employee.Id == employeeId
    select segment).ToList();

List<Task> allTasks =
    segments.Select(s => s.Task).Distinct().ToList();

Once you got the hang of writing LINQ queries, there is no way you want to go back to writing SQL statements or old-fashion foreach statements.

Think LINQ!!!

Upvotes: 2

Related Questions