How do i modify Linq generated sql statement?

Question

I have summarized my problem in following code.

NorthwindDataContext dc = new NorthwindDataContext();
        var query = from c in dc.Customers
                    select c;

Above code is generating following sql statement

    SELECT [t0].[ID], [t0].[FirstName], [t0].[LastName]
FROM [dbo].[Customer] AS [t0]

Now i want to modify the above generated query something like this

SELECT [t0].[ID], [t0].[FirstName], [t0].[LastName] FROM [dbo].[Customer] 
AS [t0] WITH (nolock)

Is it possible in linq to modify the generated query?If yes then how?

possible duplicate of [NOLOCK with Linq to SQL](http://stackoverflow.com/questions/1220807/nolock-with-linq-to-sql) — Henrik P. Hessel, Dec 21 '10 at 12:42

score 4 · Answer 1 · answered Dec 21 '10 at 12:41

You will not be able to modify the generated L2S T-SQL code directly, the way you want (unless you modify the transaction isolation level). However, we've dealt with situations like this, fairly simply, by creating a view with lock hints we want in place and querying the view, instead of the table directly.

score 4 · Accepted Answer · answered Dec 22 '10 at 05:31

I have found a very handy tips for modifying the linq generated sql statement.

NorthwindDataContext db = new NorthwindDataContext();
            if (db.Connection.State == System.Data.ConnectionState.Closed)
                db.Connection.Open();
            var cmd = db.GetCommand(db.Customers.Where(p => p.ID == 1));
            cmd.CommandText = cmd.CommandText.Replace("[Customers] AS [t0]", "[Customers] AS [t0] WITH (NOLOCK)");
            var results = db.Translate(cmd.ExecuteReader());