How to extend Linq to SQL?

Question

Last year, Scott Guthrie said, "You can really override raw SQL that uses LINQ to SQL if you want absolute control over SQL to be performed," but I can't find documentation that describes the extensibility method.

I would like to modify the following LINQ to SQL query:

  using (NorthwindContext northwind = new NorthwindContext ()) {
     var q = from row in northwind.Customers
             let orderCount = row.Orders.Count ()
             select new {
                 row.ContactName,
                 orderCount
             };
 } 

This results in the following TSQL:

  SELECT [t0]. [ContactName], (
     SELECT COUNT (*)
     FROM [dbo]. [Orders] AS [t1]
     WHERE [t1]. [CustomerID] = [t0]. [CustomerID]
     ) AS [orderCount]
 FROM [dbo]. [Customers] AS [t0] 

To:

  using (NorthwindContext northwind = new NorthwindContext ()) {
     var q = from row in northwind.Customers.With (
                         TableHint.NoLock, TableHint.Index (0))
             let orderCount = row.Orders.With (
                         TableHint.HoldLock) .Count ()
             select new {
                 row.ContactName,
                 orderCount
             };
 } 

This will result in the following TSQL:

  SELECT [t0]. [ContactName], (
     SELECT COUNT (*)
     FROM [dbo]. [Orders] AS [t1] WITH (HOLDLOCK)
     WHERE [t1]. [CustomerID] = [t0]. [CustomerID]
     ) AS [orderCount]
 FROM [dbo]. [Customers] AS [t0] WITH (NOLOCK, INDEX (0)) 

Using:

  public static Table <TEntity> With <TEntity> (
     this Table <TEntity> table,
     params TableHint [] args) where TEntity: class {

     // TODO: implement
     return table;
 }
 public static EntitySet <TEntity> With <TEntity> (
     this EntitySet <TEntity> entitySet,
     params TableHint [] args) where TEntity: class {

     // TODO: implement
     return entitySet;
 } 

and

 public class TableHint {
     // TODO: implement
     public static TableHint NoLock;
     public static TableHint HoldLock;
     public static TableHint Index (int id) {
         return null;
     }
     public static TableHint Index (string name) {
         return null;
     }
 } 

Using some type of extensibility LINQ to SQL, besides this . Any ideas?

Answer 1

The ability to change the underlying provider and thus change the SQL has not made a final reduction in LINQ to SQL.

Answer 2

Matt Warren's blog has everything you need:

http://blogs.msdn.com/mattwar/

Answer 3

DataContext x = new DataContext;

// Is something like this possible?

var a = x.Where (). with () ... etc.

let you have much finer control over sql.

Answer 4

You want to translate the expression tree into SQL ... You need to implement your own IQueryProvider

IQueryProvider Help
How

MSDN How