Use "option(maxrecursion 0)" with EntityFramework EntitySql

Question

I have a SqlServer function that performs a recrusive select with cte based on an input, which is a csv string with ids.

Unfortunately I can't use "option(maxrecursion 0)" inside my function, it must be used when the function is executed. The problem is that I can't find how to use this option with EntityFramework's EntitySql.

Considering my function is called MyRecursiveFunction, here are some code snippets:

public virtual IQueryable<MyFunctionReturnType> ExecuteMyFunction(IObjectContextAdapter objContextAdapter, string csvIds)
{
    var idsParam = new ObjectParameter("idsParam", csvIds);

    // This is the original one, that works, but has no "option(maxrecursion 0)"
    return objContextAdapter.CreateQuery<MyFunctionReturnType>("[MyRecursiveFunction](@idsParam)", idsParam);

    // gives me an error of incorrect syntax near "option"
    return objContextAdapter.CreateQuery<MyFunctionReturnType>("select VALUE tblAlias from [MyRecursiveFunction](@idsParam) as tblAlias OPTION(MAXRECURSION 0)", idsParam);

    // Also gives me syntax error:
    return objContextAdapter.CreateQuery<MyFunctionReturnType>("MyRecursiveFunction(@idsParam) option(maxrecursion 0)", idsParam);
}

Anyone knows how to use option(maxrecursion 0) with entitySql ?

I know I can use the "ExecuteStoreQuery" to perform any sql query I want, but I do need an IQueryable, since this return of "ExecuteMyFunction" will be joined with another IQueryable before materialization.

Please save your time and do not suggest calling ExecuteStoreQuery along with AsQueryable.... I really don't want to materialize the whole result set since I'll materialize only 10 results for paging.

Here is a representation of my TVF:

-- Consider that I have the given table for executing this function.
-- This table has a foreign key to itself, as the data represents a tree, like an organization chart
CREATE TABLE MyTable
(
    Id INT,
    ParentId INT, -- FK to 'MyTable'.'Id',
    Name VARCHAR(400)
)

-- Here is my function definition:
CREATE FUNCTION MyRecursiveFunction (@idsParam VARCHAR(MAX))
RETURNS TABLE
AS
RETURN
(
    -- create a cte for recursively getting the data
    with myCte (id, parentId) as
    (
        SELECT tbl.Id, tbl.ParentId FROM MyTable AS tbl
        -- This function just transform the varchar into a table of "Value"
        INNER JOIN [dbo].[SplitTextIntoTableOfInt](@idsParam, ',') AS ids ON a.ParentId = ids.Value

        UNION ALL

        SELECT a.Id, a.ParentId FROM myCte AS parent
        INNER JOIN MyTable tbl ON tbl.ParentId = parent.Id
    )
    SELECT * FROM myCte -- I can't use 'option(maxrecursion 0)' in here
)

Until you iterate the `IQueryable` or `IEnumerable` by using foreach, `ToList()`, `FirstOrDefault()`... no query will be sent to the database. Have you actually tried the `ExecuteStoreQuery` followed by `AsQueryable()`? I'm not entirely sure on this because I rarely use EF to do sql queries but likely only one query will hit the database. As I said I'm not entirely sure about this so excuse me if I'm wrong :) — Alexander Derck, Feb 05 '16 at 12:35
I tested it. The IEnumerable returned by ExecuteStoreQuery don't get executed until I use it, but as it is an IEnumerable, and not an IQueryable (and it calling "AsQueryable" do not make a difference) it is already transformed in objects in memory, so when I try to join this with another IQueryable and materialize it, which is my scenarion, an exception is thrown. — IPValverde, Feb 05 '16 at 12:56

JotaBe · Answer 1 · 2016-02-05T14:05:28.360

5

The only thing that you can do is to use EF interception and add that option to the EF generated SQL before running it.

To do so, you need to implement the IDbCommandInterceptor interface, and use DbInterception.Add(new YousCommandInterceptor()); to register your interceptor.

Your interceptor can add the option before the query is sent to the server. The SQL query is available in the command parameter of the chosen method ( you should intercept ReaderExecuted(DbCommand, DbCommandInterceptionContext<DbDataReader>))

edited Feb 05 '16 at 14:05

answered Feb 05 '16 at 13:27

JotaBe

38,030
8
98
117

Hi @JotaBe , I'm using a TVF with CodeFirst. The CTE recursion is inside this TVF, that is why I need to specify the `option(maxrecursion 0)`. I can execute the TVF, the problem is executing it with the `option(maxrecursion 0)`. How can I do that? – IPValverde Feb 05 '16 at 13:44
Can you show the code of how you've defined the TVF and how you run it with SQL to specify the option? I don't understand why it isn't inside the function definition itself. – JotaBe Feb 05 '16 at 13:47
It can't be. `option(maxrecursion 0)` cannot be defined inside the TBV, it can only be defined when executing the query. http://stackoverflow.com/questions/7428669/ – IPValverde Feb 05 '16 at 13:55
I've completely changed my answer. You can do it using EF interception, that allows to modify the SQL query before is sent to the server.You have to implement the necessary logic to inclue teh option only when necessary (perhaps by checking that the TVF name is inside the query) – JotaBe Feb 05 '16 at 14:06

score 1 · Answer 2 · edited Feb 05 '16 at 17:34

OPTION(MAXRECURSION 0) is specific to the SQL Server syntax, I don't think EntitySql would ever support this kind of specific syntax. This would make the abstraction too coupled with the underlying data store, and make difficult to support other database servers.

If you're reaching some recursion limit, maybe it's a good idea to review your design, as dropping the limit should make your problems even worse.

Use "option(maxrecursion 0)" with EntityFramework EntitySql

2 Answers2