Tuesday, September 02, 2008 11:37 AM
I'm trying to integrate LINQ to SQL in my project.
I have a lot of indexed views on which i've defined linq classes. Unfortunatly, the select queries give me no way to specify that i want to use the hint (NOEXPAND) on these views.
Am I missing something ?
Tuesday, September 02, 2008 12:55 PMHi Avi
You're not missing anything: LINQ to SQL provides no means of specifying query hints.
Tuesday, September 02, 2008 1:07 PM
Thanks for your quick reply
Will this feature be part of a next release of LINQ to SQL because it is vital when you have indexed views.
Is there no way to hook a piece of code when linq build the SQL statement to specify this hint ?
Tuesday, September 02, 2008 2:09 PMAs a workaround I guess you could write a table-valued function that returned:
select * from MyIndexedView with (noexpand)
and then map the table-valued function to L2S.
Tuesday, September 02, 2008 2:40 PM
OK here is where i stand
Let's assume i have 2 indexed views :
- Occupant (list of persons)
- ActionRequest (list of request)
I have defined 2 table functions in SQL Server (2005) returning these tables with the hint (NOEXPAND)Code Snippet
CREATE FUNCTION [dbo].[fOccupant]
(SELECT * from Occupant (NOEXPAND)
CREATE FUNCTION [dbo].[fActionRequest]
(SELECT * from ActionRequest (NOEXPAND)
then in L2S i added to 2 tables , and then the two functions dropping them on each of the table they represent.
Now the good news : the following query uses the hintCode Snippet
varres = from p in db.fOccupant() join o in db.fActionRequest() on p.KeyOccupant equals o.KeyOccupant where p.LastName == "HAIAT" select p;
And now the bad news, i have to manually do the join because if not Linq will use the base tables instead of the optimized functions
Am I correct ?
Wednesday, September 03, 2008 12:04 AMYes, I think you're correct: association properties are not going to happen with table-valued functions!
Wednesday, September 03, 2008 10:19 AM
Just as a follow up, I think I have found a much better solution than using table function.
The idea is to create a view to encpasulate the hint
something likeCode Snippet
CREATE VIEW vOccupant
SELECT * FROM Occupant (NOEXPAND)
Then I have the opportunity to create relationship between views, and SQL will use the indexes on views
Thursday, September 04, 2008 6:50 AMIndeed - that's clever.