时间戳

首页 > 解决方案 > 带有“包含”和列表的 Linq 查询过滤器多个属性

c# - 带有“包含”和列表的 Linq 查询过滤器多个属性

问题描述

我正在使用实体框架和点网框架

例如，我有学生课

public class Student
{
    public int StudentId { get; set; }
    public int Age { get; set; }
    public int DepartmentId { get; set; }
    public string Name { get; set; }
}

当我需要搜索许多部门的学生时，我可以这样做

List<Student> students = new List<Student>
{
    new Student{Age = 12,DepartmentId =1 },
    new Student{Age = 11,DepartmentId =2 },
    new Student{Age = 11,DepartmentId =3 },
    new Student{Age = 13,DepartmentId =4 },
};
// get all department ids
var departmentIds = students.Select(s => s.DepartmentId);
var studentList = DbContext.Students.Where(s=>departmentIds.Contains(s.DepartmentId)).ToList();

这将生成 SQL 查询，如

  Select StudentId , Age , DepartmentId , Name
  From Students where DepartmentId in (1 , 2 , 3 , 4)

我的问题

我需要搜索任何学生匹配学生列表中的任何项目，如以下查询

Select StudentId , Age , DepartmentId , Name
From students 
Where 
    (departmentId= 1  and age = 12)
    OR
    (departmentId= 2  and age = 11)
    OR
    (departmentId= 3  and age = 12)
    OR
    (departmentId= 4  and age = 13)

标签： c#entity-frameworklinq

解决方案

存储每个部门的年龄并写入

var departmentAge =
    departments.ToDictionary(d => d.DepartmentId, d => d.RequiredAge);

接着

.Where(s => departmentIds.Contains(s.DepartmentId) &&
            s.Age == departmentAge[s.DepartmentId])

第二部分不能翻译成 SQL。您可能必须将它与AsEnumerable查询中的两个表分开或连接以获得类似

Select s.StudentId, s.Age, s.DepartmentId, s.Name
From Students s inner join Departments d
where s.DepartmentId in (1 , 2 , 3 , 4) and s.Age = d.RequiredAge

推荐阅读