<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>

[{'answer_id': 64612, 'author': 'user8456', 'author_id': 8456, 'author_profile': 'https://Stackoverflow.com/users/8456', 'pm_score': -1, 'selected': False, 'text': '<p><code>DataContext x = new DataContext</code></p>\n\n<p>Something like this perhaps?</p>\n\n<p><code>var a = x.Where().with()</code>...etc </p>\n\n<p>It lets you have a much finer control over the SQL.</p>\n'}, {'answer_id': 67999, 'author': 'DamienG', 'author_id': 5720, 'author_profile': 'https://Stackoverflow.com/users/5720', 'pm_score': 5, 'selected': True, 'text': '<p>The ability to change the underlying provider and thus modify the SQL did not make the final cut in LINQ to SQL.</p>\n'}]
['https://Stackoverflow.com/questions/62963', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5869/']