How to get linq to create exactly sql that I want?

Question

For me, the second character is to crack the complex SQL set processing code to solve various domain model issues. However, the tendency is not to touch SQL anymore. Is there a link to a template or conversion tool out there that helps convert various SQL templates to Linq syntax?

I would look for ways to encode things like the following code: (this has a subquery):

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

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

Alternatively, as you know, Linq is executed as a single statement without using a debugger? I know you need to keep track of the listing, but I would suggest that you are just looking at the templates somewhere.

This is from the MSDN site, which is their example of making SQL difference. I am probably mistaken, but I would not think that it uses the given processing on the server (I think that it pulls both sets locally and then accepts the difference, which would be very inefficient). I am probably mistaken, and this may be one of the patterns in this link.

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 101 Linq Samples in C # is a closer linq builder in the SQL template you want. I will write more when I find them. I am really looking for a methodology (templates or conversion tool) for converting SQL to Linq.

- Update (sql from Microsoft difference template 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] )) 

This is what we wanted, and not what I expected. So this is one pattern to remember.

Answer 1

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

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

The row class exposes 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 additional information:

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

In my experience, linq to sql does not generate very good SQL code, and the code it creates breaks into large databases. What makes linq to sql very good is exposing the stored procedures to your client. For example:

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

This allows you to store SQL in a database, while taking advantage of the easy-to-use, automatically created .NET shell.

To see the exact SQL that is used, you can use the SQL Server Profiler tool:

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

Linq-to-Sql Debug Visualization Tool:

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

Or you can write your own code to register requests:

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

Answer 2

This is why the Linq Pad was created in the first place. :) This allows you to easily see what the output is. What will be the query results, etc. Best of all, it's free. This may not be the answer to your question, but I'm sure it can help you.

Answer 3

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

I can imagine several ways to translate the request you requested, but if you are concerned that the “Except” cannot be translated.

• Check this. If it works the way you want, then fine, otherwise:

• Rewrite it using elements that you know will translate, for example:

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

Answer 4

If you are concerned about the generated TSQL, I would suggest formalizing queries in stored procedures or UDF and accessing them through the data context. The UDF approach has slightly better metadata and the ability to link (compared to the stored procedure) - for example, you can add the Where / Skip / Take add-on, etc. Into the UDF query and run it in the database (but the last time I checked only LINQ-to-SQL (not Entity Framework) that supports the use of UDF).

You can also use ExecuteQuery , but there are advantages to allowing the database to have fixed queries.

Returning to what TSQL did ... with LINQ-to-SQL, you can assign any TextWriter (e.g. Console.Out ) to DataContext.Log .

Answer 5

I believe the best way is to use stored procedures. In this case, you have full control over SQL.