Convert SQL to LINQ to SQL

Question

I'm having great trouble in converting the following SQL to LINQ to SQL, any one able to help out?

SELECT     dbo.ExpensesGroup.ExpenseGroupId, dbo.ExpensesGroup.Title, SUM(dbo.Expenses.Amount) AS TotalAmount, MAX(dbo.Expenses.DateLastTickled) 
                      AS LastTickledDate, MAX(dbo.ExpensesGroup.DateTime) AS Date, Username
FROM         dbo.Expenses INNER JOIN
                      dbo.ExpensesGroup ON dbo.Expenses.ExpenseId = dbo.ExpensesGroup.ExpensesId
WHERE     dbo.Expenses.Status = 'AwaitingApproval' or dbo.Expenses.Status = 'AwaitingApprovelGrouping'
GROUP BY dbo.ExpensesGroup.ExpenseGroupId, dbo.ExpensesGroup.Title, dbo.Expenses.Username
ORDER BY MAX(dbo.ExpensesGroup.DateTime) DESC, dbo.ExpensesGroup.Title

Or even better anyone know of an automatic SQl to LINQ converter?

Answer 1

See this existing thread.

If you decide to do it by hand, Linqpad should be useful.

Answer 2

I tried linqpad but its not for converting SQL to linq but its actual use is to replace sql with linq to query your database

however I think linqer is the right choice if you like to convert SQL query to LINQ. you can download it from their official site.

http://www.sqltolinq.com/

Answer 3

linqpad don't convert.

free converter

http://windowsphone.interoperabilitybridges.com/articles/sql-to-linq-converter

Note: Run with SQLite in Windows, not WindowsPhone necesary

Answer 4

To go from LINQ -> SQL try this:

        var db = new DBModelDataContext();
        StringBuilder sb = new StringBuilder();
        StringWriter sw = new StringWriter(sb);
        db.Log = sw; //set the writer

        var items = (from rec in db.Table1
                     select rec).ToList();

        var sql = sb.ToString(); //here is the SQL from LINQ.