Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

Abonnements

EF4 : comment inclure des table valued function avec LINQ To Entities / comment faire des requêtes récursives avec LINQ To Entities ?

Dans le cadre de ce post, j’utilise la base Northwind avec l’EDM suivant :

 image_thumb[3]

L’objectif de ce post est de récupérer les commandes pour un employé donné ou un de ses sous-employés en une seule requête SQL avec SQL Server.

Récursivité avec T-SQL => CTE. Le problème c’est qu’EF4 ne supporte pas la récursivité.

Mon objectif était donc de trouver une feinte pour intégrer les requêtes récursives.

Pour commencer, voici ma 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
)

Premier problème : @employeeId. Comme ce n’est pas une constante, je passe par une table-valued function (mieux qu’une proc stock pour pouvoir l’intégrer dans une requête SQL):

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

Maintenant l’idée est d’utiliser cette fonction dans une requête L2E. Mais malheureusement, avec EF4, L2E ne supporte que les fonctions qui retournent des types scalaires.

Et c’est là que la feinte commence.

Dans mon EDM, je crée un nouveau EntitySet dans le SSDL :

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

Je fais la même chose dans le CSDL :

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

puis je mappe mon CSDL EntitySet sur le 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>

Maintenant j’écris la requête L2E suivante :

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

Qui utilise la méthode UseTableValuedFunction suivante :

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

Je pense qu’utiliser la méthode ExecuteStoreQuery est une mauvaise chose en générale car ça casse l’abstraction apportée par l’EDM. Cependant, c’est très utile pour contourner les lacunes d’EF4.

Voilà, nous venons d’intégrer une table-valued function dans L2E et permis de faire de la récursivité.

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :

Publié jeudi 17 juin 2010 08:56 par Matthieu MEZIL

Commentaires

Pas de commentaires

Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01