Matthieu MEZIL

LINQ To Entities

Cette semaine je suis à Montréal en tant que speaker sur Entity Framework pour l’évènement confoo. J’en profite pour remercier les organisateurs de cet évènement de m’avoir fait confiance et Access-IT de m’avoir permis d’y participer.

En parallèle, j’ai profité de ma venu pour enregistrer un podcast Visual Studio Talk Show, avec Guy Barette et Mario Cardinal les célèbres animateurs de cette émission culte, qui devrait bientôt être en ligne et j’ai également animé une journée d’ateliers Entity Framework pour la communauté .NET de Montréal.

J’en profite pour remercier Guy de m’avoir donné cette occasion.

Cette journée d’ateliers c’est décomposée en 3 parties :

• Le mapping
• Le requêtage
• Les template T4

Comme promis, je vous met à disposition le pps et surtout la correction des requêtes L2E.

Enjoy

 

·        Parcourez l’ensemble des clients et affichez leur type dans la console

using (var context = new MyNorthwindEFEntities())
{
    foreach (var c in context.Customers)
        Console.WriteLine(c.GetType());
}

Parmi les DAL.Customer, vous devriez voir quelques DAL.Member.

 

·        Récupérez le nom des clients ayant une carte de membre

from c in context.Customers.OfType<Member>()
select c.ContactName;

 

·        Récupérez les sociétés des clients habitant Montréal, Québec, Canada

string city = "Montréal";
string region = "Québec";
string country = "Canada";

from c in context.Customers
where c.Address.City == city && c.Address.Region == region && c.Address.Country == country
select c.CompanyName;

 

·        Récupérez les clients triés par nom

from c in context.Customers
orderby c.ContactName
select c;

 

·        Récupérez le nom et la société des clients habitant Montréal

string city = "Montréal";

 

from c in context.Customers
where c.Address.City == city
select new { c.CompanyName, c.ContactName };

 

·        Récupérez les catégories avec leur nombre de produits triées par leur nombre de produits (décroissant)

from c in context.Categories
let nbProducts = c.Products.Count
orderby nbProducts descending
select new { Category = c, NbProducts = nbProducts };

 

·        Récupérer les vendeurs avec leur CA généré triés par CA (décroissant)

from e in context.Employees
let sold = e.Orders.Sum(o => o.OrderDetails.Sum(od => (double)od.Quantity * (double)od.UnitPrice * (1D - od.Discount)))
orderby sold descending
select new { Employee = e, Sold = sold };

 

·        Récupérez du 21^ème au 30^ème meilleurs clients (inutile de récupérer le montant dépensé)

int skip = 20;
int take = 10;

 

(from c in context.Customers
 orderby c.Orders.Sum(o => o.OrderDetails.Sum(od => (double)od.Quantity * (double)od.UnitPrice * (1D - od.Discount))) descending
 select c).Skip(skip).Take(take);

 

·        Récupérez le meilleur vendeur par catégorie avec le CA

from c in context.Categories
let bestEmployee = 
    (from e in context.Employees
     let sold = e.Orders.Sum(o => o.OrderDetails.Where(od => od.Product.CategoryID == c.CategoryID).Sum(od => (double)od.Quantity * (double)od.UnitPrice * (1D - od.Discount)))
     where sold != null
     orderby sold descending
     select new { Employee = e, Sold = sold }).FirstOrDefault() 
where bestEmployee.Sold != null
select new { Category = c, BestEmployee = bestEmployee.Employee, Sold = bestEmployee.Sold };

 

·        Récupérez les 10 meilleures dates de vente avec le montant des ventes

int take = 10;

 

var q = from o in context.Orders
             select new { o.OrderDate, Amount = o.OrderDetails.Sum(od => (double)od.UnitPrice * od.Quantity * (1D - od.Discount)) };

var q2 = (from o in q
                group o by o.OrderDate into g
                let sold = g.Sum(o => o.Amount)
                orderby sold descending
                select new { Date = g.Key, Sold = sold }).Take(take);

 

·        Récupérez les 3 meilleures années de vente

int take = 3;

 

var q = from o in context.Orders
             select new { o.OrderDate, Amount = o.OrderDetails.Sum(od => (double)od.UnitPrice * od.Quantity * (1D - od.Discount)) };

var q2 = (from o in q
                group o by o.OrderDate.Value.Year into g
                let sold = g.Sum(o => o.Amount)
                orderby sold descending
                select new { Year = g.Key, Sold = sold }).Take(take);

 

·        Récupérez la ou les (en cas d’égalité) meilleure(s) catégorie(s) de chaque vendeur

from e in context.Employees
let categories =
    from c in context.Categories
    let sold = c.Products.Sum(p => p.OrderDetails.Where(od => od.Order.EmployeeID == e.EmployeeID).Sum(od => (double)od.Quantity * (double)od.UnitPrice * (1D - od.Discount)))
    where sold != null
    select new { Category = c, Sold = sold }
