SQL to LINQ Converter

Question

I have a SQL and having a hard time to convert it to LINQ. Are there any other tools that can convert SQL to LINQ? I heard Linqer and tried it but it doesn't allow you to query on the Junction/Join Table of a Many-to-Many relationship tables. In any case here's the SQL that I need to convert to LINQ or even lambda.

SELECT A.*
FROM
(              SELECT  TOP 10   T2.ID,
                T2.Name,                        
                SUM(T1.Column1 + T1.Column2 + T1.Column3) AS Total
FROM POS.dbo.Table1 T1
INNER JOIN POS.dbo.Table2 T2 on T2.ID = T1.ID
WHERE T2.ID IN 
(
    SELECT ID FROM POS.dbo.Table3 WHERE [Id] = 1
)
AND [Date] BETWEEN '2011-11-09 00:00:00' AND '2011-11-09 23:59:59'
GROUP BY T2.ID, T2.Name
ORDER BY Total DESC
) A
ORDER BY Name ASC

Here is my first attempt:

var query = db.Table1
              .Include(e => e.Table2)
              .Where(x => x.Date >= '2011-11-09 00:00:00'
                       && x.DateCreated <= '2011-11-09 23:59:59')
              .Where(y => y.Table2.Table3.Any(u => u.Id == 1))
              .Take(10);

score 5 · Accepted Answer · answered Nov 10 '11 at 11:12

One of the things I like most about Linq is that it will combine queries together, so I break a complicated query into subqueries and let Linq recombine them all.

Using that basis, does something like this work.

DateTime startDate = new DateTime(2011,11,9);
DateTime endDate   = new DateTime(2011,11,9,23,59,59);

var table3Ids = (from r in Pos.dbo.Table3 where id = 1 select r.id) ;

var query1  = 
(
    from t1 in Pos.dbo.Table1 
    where t1.Table2.Id == 1 && t1.Date >= startDate && t1.Date <= endDate
    where table3Ids.Contains(t1.Table2.Id)
    group t1 by new { t1.Table2.Id , t1.Table2.Name} into results

    select new 
    { 
        results.Key.Id , 
        results.Key.Name , 
        Total = results.Sum(r=> (r.Column1 + r.Column2 + r.Column3)) 
    } 
);


var query2 = (from r in query1 orderby r.Total descending select r).Take(10);
var query3 = (from r in query2 orderby r.Name select r);

var list = query3.ToList();

the problem here is table 3 is the JOIN table for a many to many relationship which doesn't have class on my C# Entities. Any luck? — gnaungayan, Nov 10 '11 at 17:48
It's not an Entity it's a JOIN Table(many-to-many) that EF 4.1 made between Table1 and Table2 anyway, I got the result using the query just modified some minor stuff. Thanks — gnaungayan, Nov 11 '11 at 19:15

score 0 · Answer 2 · answered Nov 10 '11 at 03:42

Here's my shot - it's a bit hard without knowing the actual data structure or the expected results:

var query = db.Table1
    .Join(db.Table2, table1 => table1.Id, table2 => table2.Id, (t1, t2) => new { 
        Id = t2.Id, 
        Name = t2.Name, 
        Total = (t1.Column1 + t1.Column2 + t1.Column3), 
        Date = t1.Date //you didn't use a table alias for this field, so I'm not sure which table it comes from
        })
    .Where(x => x.Date >= new DateTime(2011, 11, 9)
                && x.DateCreated <= new DateTime(2011, 11, 9, 23, 59, 59))
    .Join(db.Table3, x => x.Id, table3 => table3.Id, (x, t3) => new {
        Id = x.Id,
        Name = x.Name,
        x.Total
    })
    .Where(x => x.Id == 1)
    .OrderByDescending(x => x.Total)
    .ThenBy(x => x.Name)
    .Take(10)
    .ToList()

Because that might be the craziest Linq statement I've ever written, don't assume it'll work the first time, but it's at least a good starting point.

SQL to LINQ Converter

2 Answers2