locked
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

    Tuesday, September 02, 2008 11:37 AM

Answers

  •  

    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

     

    Wednesday, September 03, 2008 10:19 AM
  • Yes, I think you're correct: association properties are not going to happen with table-valued functions!

    Joe
    Wednesday, September 03, 2008 12:04 AM

All replies

  • Hi Avi

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

    Joe
    Tuesday, September 02, 2008 12:55 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 ?

     

    I'm desesperated....

     

    Tuesday, September 02, 2008 1:07 PM
  • 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.
    Tuesday, September 02, 2008 2:09 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]

    (

    )

    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

     

     

     

    Tuesday, September 02, 2008 2:40 PM
  • Yes, I think you're correct: association properties are not going to happen with table-valued functions!

    Joe
    Wednesday, September 03, 2008 12:04 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 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

     

    Wednesday, September 03, 2008 10:19 AM
  • Indeed - that's clever.

    Joe
    Thursday, September 04, 2008 6:50 AM