let maxSold = categories.Select(c => c.Sold).Max()
select new { Employee = e, BestCategories = categories.Where(c => c.Sold == maxSold).Select(c => c.Category) };

 

·        Récupérez la liste des employés encore en activé avec leur ancienneté (par rapport à la date de la base de données) triés par ancienneté

Ouvrez l’edmx en mode xml (click droit, open with, Xml (Text) Editor).

Allez dans la partie CSDL puis dans l’élémént Schema.

Ajoutez-y le code suivant :

<Function Name="DiffYearsToCurrent" ReturnType="Int32">
  <Parameter Name="date" Type="DateTime" />
  <DefiningExpression>
    DiffYears(date, CurrentDateTime())
  </DefiningExpression>
</Function>

Ensuite ajoutez une nouvelle classe DateTimeExtension avec le code suivant :

public static class DateTimeExtension
{
    [EdmFunction("MyNorthwindEFModel", "DiffYearsToCurrent")]
    public static int DiffYearsToCurrent(this DateTime p)
    {
        throw new NotSupportedException();
    }
}

Vous pouvez maintenant utiliser la méthode DiffYearsToCurrent dans vos requêtes LINQ To Entities.

from e in context.Employees.OfType<EmployeeInActivity>()
where e.HireDate != null
let nbYears = e.HireDate.Value.DiffYearsToCurrent()
orderby nbYears descending
select new { Employee = e, HireOld = nbYears };

 

·        Afin de ne pas s’embêter avec le e.HireDate != null et le .Value, rajoutez le directement dans le modèle afin de filtrez tous les employés

Sur le mapping de l’entity type Employee, rajoutez la condition HireDate Is Not Null (avec la fenêtre de mapping) et passez la propriété HireDate à Not Nullable (avec la fenêtre de propriétés)

 

·        Récupérez de nouveau la liste des employés encore en activé avec leur ancienneté (par rapport à la date de la base de données) triés par ancienneté

from e in context.Employees.OfType<EmployeeInActivity>()
let nbYears = e.HireDate.DiffYearsToCurrent()
orderby nbYears descending
select new { Employee = e, HireOld = nbYears };

 

·        Récupérez les employés en activité ayant plus de 10 ans d’ancienneté avec l’ensemble de leurs Orders chargés (en une seule requête SQL)

int nbYears = 10;

 

from e in context.Employees.OfType<EmployeeInActivity>().Include("Orders")
where e.HireDate.DiffYearsToCurrent() >= nbYears
select e;

 

·        Récupérez les employés dont le prénom ne contient que des caractères alphabétique (ie sans accent ni caractère spéciaux)

from e in context.Employees.AsEnumerable()
where Regex.IsMatch(e.FirstName, "^[A-Za-z]*$")
select e;

 

·        Récupérez les employés en activité ayant plus de 10 ans d’ancienneté avec leurs 10 dernières ventes chargées (en une seule requête SQL)

int nbYears = 10;
int take = 10;

 

(from e in context.Employees.OfType<EmployeeInActivity>()
 where e.HireDate.DiffYearsToCurrent() >= nbYears
 select new
 {
     Employee = e,
     TenLastOrders = (from o in e.Orders
                                 orderby o.OrderDate descending
                                 select o).Take(take)
 }).AsEnumerable().Select(e => e.Employee);

 

·        Récupérez les employés en activité ayant plus de 10 ans d’ancienneté avec leurs 10 dernières ventes chargées avec le détail et le client de celles-ci (en une seule requête SQL)

int nbYears = 10;
int take = 10;

 

(from e in context.Employees.OfType<EmployeeInActivity>()
 where e.HireDate.DiffYearsToCurrent() >= nbYears
 select new
 {
     Employee = e,
     TenLastOrders = (from o in e.Orders
                                orderby o.OrderDate descending
                                select new { Order = o, o.OrderDetails, o.Customer }).Take(take)
 }).AsEnumerable().Select(e => e.Employee);

 

·        Récupérez les Orders des employees en activité depuis moins de 3 ans ou plus en activité depuis plus de 3 ans

var q1 = from o in context.Orders
         where o.EmployeeID != null && o.Employee is EmployeeInActivity && (int)(o.Employee.HireDate.DiffDaysFromNow() / 365.25) < 3
         select o;

//Cast in OutEmployee is not supported by L2E with EF4 RC (only cast on primitive type is supported)
//var q2 = from o in context.Orders
//         where o.Employee != null && o.Employee is OutEmployee && (int)(((OutEmployee)o.Employee).OutDate.DiffDaysFromNow() / 365.25) > 3
//         select o;

var q2 = (from e in context.Employees.OfType<OutEmployee>()
          where (int)(e.OutDate.DiffDaysFromNow() / 365.25) > 3 
          select e.Orders).SelectMany(o => o);

var q = q1.Union(q2);

Publié dimanche 14 mars 2010 03:51 par Matthieu MEZIL