SQL查询到LINQ(对我来说很复杂)

Question

我是LINQ的新手，我做了简单的查询，但我在这个问题上碰壁了。 有人可以帮我将其转换为 LINQ 吗？ 将桌子与自己连接起来，记录的计数让我挂了。

SELECT DISTINCT
     RegionID = O.Region,
     Region = R.Office,
     OfficeCount = (SELECT
          COUNT(officeID)
          FROM OFfice
          WHERE Region = O.region
          AND Type IN (5, 6)
          AND ClosedProduction = 0
          AND OfficeID NOT IN (10, 135, 151)
          AND (OfficeID IN (SELECT DISTINCT
                    OfficeID
                    FROM WR_Data_Work
                    WHERE PhaseID IS NOT NULL)
          OR OfficeID = 154))
     FROM office O
     JOIN Office R
     ON O.Region = R.OfficeID
     JOIN Employee VP
     ON R.VicePresID = VP.EmployeeID
     WHERE O.OfficeID NOT IN (10, 135, 151)
     AND O.Type IN (5, 6)
     AND O.ClosedProduction = 0
     AND (O.OfficeID IN (SELECT DISTINCT
                         OfficeID
                         FROM WR_Data_Work
                         WHERE PhaseID IS NOT NULL)
     OR O.OfficeID = 154)
ORDER BY RegionID

防毒墙 ID 办公室名称区域88 办公室 1 9090 办公室 2 9096 办公室 3 9086 办公室 4 9391 办公室 5 9392 办公室 6 9393 办公室 7 9395 办公室 8 93

在这种情况下，办公室

2 是办公室 1 和 3 的区域办事处，但办公室 7 是办公室 4、5、6 和 8 的区域办事处。

Answer 1

这是我没有任何测试能力的尝试：

var PhaseOffices = (from w in WR_Data_Work where w.PhaseID != null select w.OfficeID).Distinct();
var TargetOffices = from O in Office
                    where (O.Type == 5 || O.Type == 6) && O.ClosedProduction == 0 &&
                    (!(new[] { 10, 135, 151 }).Contains(O.OfficeID)) && (PhaseOffices.Contains(O.OfficeID) || O.OfficeID == 154)
                    select O;
var res = (from O in TargetOffices
          join R in Office on O.Region equals R.OfficeID
          //join VP in Employee on R.VicePresID equals VP.EmployeeID = not needed?
          orderby O.Region
          select new {
              RegionID = O.Region,
              Region = R.Office,
              OfficeCount = (from ofc in TargetOffices
                             where ofc.Region == O.Region
                             select ofc.OfficeID).Count()
           }).Distinct();

某些 LINQ 提供程序不会处理我使用的本地数组Contains，在这种情况下，您必须将其替换为单个测试，就像我对 Type 所做的那样。