How do I get LinqToSql to pass “index hints” to sql server?

Question

As we can’t trust our customers to update the index stats etc in sql server, we have in the past had to use index hints. (As some of our customers are still on Sql Server 2000, we also can’t depend on the better query optimizer in later version of Sql Server).

So how do I pass in index hints when using LinqToSql to build a query rather than raw Sql?

Answer 1

Unfortunately this is not possible. The only thing you can do is influence the transaction isolation level. See an example of this here.

Answer 2

This is not something that is supported, you only options over a query is to set the transaction isolation level, but that doesn't help with this issue.

I would recommend one of two courses of action is this is something that you really need to do.

1. You can write a stored procedure and handle this within your procedure and use LINQ to call it....

2. Move away from LINQ to SQL

Personally, IF this is really necessary I would recommend option two. If in fact you must take that level of action/control over your database calls, auto-generated SQL is going to be problematic for you at later points as well.