How to include recursion / table valued functions in LINQ To Entities queries with EF4?

In this post, we use Northwind DB with this EDM:

 image

I want to get all orders for an employee or his sub-employees in only one T-SQL query.

Recursion with T-SQL => CTE. The problem is the fact that EF4 doesn’t support recursion.

I found an interesting way to fix it.

First, I write my CTE:

With GetEmployeeAndManagersCTE
AS
(
      SELECT EmployeeID, LastName, FirstName, ManagerID
      FROM Employees
      WHERE EmployeeID = @employeeId
      UNION ALL 
            SELECT E.EmployeeID, E.LastName, E.FirstName, E.ManagerID
            FROM Employees E
            INNER JOIN GetEmployeeAndManagersCTE CTE ON CTE.ManagerID = E.EmployeeID
)

My first problem is the @employeeId. As it is not a constant, I create a table-valued function (better than a stored procedure in order to use it in a query):

CREATE FUNCTION [dbo].[GetEmployeeAndManagers]
(
      @employeeId as int
)
RETURNS 
      @employees TABLE
(
      EmployeeId int,
      LastName nvarchar(20),
      FirstName nvarchar(10),
      ManagerId int
)
AS
BEGIN
      With GetEmployeeAndManagersCTE
      AS
      (
            SELECT EmployeeID, LastName, FirstName, ManagerID
            FROM Employees
            WHERE EmployeeID = @employeeId
            UNION ALL 
                  SELECT E.EmployeeID, E.LastName, E.FirstName, E.ManagerID
                  FROM Employees E
                  INNER JOIN GetEmployeeAndManagersCTE CTE ON CTE.ManagerID = E.EmployeeID
      )

 

      INSERT INTO @Employees SELECT * FROM GetEmployeeAndManagersCTE
      RETURN

END

Now the idea is to use this function in a L2E query. But sadly, L2E with EF4 only supports scalar functions.

In my EDM, I create a new SSDL EntitySet:

<EntitySet Name="GetEmployeeAndManagers" EntityType="MyNorthwindEFModel.Store.Employees" store:Type="Tables" Schema="dbo" />


Then I do the same for the CSDL:



<EntitySet Name="GetEmployeeAndManagers" EntityType="MyNorthwindEFModel.Employee" />


And I map my CSDL EntitySet on my SSDL EntitySet:



<EntitySetMapping Name="GetEmployeeAndManagers">
  <EntityTypeMapping TypeName="MyNorthwindEFModel.Employee">
    <MappingFragment StoreEntitySet="GetEmployeeAndManagers">
      <ScalarProperty Name="EmployeeID" ColumnName="EmployeeID" />
      <ScalarProperty Name="LastName" ColumnName="LastName" />
      <ScalarProperty Name="FirstName" ColumnName="FirstName" />
      <ScalarProperty Name="ManagerID" ColumnName="ManagerID" />
    </MappingFragment>
  </EntityTypeMapping>
</EntitySetMapping>


Now I write the following L2E query:



public IEnumerable<Order> GetOrdersForEmployeeWithRecursion(int employeeId)
{
    using (var context = new MyNorthwindEFEntities())
    {
        IQueryable<Order> query = from o in context.Orders
                                  where context.GetEmployeeAndManagers.Select(e => e.EmployeeID).Where(e => e == o.EmployeeID).Contains(employeeId)
                                  select o;
        ObjectQuery<Order> objectQuery = (ObjectQuery<Order>)query;
        foreach (Order o in objectQuery.UseTableValuedFunction(new KeyValuePair<string, int>("GetEmployeeAndManagers", 1)))
            yield return o;
    }
}


With this UseTableValuedFunction method:



public static IEnumerable<T> UseTableValuedFunction<T>(this ObjectQuery<T> query, IEnumerable<KeyValuePair<string, int>> nbParamsPerFunction)
{
    string sql = query.ToTraceString();
    sql = sql.Replace("@p__linq__", "@p");
    foreach (KeyValuePair<string, int> function in nbParamsPerFunction)
    {
        sql = Regex.Replace(sql, string.Format(@"(\[?{0}\]?)(\s+AS\s+(\[?\w+\]?))?\s*WHERE\s+((\(*[\w\[\].]+\s*=\s*([\w\[\]@.]+)\)*(\s*AND\s*)?)*)", function.Key), m =>
        {
            string condition = m.Groups[4].Value;
            int nbParam = function.Value;
            List<string> functionParams = new List<string>();
            while (nbParam-- != 0)
            {
                Match match = Regex.Match(condition, @"\(*([\w\[\]@.]+)\s*=\s*([\w\[\]@.]+)\)*(\s+AND\s+)?");
                functionParams.Add(match.Groups[1].Value.StartsWith(m.Groups[3].Value) ? match.Groups[2].Value : match.Groups[1].Value);
                condition = condition.Substring(condition.IndexOf(match.Value) + match.Value.Length);
            }
            return string.Format("{0}({1}){2} {3}",
                m.Groups[1],
                functionParams.Aggregate((p1, p2) => string.Concat(p1, ", ", p2)),
                m.Groups[2].Value,
                (condition = condition.TrimStart()).Length == 0 ? "" : string.Concat("WHERE ", condition));
        });
    }
    return query.Context.ExecuteStoreQuery<T>(sql, query.Parameters.Select(p => p.Value).ToArray());
}
public static IEnumerable<T> UseTableValuedFunction<T>(this IQueryable<T> query, IEnumerable<KeyValuePair<string, int>> nbParamsPerFunction)
{
    return ((ObjectQuery<T>)query).UseTableValuedFunction(nbParamsPerFunction);
}
public static IEnumerable<T> UseTableValuedFunction<T>(this IQueryable<T> query, params KeyValuePair<string, int>[] nbParamsPerFunction)
{
    return UseTableValuedFunction<T>(query, (IEnumerable<KeyValuePair<string, int>>)nbParamsPerFunction);
}


I think that using ExecuteStoreQuery method is bad because using it brakes EDM abstraction. However, it is very useful to make up for EF4 lacks.



Here we are. We just integrated our table-valued function in L2E query and recursion in our query.

This entry was posted in 12253, 7671, 7674, 7675, 9104. Bookmark the permalink.

2 Responses to How to include recursion / table valued functions in LINQ To Entities queries with EF4?

  1. bohebolo says:

    As far I know, table valued functions is not supported yet in EF4.
    Am I correct ?

  2. Yes you are but with my code, you can use them

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>