Extending LINQ to SQL

Last year, Scott Guthrie stated “You can actually override the raw SQL that LINQ to SQL uses if you want absolute control over the SQL executed”, but I can’t find documentation describing an 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
};
}


Which 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
};
}


Which would 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 LINQ to SQL extensibility, other than this one. Any ideas?

Please comment on this question over at StackOverflow.com

One comment on “Extending LINQ to SQL

  1. Unfortunately you can’t actually extend LINQ to SQL in that way. I would imagine Scott was using some prototype bits that didn’t make the final cut although I don’t know for sure.

    Matt Warren has some details upon his blog about writing your own provider but it really is a lot of work.

    [)amien

Comments are closed.