How to use the NOEXPAND hint with Linq to SQL?

Question

I have an indexed view that I need to specify the noexpand hint for in order for it to perform reasonably. Unfortunately as seen with regard to modifying the Linq to SQL generated T-SQL query from the NOLOCK hint it appears that there is no easy way to take advantage of these hints directly or is there?

My thought is that it would make sense to allow customization of this stuff through the use of attributes or declaratively through the dbml. Also since Linq to SQL seems to only work targeting SQL Server it only makes sense that we are also able to leverage these advanced features (if they exists). Regardless of the implementation though I'm interested in any creative ways of solving this problem.

Seen

Answer 1

I've found a workaround which appears to work but requires making a second View for each SQL View that one would want to use the NOEXPAND hint with. In the second view just select all of the fields from the original view and tack on the NOEXPAND hint. Any Linq to SQL query that needs to use the NOEXPAND hint can now just target that view that wraps the original.

More details can be found in this MSDN post.

One down side among others to consider when creating dependent views is that you will have to make sure to apply the creation scripts in the proper dependency order

Does anybody have any better alternatives? I would rather not have to create extra SQL Views just to support usage of this necessary optimizer hint.

Seen

Answer 2

I totally agree, but I do not believe there is such a method. In EF4 you can use the ExecuteStoreCommand that will let you execute SQL directly. That may be your only option if performance is unacceptable.

==EDIT==

You can also do this in LINQ to SQL through the ExecuteQuery method.

http://msdn.microsoft.com/en-us/library/bb399403.aspx

Counterbalance

Now that Entity Framework is becoming more capable I may take a second look at it and consider switching from Linq to SQL but for now unless I can find enough compelling reasons and time to research such conversion then I'm more interested in a solution for Linq to SQL. I'll chalk the ExecuteStoreCommand feature of EF4 up as an interesting advantage to look out for for now. – Seen

Yeah, oops. I have been reading them interchangeably now days! :) I updated my answer with LINQ to SQL. It's never fun to break the architectural mold and do something like execute a SQL string, but considering that you're already tied to SQL Server, the objection is only that of purity not practicality. – Counterbalance

In my particular case I'm dealing with purely deferred and highly dynamic queries so I believe it excludes direct execution techniques such as ExecuteQuery. – Seen