Use option (maxrecursion 0) "with EntityFramework EntitySql

Question

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

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

Given that my function is called MyRecursiveFunction , here are a few snippets of code:

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

Does anyone know how to use option(maxrecursion 0) with entitySql?

I know that I can use "ExecuteStoreQuery" to execute any SQL query that I want, but I need IQueryable, since this return of "ExecuteMyFunction" will be connected to another IQueryable before materializing .

Please save your time and do not suggest calling ExecuteStoreQuery together with AsQueryable .... I really do not want to materialize the whole set of results, since I only materialize 10 results for paging.

Here is a view 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 you can do is use EF hooking and add this option to the EF generated SQL before running.

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

Your interceptor may add this parameter before sending a request to the server. The SQL query is available in the command parameter of the selected method (you must intercept ReaderExecuted(DbCommand, DbCommandInterceptionContext<DbDataReader>) )

Answer 2

OPTION(MAXRECURSION 0) specific to SQL Server syntax, I don't think EntitySql has ever supported this type of syntax. This would make the abstraction too related to the main data warehouse and make it difficult to support other database servers.

If you have reached the limit of recursion, it might be a good idea to consider your design, as dropping the limit should make your problems even worse.