EF Core 3.0-将SQL转换为LINQ [英] EF Core 3.0 - Convert SQL to LINQ

问题描述

当我们寻找完全匹配时，包含逻辑将不起作用.如果学生已报名参加6门课程(例如:1、2、3、4、5、6)，并且请求的列表包含5门课程(例如:1、2、3、4、5)，则查询将返回匹配项不应该.当学生已经注册了所请求列表的子集时，另一种方法会很好地起作用.

The contains logic will not work when we are looking for an exact match. If a student has enrolled for 6 courses (ex : 1,2,3,4,5,6) and the requested list contains 5 (ex: 1,2,3,4,5) the query will return a match when it should not. The other way works well when the student has enrolled in a subset of the requested list.

以下解决方案有效，但需要帮助将下面的sql转换为LINQ(EF Core 3.0)吗?

Below solution works but need help to convert the below sql to LINQ (EF Core 3.0) ?

Create TABLE dbo.Enrollments (StudentId INT NOT NULL, CourseId INT NOT NULL)
insert into dbo.Enrollments values (1,1)
insert into dbo.Enrollments values (1,2)
insert into dbo.Enrollments values (1,3)
insert into dbo.Enrollments values (1,4)
insert into dbo.Enrollments values (1,5)
insert into dbo.Enrollments values (1,6)

DECLARE @TempCourses TABLE
(
   CourseId INT
);

INSERT INTO @TempCourses (CourseId) VALUES (1), (2), (3),(4),(5);

SELECT t.StudentId
FROM
(
  SELECT StudentId, cnt=COUNT(*)
  FROM dbo.Enrollments
  GROUP BY StudentId
) kc
INNER JOIN
(
  SELECT cnt=COUNT(*)
  FROM @TempCourses
) nc ON nc.cnt = kc.cnt
JOIN dbo.Enrollments t ON t.StudentId = kc.StudentId
JOIN @TempCourses n ON n.CourseId = t.CourseId
GROUP BY t.StudentId
HAVING COUNT(*) = MIN(nc.cnt);

drop table dbo.Enrollments

db<>小提琴

推荐答案

我不知道SQL查询，但是针对同一任务的EF Core 3.0 LINQ查询是这样的:

I don't know about the SQL query, but the EF Core 3.0 LINQ query for the same task is something like this:

var matchIds = new[] { 1, 2, 3, 4, 5 }.AsEnumerable();
var query = dbContext.Students
    .Where(s => s.Enrollments.All(e => matchIds.Contains(e.CourseId)) 
        && s.Enrollments.Count() == matchIds.Count());

主要匹配工作是通过 All 子查询完成的.不幸的是，仅当相关链接记录超过匹配的ID时，这还不够，因此，通过额外的计数比较就可以解决该问题.

The main matching job is done with All subquery. Unfortunately that's not enough for the case when related link records are more than the matching ids, so additional counts comparison solves that.

这篇关于EF Core 3.0-将SQL转换为LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！