给 Linq Primer 的 SQL 语句

本文关键字：SQL 语句 Primer Linq | 更新日期: 2023-09-27 18:30:47

我有一个非常丑陋的SQL语句，我需要将其转换为Linq。 与其一次性完成所有工作，我认为最好将其分解成更小的块。 如果单个查询无法做到这一点，那么就示例而言，我可以查看什么，这可以为我提供开始如此可怕的事情的地方？ Linq 甚至可以支持这样的东西吗？ 如果是这样，有没有教程可以指导我了解这个野兽？

SELECT
  Test1 = CAST(Table2.Column1 AS VARCHAR(10))
, Test2 = CAST(CAST(Table2.Column2 AS INT) AS VARCHAR(10))
, Test3 = CASE WHEN CAST(Table2.Column2 AS INT) = 0 THEN '0' ELSE CAST(CAST(CAST(Table2.Column1  AS FLOAT)/CAST(Table2.Column2 AS FLOAT)*100 AS DECIMAL(5,2) )AS VARCHAR(10)) END
, Test4 = '100'
, Test5 = CASE WHEN CAST(Table2.Column2 AS INT) = 0 THEN 'No' WHEN CAST(Table2.Column1  AS FLOAT)/CAST(Table2.Column2 AS FLOAT)*100 > 10 THEN 'Yes' ELSE 'No' END
 FROM (
SELECT  Test16 = IsNull(SUM(Table1.Column1), 0), Test15 = IsNull(SUM(Table1.Column2), 12) FROM (SELECT  DISTINCT
  a_Test6 = Table3.Column1
, a_Test7 = 1
, a_Test8 = IsNull(Table4.Column1+', ','' )+IsNull(Table4.Column2+' ', '' )+IsNull( Table4.Column3, '')
, a_Test9 = IsNull ( Table5.Column2, ' ')
, a_Test10 = Table3.Column4
, a_Test11 = Table6.Column4
, a_Test12 = Case IsNull (Table3.Column2, 0) When 1 Then 1 Else 0 End
, a_Test13 = Case IsNull (Table3.Column2, 0) When 1 Then 'Yes' Else 'No' End
, a_Test14 = 'NAP'
 FROM Table3
 Left JOIN Table3 ON Table3.Column3 = Table3.Column3
 INNER JOIN Table5 ON Table6.Column3 = Table5.Column1 Where Table3.Column5 IN (2,3,1) AND Table3.Column6 in (9,8) AND (CAST(CAST( Table3.Column4 AS float) AS int )>=12345 And CAST(CAST( Table3.Column4 AS float) AS int )<=54321) ) As Table0
) AS a FOR BROWSE

给 Linq Primer 的 SQL 语句

我给你一个起点，基本上，先格式化丑陋的SQL，然后把它分解成子查询，然后用LINQ组合起来，不需要把所有东西都链接成一个。

SELECT Test1 = CAST(Table2.Column1 AS VARCHAR(10)) ,
       Test2 = CAST(CAST(Table2.Column2 AS INT) AS VARCHAR(10)) ,
       Test3 = CASE
                    WHEN CAST(Table2.Column2 AS INT) = 0 THEN '0'
                    ELSE CAST(CAST(CAST(Table2.Column1 AS FLOAT)/CAST(Table2.Column2 AS FLOAT)*100 AS DECIMAL(5,2))AS VARCHAR(10))
                END,
        Test4 = '100',
        Test5 = CASE
                    WHEN CAST(Table2.Column2 AS INT) = 0 THEN 'No'
                    WHEN CAST(Table2.Column1 AS FLOAT)/CAST(Table2.Column2 AS FLOAT)*100 > 10 THEN 'Yes'
                    ELSE 'No'
                END
FROM ...

嗯，这很简单，最难的是强制转换，可以用 SqlFunctions 和Convert.ToInt32加三元运算符来解决。

YourSource.Select(x => new {
   Test1 = SqlFunctions.StringConvert(Table2.Column1, 10),
   Test3 = Convert.ToInt32(Table2.Column2) == 0 ? "0" : <..more logic here..>
});

另一个有点棘手的，是独特的。

(SELECT DISTINCT 
        a_Test6 = Table3.Column1 ,
        a_Test7 = 1 ,
        a_Test8 = IsNull(Table4.Column1+', ','')+IsNull(Table4.Column2+' ', '')+IsNull(Table4.Column3, '') ,
        a_Test9 = ISNULL (Table5.Column2, ' '),
        a_Test10 = Table3.Column4 ,
        a_Test11 = Table6.Column4 ,
        a_Test12 = CASE ISNULL (Table3.Column2, 0) WHEN 1 THEN 1 ELSE 0 END ,
        a_Test13 = CASE ISNULL (Table3.Column2, 0) WHEN 1 THEN 'Yes' ELSE 'No' END ,
        a_Test14 = 'NAP'
    FROM Table3
    LEFT JOIN Table3 ON Table3.Column3 = Table3.Column3
    INNER JOIN Table5 ON Table6.Column3 = Table5.Column1
    WHERE Table3.Column5 IN (2, 3, 1)
     AND Table3.Column6 IN (9, 8)
     AND (CAST(CAST(Table3.Column4 AS float) AS int)>=12345
          AND CAST(CAST(Table3.Column4 AS float) AS int)<=54321))

好吧，我们已经解决了案例/时间/演员的问题，所以你现在应该不会有问题。

IsNULL, CAST, WHEN/CASE也可以用三元运算符/SqlFunctions.StringConvert来解决。

对表执行左联接：LINQ to SQL 左外联接

对表执行内部联接 LINQ to SQL 中内部联接的语法是什么？

你可以用.Distinct()做不同的事情。

如果您正在寻找 IN ，您想在 lambda 表达式中使用new[]{2,3,1}.Contains(Table3.Column5)。

祝你好运。

相关文章：