LINQ to SQL and NOEXPAND

Question

 

Hi,

 

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 ?

 

Cheers

 

Avi

Answer 1

Hi Avi

You're not missing anything: LINQ to SQL provides no means of specifying query hints.

Joe

Answer 2

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 ?

 

I'm desesperated....

 

Answer 3

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

Answer 4

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]

(

)

RETURNS TABLE

AS

RETURN

(

SELECT * from Occupant (NOEXPAND)

)

 

GO

 

CREATE FUNCTION [dbo].[fActionRequest]

(

)

RETURNS TABLE

AS

RETURN

(

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 hint

 

Code Snippet

var res = 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 ?

 

Cheers

 

 

 

Answer 5

Yes, I think you're correct: association properties are not going to happen with table-valued functions!

Joe

Answer 6

 

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 like

 

Code Snippet

CREATE VIEW vOccupant

AS

SELECT * FROM Occupant (NOEXPAND)

 

 

 

 

Then I have the opportunity to create relationship between views, and SQL will use the indexes on views

 

 

Cheers

 

Answer 7

Indeed - that's clever.

Joe