Ability to set "Query Hints"

[kam99]

trafficstars

Hi, guys.

Does BLToolkit have an ability to gracefully set any of query hints/options, like that: http://msdn.microsoft.com/en-us/library/ms181714.aspx ?

In particular i'm wondering how i can add "OPTION (RECOMPILE)" statement to the end of my query.

Thanks in advance.

[igor-tkachev]

Is this what you are looking for? https://github.com/igor-tkachev/bltoolkit/blob/master/UnitTests/Linq/Model/ParentChild.cs#L503

[kam99]

Looks optimistic! But not exactly what i'm looking for.

I can have "any complexity" level query, say this one:

var query = from e1 in Repository.All<DBEntity1>()
    join e2 in Repository.All<DBEntity2>() on e1.Entity1Id equals e2.Entity1Id
    where e2.SomeFieldId != null
    select new { e1.Entity1Id, e2.Entity2Id };

query.ToList();

and need get an output like that:

exec sp_executesql N'SELECT
    [e1].[Entity1Id],
    [e2].[Entity2Id]
FROM
    [DBEntity1] [e1]
        INNER JOIN [DBEntity2] [e2] ON [e2].[Entity1Id] = [e2].[Entity1Id]
WHERE
    [e2].[SomeFieldId] is not null
OPTION (RECOMPILE)'

[igor-tkachev]

I think that the only way to implement it is to give you access to SQL generation process. Somehow. No idea how.

[NetTecture]

Create a way to inject hints into the process via a Extension method. WithHint (TableHint.NoLocking) for example.

Providers then can decide to honor them or not.

I have similar items - I do a ton of queries in SQL Server "WITH (READCOMMITTED)" (does not LEAVE locks).

[kam99]

2 NetTecture:

As it was pointed here https://github.com/igor-tkachev/bltoolkit/issues/214#issuecomment-16412425 there is a way to implement Table Hint already.

It works fine (at least for me) - for one of my needs i put WITH (NOLOCK) TableExpression attribute on a generic method that gets a table, and can easily reuse it through all my code. Similar way you can apply any Table Hint.

What not supported and what this ticket about is - entire Query (not just a "single" table) Hint/Option.