Linq, linq where in where in where

Question

I have a SQL statement like this:

SELECT 
    projects.name, projects.id, 
    issues.subject, issues.description, 
    time_entries.spent_on, time_entries.hours 
FROM 
    (time_entries 
INNER JOIN 
    projects ON time_entries.project_id = projects.id) 
INNER JOIN 
    issues ON time_entries.issue_id = issues.id
WHERE 
    (((projects.id) IN (26, 27)) 
    AND ((issues.subject) NOT LIKE "*zlecane*")) 
    AND MONTH(spent_on) = MONTH(CURRENT_TIMESTAMP) 
GROUP BY 
    name, id, subject, spent_on

and I need to write it in Linq.

I write it like this but it doesn't work - it returns an empty list:

ProjectIdsForBudgets - is list with 26 and 27

Projects.Include(x => x.Issues)
        .ThenInclude(k => k.TimeEntries)
        .Where(x => ProjectIdsForBudgets.Contains(x.Id) && 
                    x.Issues.Any(y => !y.Subject.Contains("zlecane") &&
                    y.TimeEntries.Any(K => K.SpentOn >= firstDayOfTheMonth)
              )
         )

Group by we can skip

Can you help me please?

When debugging which condition is not working for you? Just add them one by one and test. — CodeNotFound, Jun 23 '18 at 13:45
Which LINQ flavor (Entity Framework?) and which database brand? Please use appropriate tags (+ versions). — Gert Arnold, Jun 23 '18 at 14:48
Test your code in smaller pieces. First leave out the where and make sure you get results. Then add one piece of a where at a time making sure you are getting the correct results until you isolate which piece of the query is causing the issue. — jdweng, Jun 23 '18 at 15:12

score 0 · Answer 1 · answered Jun 26 '18 at 14:25

So you have a sequence of Projects, and a sequence of Issues. Furthermore you have a sequence of TimeEntries where every TimeEntry belongs to exactly one Project using foreign key TimeEntry.ProjectId. Every TimeEntry also belongs to exactly one Issue using foreign key TimeEntry.IssueId

You want to join these three tables on their primary and foreign keys. You only want to keep some elements of the joined results (Where). The remaining elements should be grouped into groups with same name, id, subject and spentOn. Finally you want to select some properties from every group with its elements.

If you have something similar to entity framework, your Project will have a virtual collection of TimeEntries and every TimeEntry has a virtual reference to a Project. If that method is used, entity framework will understand that a join is needed. You can usee the references between the tables.

That is discussed later. First the method using the join three tables

Method: Join three tables

Normally I would use Method syntax. However method syntax looks hideous if you join three tables. Those are the only times I use Query syntax.

I do this in smaller steps. Feel free to make it one big linq

var joinedItems = from timeEntry in timeEntries
    join project in project on timeEntry.ProjectId equals project.Id
    join issue in issues on timeEntry.IssueId equals issue.Id
    select new
    {
        TimeEntry = timeEntry,
        Project = Project,
        Issue = Issue,
    };

Keep only some of the joined items:

int currentMonth = DateTime.Now.Month;
var subsetJoinedItems = joinedItems.Where(joinedItem =>
   (joinedItem.Project.Id == 26 || joinedItem.Project.Id == 27)
   && joinedItem.Issue.Subject.Contains("zlecane")   // consider ignore case
   && joinedItem.TimeEntry.SpentOn.Month == currentMonth);

Group the resulting elements into groups of same [name, id, subject, spent_on]:

var groups = subsetJoinedItems.GroupBy(joinedItem => new
{
     ProjectId = joinedItem.Project.Id,
     ProjectName = joinedItem.Project.Name,
     Subject = joinedItem.Issue.Subject,
     Spent_on = joinedItem.TimeEntry.SpentOn,
});

Finally, from every group select the items you want to keep:

var result = groups.Select(group => new
{
    ProjectId = group.Key.ProjectId,
    ProjectName = group.Key.ProjectName,
    Subject = group.Key.Subject,
    SpentOn = group.Key.SpentOn,

    // remaining properties: SQL query seems incorrect
    ...
});

Using Class Relationships as in entity framework

If you have something similar to entity framework, then your one-to-many relationships would have been implemented using collections:

class TimeEntry
{
     public int Id {get; set;}

     // every TimeEntry belongs to exactly one Project using foreign key
     public int ProjectId {get; set;}
     public virtual Project Project {get; set;}

     // every TimeEntry belongs to exactly one Issue using foreign key
     public int IssueId {get; set;}
     public virtual Issue Issue {get; set;}
}

If you have something like this, you don't have to do the joins yourself, entity framework would understand it:

var result = TimeEntries.Where(timeEntry =>
    (timeEntry.ProjectId == 26 || timeEntry.ProjectId == 27)
       && timeEntry.Issue.Subject.Contains("zlecane")   // consider ignore case
       && TimeEntry.SpentOn.Month == currentMonth)
    .GroupBy(timeEntry => new
    {
        ProjectId = joinedItem.Project.Id,
        ProjectName = joinedItem.Project.Name,
        Subject = joinedItem.Issue.Subject,
        Spent_on = joinedItem.TimeEntry.SpentOn,
    })
    .Select(group => new
    {
        ProjectId = group.Key.ProjectId,
        ProjectName = group.Key.ProjectName,
        Subject = group.Key.Subject,
        SpentOn = group.Key.SpentOn,
        ...
     });

Linq, linq where in where in where

1 Answers1

Method: Join three tables

Using Class Relationships as in entity framework