Linq，linq where in where

Question

我有一条这样的SQL语句：

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

我需要用Linq编写它。

我这样写它，但它不工作-它返回一个空列表：

包含26和27的ProjectIdsForBudgets - is列表

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)
              )
         )

分组依据我们可以跳过

你能帮帮我吗？

Answer 1

所以你有一个Projects序列和一个Issues序列。此外，您有一个TimeEntries序列，其中每个TimeEntry都只属于一个使用外键TimeEntry.ProjectId的Project。每个TimeEntry也只属于一个使用外键TimeEntry.IssueId的Issue

您希望在这三个表的主键和外键上连接它们。您只需要保留连接结果的某些元素(Where)。其余元素应分组到具有相同name、id、subject和spentOn的组中。最后，您希望从每个组及其元素中选择一些属性。

如果你有类似于实体框架的东西，你的项目将有一个虚拟的TimeEntries集合，并且每个TimeEntry都有一个对项目的虚拟引用。如果使用该方法，实体框架将理解需要连接。您可以使用表之间的引用。

这将在后面讨论。首先，使用连接三个表的方法

方法:连接三个表

通常我会使用方法语法。但是，method syntax looks hideous if you join three tables.这是我使用查询语法的唯一一次。

我用更小的步骤来做这件事。让它成为一个大的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,
    };

仅保留部分联接项：

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);

将生成的元素分组到具有相同名称、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,
});

最后，从每个组中选择您想要保留的项目：

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
    ...
});

在实体框架中使用类关系

如果你有类似于实体框架的东西，那么你的一对多关系应该是使用集合实现的：

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;}
}

如果你有这样的东西，你不需要自己做连接，实体框架会理解它：

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,
        ...
     });