How to get linq to produce exactly the sql I want?

Question

It is second nature for me to whip up some elaborate SQL set processing code to solve various domain model questions. However, the trend is not to touch SQL anymore. Is there some pattern reference or conversion tool out there that helps convert the various SQL patterns to Linq syntax?

I would look-up ways to code things like the following code: (this has a sub query):

SELECT * FROM orders X WHERE
(SELECT COUNT(*) FROM orders Y
WHERE Y.totalOrder > X.totalOrder) < 6

(Grab the top five highest total orders with side effects)

Alternatively, how do you know Linq executes as a single statement without using a debugger? I know you need to follow the enumeration, but I would assume just lookup the patterns somewhere.

This is from the MSDN site which is their example of doing a SQL difference. I am probably wrong, but I wouldn't think this uses set processing on the server (I think it pulls both sets locally then takes the difference, which would be very inefficient). I am probably wrong, and this could be one of the patterns on that reference.

SQL difference example:

var differenceQuery =
(from cust in db.Customers
select cust.Country)
.Except
    (from emp in db.Employees
    select emp.Country);

Thanks

-- Update:

-- Microsoft's 101 Linq Samples in C# is a closer means of constructing linq in a pattern to produce the SQL you want. I will post more as I find them. I am really looking for a methodology (patterns or a conversion tool) to convert SQL to Linq.

-- Update (sql from Microsoft's difference pattern in Linq):

SELECT DISTINCT [t0].[field] AS [Field_Name]
FROM [left_table] AS [t0]
WHERE NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM [right_table] AS [t1]
WHERE [t0].[field] = [t1].[field]
))

That's what we wanted, not what I expected. So, that's one pattern to memorize.

Answer 1

If you have hand-written SQL, you can use ExecuteQuery, specifying the type of "row" class as a function template argument:

var myList = DataContext.ExecuteQuery<MyRow>(
    "select * from myview");

The "row" class exposes the columns as public properties. For example:

public class MyRow {
    public int Id { get; set; }
    public string Name { get; set; }
    ....
}

You can decorate the columns with more information:

public class MyRow {
    ....
    [Column(Storage="NameColumn", DbType="VarChar(50)")]
    public string Name { get; set; }
    ....
}

In my experience linq to sql doesn't generate very good SQL code, and the code it does generate breaks down for large databases. What linq to sql does very well is expose stored procedures to your client. For example:

var result = DataContext.MyProcedure(a,b,c);

This allows you to store SQL in the database, while having the benefits of an easy to use, automatically generated .NET wrapper.

To see the exact SQL that's being used, you can use the SQL Server Profiler tool:

http://msdn.microsoft.com/en-us/library/ms187929.aspx

The Linq-to-Sql Debug Visualizer:

http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx

Or you can write custom code to log the queries:

http://goneale.wordpress.com/2008/12/31/log-linq-2-sql-query-execution-to-consoledebug-window/

Answer 2

This is why Linq Pad was created in the first place. :) It allows you to easily see what the output is. What the results of the query would be etc. Best is it's free. Maybe not the answer to your question but I am sure it could help you.

Answer 3

If you know exactly the sql you want, then you should use ExecuteQuery.

I can imagine a few ways to translate the query you've shown, but if you're concerned that "Except" might not be translated.

1. Test it. If it works the way you want then great, otherwise:

2. Rewrite it with items you know will translate, for example:

   db.Customers.Where(c => !db.Employees.Any(e => c.Country == e.Country) );