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
)

Answer 1

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>))

Answer 2

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.