title: How do you extend Linq to SQL?
body: <p>Last year, Scott Guthrie <a href="http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx" rel="noreferrer">stated</a> “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.</p>
  
  <p>I would like to modify the following LINQ to SQL query:</p>
  
  <pre>using (NorthwindContext northwind = new NorthwindContext ()) {
      var q = from row in northwind.Customers
              let orderCount = row.Orders.Count ()
              select new {
                  row.ContactName,
                  orderCount
              };
  }</pre>
  
  <p>Which results in the following TSQL:</p>
  
  <pre>SELECT [t0].[ContactName], (
      SELECT COUNT(*)
      FROM [dbo].[Orders] AS [t1]
      WHERE [t1].[CustomerID] = [t0].[CustomerID]
      ) AS [orderCount]
  FROM [dbo].[Customers] AS [t0]</pre>
  
  <p>To:</p>
  
  <pre>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
              };
  }</pre>
  
  <p>Which <em>would</em> result in the following TSQL:</p>
  
  <pre>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))</pre>
  
  <p>Using:</p>
  
  <pre>public static Table&lt;TEntity&gt; With&lt;TEntity&gt; (
      this Table&lt;TEntity&gt; table,
      params TableHint[] args) where TEntity : class {
  
      //TODO: implement
      return table;
  }
  public static EntitySet&lt;TEntity&gt; With&lt;TEntity&gt; (
      this EntitySet&lt;TEntity&gt; entitySet,
      params TableHint[] args) where TEntity : class {
  
      //TODO: implement
      return entitySet;
  }</pre>
  
  <p>And</p>
  
  <pre>
  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;
      }
  }</pre>
  
  <p>Using some type of LINQ to SQL extensibility, other than <a href="http://blogs.msdn.com/mattwar/archive/2008/05/04/mocks-nix-an-extensible-linq-to-sql-datacontext.aspx" rel="noreferrer">this one</a>. Any ideas?</p>