LInq to sql 查询

Question

我有一个如下的SQL查询：

  Declare @DivisionNo INT

  SET @DivisionNo = 5117



  SELECT distinct CASE WHEN ISNULL([DivisionNo],'') <> @DivisionNo 
                       THEN @DivisionNo ELSE [DivisionNo] END as DivisionNo

      --,[RecordID]    
      ,[AcctCat]    
      ,[AcctCatDesc]    
      ,[CostCode]    
      ,[CostCodeDesc]

  FROM [dbo].[vw_eSchdl_AcctCat_CostCode]    
  WHERE DivisionNo = @DivisionNo

  UNION

  SELECT distinct CASE WHEN ISNULL([DivisionNo],'') <> @DivisionNo 
                       THEN @DivisionNo ELSE [DivisionNo] END as DivisionNo

      --,[RecordID]    
      ,[AcctCat]    
      ,[AcctCatDesc]    
      ,[CostCode]    
      ,[CostCodeDesc]

  FROM [dbo].[vw_eSchdl_AcctCat_CostCode]    
  WHERE AcctCat not in (
      SELECT [AcctCat]     
      FROM [dbo].[vw_eSchdl_AcctCat_CostCode]
      WHERE DivisionNo = @DivisionNo
  )

我该如何使用linq to sql进行复制呢？

谢谢。

- Mohit

---

你尝试过什么？你能够做到一半，但在某个地方卡住了吗？你可以发布你所拥有的东西吗？哪一部分让你感到困惑？或者你根本不知道从哪里开始？ - Mark Byers

Answer 1

你可以使用Linqer将SQL转换成Linq。你可以从这里下载。

- Mark Byers

---

@Mark - LinqPad不会将SQL转换为Linq。但是，一个名为Linqer的产品可以实现此功能。 - Randy Minder

Answer 2

函数等价怎么样？

int divisionNo = 5117;

var matches = from ac in context.AcctCatCostCodes 
              where ac.DivisionNo == divisionNo
              select ac;

var missingAcctCat = from ac in matches
                     select ac.AcctCat;

var others = from ac in context.AcctCatCostCodes
             where !missingAcctCat.Contains(ac.AcctCat)
             select ac;

var union = from ac in matches.Union(others)
            select new
            {
                DivisionNo = ac.DivisionNo ?? divisionNo,
                ac.AcctCat,
                ac.AcctCatDesc,
                ac.CostCode,
                ac.CostCodeDesc
            };

...与方法相同，而不是查询语法...

var matches = context.AcctCatCostCodes
                     .Where(ac => ac.DivisionNo == divisionNo);

var missingAcctCat = matches.Select(ac => ac.AcctCat);

var others = context.AcctCatCostCodes
                    .Where(ac => !missingAcctCat.Contains(ac.AcctCat));

var union = matches.Union(others).Select(ac =>
             new
            {
                DivisionNo = ac.DivisionNo ?? divisionNo,
                ac.AcctCat,
                ac.AcctCatDesc,
                ac.CostCode,
                ac.CostCodeDesc
            });

...由LINQ2SQL生成的SQL语句...

SELECT COALESCE([t4].[DivisionNo],@p2) AS [DivisionNo], 
       [t4].[AcctCat], 
       [t4].[AcctCatDesc], 
       [t4].[CostCode], 
       [t4].[CostCodeDesc]
FROM (
    SELECT [t3].[AcctCat], [t3].[AcctCatDesc], [t3].[CostCode], 
           [t3].[CostCodeDesc], [t3].[DivisionNo]
    FROM (
        SELECT [t0].[RecordID], [t0].[AcctCat], [t0].[AcctCatDesc], 
               [t0].[CostCode], [t0].[CostCodeDesc], [t0].[DivisionNo]
        FROM [AcctCatCostCode] AS [t0]
        WHERE [t0].[DivisionNo] = @p0
        UNION
        SELECT [t1].[RecordID], [t1].[AcctCat], [t1].[AcctCatDesc], 
               [t1].[CostCode], [t1].[CostCodeDesc], [t1].[DivisionNo]
        FROM [AcctCatCostCode] AS [t1]
        WHERE NOT (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [AcctCatCostCode] AS [t2]
            WHERE ([t2].[AcctCat] = [t1].[AcctCat]) 
                  AND ([t2].[DivisionNo] = (@p1))
            ))
        ) AS [t3]
    ) AS [t4]

- Matthew Whited

Answer 3

如果你有一点时间，使用 linqpad 自己动手做是最好的学习方式。你可以打开一个 SQL 标签和一个 LINQ 标签，尝试复制你的查询结果。

- Nicholas Murray