SQL Statement to Linq Primer

Question

I've got an excruciatingly ugly SQL statement that I need converted to Linq. Rather than do it all in one shot, I'm assuming it's probably better to break it up into smaller chunks. If this isn't possible for a single query, what can I look at, as far as examples, that can give me a place to start for something this hideous? Can Linq even support something like this? If so, are there any tutorials that can guide me through this beast?

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

I don't see Table6 or Table2 in the FROM but I do see them both in the SELECT. Why would you Left join a table to itself on the same column? — Dave.Gugg, Nov 18 '14 at 20:05
Good question. Unfortunately, I don't have a good answer (inherited code). I will see about resolving that join. EDIT: Changed it. Looks like I made a mistake in translation. — razorsyntax, Nov 18 '14 at 20:08

score 2 · Accepted Answer · edited May 23 '17 at 12:06

I'll give you a starting point, basically, format ugly SQL first, and then break it into subqueries, later combine it using LINQ, no need to chain everything into one.

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 ...

Well, this is quite simple, the hardest thing are the casts, which can be solved with SqlFunctions, and Convert.ToInt32 plus ternary operator.

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

The other one that is a bit tricky, is the distinct one.

(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))

Well, we have already taken care the CASE/WHEN/CAST problem, so you shouldn't have problems with that now.

The IsNULL, CAST, WHEN/CASE can also be all solved with ternary operators/SqlFunctions.StringConvert..

Doing a left join on a table: LINQ to SQL Left Outer Join

Doing an inner join on a table What is the syntax for an inner join in LINQ to SQL?

You can do distinct with .Distinct().

If you're looking for IN, you want to use new[]{2,3,1}.Contains(Table3.Column5) in a lambda expession.

Goodluck.

That definitely points me in the right direction! Thank you so much! I've got about 16 of these ugly queries to go through and I absolutely appreciate the help! — razorsyntax, Nov 18 '14 at 20:49

SQL Statement to Linq Primer

1 Answers1