Reputation: 6804

Convert SQL to LINQ to SQL

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?

Upvotes: 7

Answers (4)

Omzig

Reputation: 921

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.

Upvotes: -2