Le titre peut paraître bizarre mais c’est un vrai besoin rencontré chez un client pour lequel je vais utiliser Roslyn.
Imaginez le scénario suivant : dans un projet, il y a des développeurs et des experts fonctionnels. Les experts fonctionnels doivent "développer" des Workflows. Les développeurs vont développer des méthodes qui seront utilisées dans les workflows.
Le problème avec ça c’est les InvokeActivity. Pour les utiliser, ils doivent connaîtrent la signature de la méthode pour spécifier les bons types des paramètres / résultat. Un autre problème c’est qu’il faut écrire manuellement le nom de la méthode avec potentiellement le risque de faute de frappe / mise à jour.
Les experts fonctionnels préfèreraient faire du drag and drop des activités dans le workflow.
Donc mon idée est d’encapsuler les InvokeActivity dans des Activity. Maintenant, ça peut être très pénible pour les développeurs de faire ces activités.
Pour cela, il serait plus simple que les développeurs décorent les méthodes en utilisant un Attribut et ils pourraient utiliser un T4 pour générer ces activités.
Le T4 utilise Roslyn pour récupérer les informations sur les méthodes.
Ce T4 va utiliser le ttinclude d’Entity Framework EF.Utility.CS.ttinclude qui intègre déjà la création de plusieurs fichiers depuis un T4 (dans mon casje veux un fichier par méthode).
<#@ include file="EF.Utility.CS.ttinclude"#>
Ensuite, le T4 référence les assemblies de Roslyn :
<#@ assembly name="C:\Program Files (x86)\Reference Assemblies\Microsoft\Roslyn\v1.0\Roslyn.Compilers.dll"#>
<#@ assembly name="C:\Program Files (x86)\Reference Assemblies\Microsoft\Roslyn\v1.0\Roslyn.Compilers.CSharp.dll"#>
<#@ assembly name="C:\Program Files (x86)\Reference Assemblies\Microsoft\Roslyn\v1.0\Roslyn.Services.dll"#>
<#@ assembly name="C:\Program Files (x86)\Reference Assemblies\Microsoft\Roslyn\v1.0\Roslyn.Services.CSharp.dll"#>
<#@ assembly name="C:\Program Files (x86)\Reference Assemblies\Microsoft\Roslyn\v1.0\Roslyn.Services.VisualBasic.dll"#>
Le chemin vers la solution et le nom de projet sont spécifiés au début du T4. Donc maintenant, on peut charger la solution en utilisant Roslyn et récupérer les méthodes décorées avec l’attribut dans le projet spécifié.
var solution = Solution.Load(solutionPath);
var project = solution.Projects.First(p => p.AssemblyName == projectAssemblyName);
foreach (var document in project.Documents)
{
//…
}
Ensuite, pour identifier les méthodes, on utilisera un SyntaxVisitor:
public class InvokeActivityAttributeVisitor : SyntaxVisitor<object>
{
protected override object VisitCompilationUnit(CompilationUnitSyntax node)
{
foreach (var n in node.ChildNodes())
Visit(n);
return null;
}
protected override object VisitNamespaceDeclaration(NamespaceDeclarationSyntax node)
{
foreach (var n in node.ChildNodes())
Visit(n);
return null;
}
protected override object VisitTypeDeclaration(TypeDeclarationSyntax node)
{
foreach (var n in node.ChildNodes())
Visit(n);
return null;
}
protected override object VisitMethodDeclaration(MethodDeclarationSyntax node)
{
if (node.Modifiers.Any(st => st.Kind == SyntaxKind.PublicKeyword) && node.Attributes.Any(a => a.Attributes.Any(a2 => Regex.IsMatch(a2.Name.GetFullText(), @"^(Roslyn.WF.ActivityGenerator.)?InvokeActivity$"))))
{
string methodName = node.Identifier.GetText();
var returnTypeAsPredefinedTypeSyntax = node.ReturnType as PredefinedTypeSyntax;
if (returnTypeAsPredefinedTypeSyntax == null || returnTypeAsPredefinedTypeSyntax.Keyword.Kind != SyntaxKind.VoidKeyword)
{
//…
}
foreach (var parameter in node.ParameterList.Parameters)
{
//…
}
}
}
}
Maintenant le point important est de connaître le namespace et l’assembly des types utilisés dans les méthodes. Pour cela, on utilisera les symbols de compilation :
_syntaxTree = (SyntaxTree)_document.GetSyntaxTree();
_semanticModel = _document.Project.GetCompilation().GetSemanticModel(_syntaxTree);
var returnTypeSymbol = _semanticModel.GetSemanticInfo(node.ReturnType).Symbol;
string returnTypeAssemblyName = returnTypeSymbol.ContainingAssembly.AssemblyName.Name;
string returnTypeNamespaceName = returnTypeSymbol.ContainingNamespace.ToString();
Il faut également savoir si les paramètres sont en In/Out/InOut:
parameter.Modifiers.Any(st => st.Kind == SyntaxKind.RefKeyword) ? Direction.InOut : (parameter.Modifiers.Any(st => st.Kind == SyntaxKind.OutKeyword) ? Direction.Out : Direction.In)
Le reste du code est basique, utilisé pour générer les activités.
Vous pouvez télécharger le code ici.
On a souvent le problème suivant avec SL4 : dans le Model on récupère une collection d’entités et dans le ViewModel, on veut récupérer cette collection en y ajoutant de l’intelligence (par exemple des propriétés calculées).
Dans ce cas, il faut gérer deux collections et propager les modifications dans les deux sens. Une bonne pratique consiste à utiliser des "RelayCollection" pour s’occuper de cette propagation.
En .NET, on peut ajouter de "fausses" propriétés utilisées pour le binding en implémentant l’interface ICustomTypeDescriptor mais celle-ci n’existe pas dans Silverlight.
Cependant, avec SL5, on peut utiliser l’interface ICustomTypeProvider.
J’ai repris le code d’Alexandra et j’ai tenté de l’améliorer.
Cela nous donne le code suivant :
public abstract class DynamicBaseType
{
public abstract object GetPropertyValue(string propertyName);
public abstract void SetPropertyValue(string propertyName, object value);
}
public abstract class DynamicBaseType<T> : DynamicBaseType, ICustomTypeProvider, INotifyPropertyChanged
where T : DynamicBaseType<T>
{
private static List<CustomPropertyInfo> _customProperties = new List<CustomPropertyInfo>();
private Dictionary<string, object> _customPropertyValues;
private CustomType _customtype;
protected DynamicBaseType()
{
_customPropertyValues = new Dictionary<string, object>();
foreach (var property in _customProperties)
_customPropertyValues.Add(property.Name, null);
}
public static void AddProperty(string name, Type type, object value = null, List<Attribute> attributes = null)
{
if (!CheckIfNameExists(name))
_customProperties.Add(new CustomPropertyInfo(name, type, value, attributes));
}
public static void AddProperty<V>(string name, Func<T, V> get, Action<T, V> set = null, List<Attribute> attributes = null, string[] properties = null)
{
if (!CheckIfNameExists(name))
_customProperties.Add(new CustomPropertyInfo<V>(name, get, set, attributes, properties));
}
private static bool CheckIfNameExists(string name)
{
if (_customProperties.Select(p => p.Name).Contains(name) || typeof(T).GetProperties().Select(p => p.Name).Contains(name))
throw new Exception("The property with this name already exists: " + name);
return false;
}
private bool ValidateValueType(object value, Type type)
{
if (value == null)
{
if (!type.IsValueType)
return true;
return (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>));
}
return type.IsAssignableFrom(value.GetType());
}
public override object GetPropertyValue(string propertyName)
{
object customPropertyValue;
if (_customPropertyValues.TryGetValue(propertyName, out customPropertyValue))
return customPropertyValue ?? _customProperties.First(p => p.Name == propertyName).GetDefaultValue(this);
throw new Exception("There is no property " + propertyName);
}
public override void SetPropertyValue(string propertyName, object value)
{
CustomPropertyInfo propertyInfo = _customProperties.FirstOrDefault(prop => prop.Name == propertyName);
object customPropertyValue;
if (!_customPropertyValues.TryGetValue(propertyName, out customPropertyValue))
throw new Exception("There is no property " + propertyName);
if (ValidateValueType(value, propertyInfo.PropertyType))
{
if (customPropertyValue != value)
{
_customPropertyValues[propertyName] = value;
OnPropertyChanged(propertyName);
}
}
else throw new Exception("Value is of the wrong type or null for a non-nullable type.");
}
public PropertyInfo[] GetProperties()
{
return this.GetCustomType().GetProperties();
}
public Type GetCustomType()
{
return _customtype ?? (_customtype = new CustomType(typeof(T)));
}
protected virtual void OnPropertyChanged(string propertyName)
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
foreach (var dependantCustomPropertyInfo in _customProperties.OfType<IDependantCustomPropertyInfo>().Where(dcpi => dcpi.Properties.Contains(propertyName)))
PropertyChanged(this, new PropertyChangedEventArgs(dependantCustomPropertyInfo.Name));
}
}
public event PropertyChangedEventHandler PropertyChanged;
private class CustomType : Type
{
Type _baseType;
public CustomType(Type delegatingType)
{
_baseType = delegatingType;
}
public override Assembly Assembly
{
get { return _baseType.Assembly; }
}
public override string AssemblyQualifiedName
{
get { return _baseType.AssemblyQualifiedName; }
}
public override Type BaseType
{
get { return _baseType.BaseType; }
}
public override string FullName
{
get { return _baseType.FullName; }
}
public override Guid GUID
{
get { return _baseType.GUID; }
}
protected override TypeAttributes GetAttributeFlagsImpl()
{
throw new NotImplementedException();
}
protected override ConstructorInfo GetConstructorImpl(BindingFlags bindingAttr, Binder binder, CallingConventions callConvention, Type[] types, ParameterModifier[] modifiers)
{
throw new NotImplementedException();
}
public override ConstructorInfo[] GetConstructors(BindingFlags bindingAttr)
{
return _baseType.GetConstructors(bindingAttr);
}
public override Type GetElementType()
{
return _baseType.GetElementType();
}
public override EventInfo GetEvent(string name, BindingFlags bindingAttr)
{
return _baseType.GetEvent(name, bindingAttr);
}
public override EventInfo[] GetEvents(BindingFlags bindingAttr)
{
return _baseType.GetEvents(bindingAttr);
}
public override FieldInfo GetField(string name, BindingFlags bindingAttr)
{
return _baseType.GetField(name, bindingAttr);
}
public override FieldInfo[] GetFields(BindingFlags bindingAttr)
{
return _baseType.GetFields(bindingAttr);
}
public override Type GetInterface(string name, bool ignoreCase)
{
return _baseType.GetInterface(name, ignoreCase);
}
public override Type[] GetInterfaces()
{
return _baseType.GetInterfaces();
}
public override MemberInfo[] GetMembers(BindingFlags bindingAttr)
{
return _baseType.GetMembers(bindingAttr);
}
protected override MethodInfo GetMethodImpl(string name, BindingFlags bindingAttr, Binder binder, CallingConventions callConvention, Type[] types, ParameterModifier[] modifiers)
{
throw new NotImplementedException();
}
public override MethodInfo[] GetMethods(BindingFlags bindingAttr)
{
return _baseType.GetMethods(bindingAttr);
}
public override Type GetNestedType(string name, BindingFlags bindingAttr)
{
return _baseType.GetNestedType(name, bindingAttr);
}
public override Type[] GetNestedTypes(BindingFlags bindingAttr)
{
return _baseType.GetNestedTypes(bindingAttr);
}
public override PropertyInfo[] GetProperties(BindingFlags bindingAttr)
{
PropertyInfo[] clrProperties = _baseType.GetProperties(bindingAttr);
if (clrProperties != null)
return clrProperties.Concat(_customProperties).ToArray();
return _customProperties.ToArray();
}
protected override PropertyInfo GetPropertyImpl(string name, BindingFlags bindingAttr, Binder binder, Type returnType, Type[] types, ParameterModifier[] modifiers)
{
return GetProperties(bindingAttr).FirstOrDefault(prop => prop.Name == name) ?? _customProperties.FirstOrDefault(prop => prop.Name == name);
}
protected override bool HasElementTypeImpl()
{
throw new NotImplementedException();
}
public override object InvokeMember(string name, BindingFlags invokeAttr, Binder binder, object target, object[] args, ParameterModifier[] modifiers, System.Globalization.CultureInfo culture, string[] namedParameters)
{
return _baseType.InvokeMember(name, invokeAttr, binder, target, args, modifiers, culture, namedParameters);
}
protected override bool IsArrayImpl()
{
throw new NotImplementedException();
}
protected override bool IsByRefImpl()
{
throw new NotImplementedException();
}
protected override bool IsCOMObjectImpl()
{
throw new NotImplementedException();
}
protected override bool IsPointerImpl()
{
throw new NotImplementedException();
}
protected override bool IsPrimitiveImpl()
{
return _baseType.IsPrimitive;
}
public override Module Module
{
get { return _baseType.Module; }
}
public override string Namespace
{
get { return _baseType.Namespace; }
}
public override Type UnderlyingSystemType
{
get { return _baseType.UnderlyingSystemType; }
}
public override object[] GetCustomAttributes(Type attributeType, bool inherit)
{
return _baseType.GetCustomAttributes(attributeType, inherit);
}
public override object[] GetCustomAttributes(bool inherit)
{
return _baseType.GetCustomAttributes(inherit);
}
public override bool IsDefined(Type attributeType, bool inherit)
{
return _baseType.IsDefined(attributeType, inherit);
}
public override string Name
{
get { return _baseType.Name; }
}
}
private class CustomPropertyInfo : PropertyInfo
{
private string _name;
private Type _type;
private object _defaultValue;
private List<Attribute> _attributes;
public CustomPropertyInfo(string name, Type type, object defaultValue = null, List<Attribute> attributes = null)
: this(name, type, attributes)
{
_defaultValue = defaultValue;
}
protected CustomPropertyInfo(string name, Type type, List<Attribute> attributes = null)
{
_name = name;
_type = type;
_attributes = attributes;
}
public virtual object GetDefaultValue(DynamicBaseType entity)
{
return _defaultValue;
}
public override PropertyAttributes Attributes
{
get { throw new NotImplementedException(); }
}
public override bool CanRead
{
get { return true; }
}
public override bool CanWrite
{
get { return true; }
}
public override MethodInfo[] GetAccessors(bool nonPublic)
{
throw new NotImplementedException();
}
public override MethodInfo GetGetMethod(bool nonPublic)
{
throw new NotImplementedException();
}
public override ParameterInfo[] GetIndexParameters()
{
throw new NotImplementedException();
}
public override MethodInfo GetSetMethod(bool nonPublic)
{
throw new NotImplementedException();
}
public override object GetValue(object obj, BindingFlags invokeAttr, Binder binder, object[] index, System.Globalization.CultureInfo culture)
{
return ((DynamicBaseType)obj).GetPropertyValue(_name);
}
public override void SetValue(object obj, object value, BindingFlags invokeAttr, Binder binder, object[] index, System.Globalization.CultureInfo culture)
{
((DynamicBaseType)obj).SetPropertyValue(_name, value);
}
public override Type PropertyType
{
get { return _type; }
}
public override Type DeclaringType
{
get { throw new NotImplementedException(); }
}
public override object[] GetCustomAttributes(Type attributeType, bool inherit)
{
return _attributes == null ? new object[0] : _attributes.Where(a => a.GetType() == attributeType).ToArray();
}
public override object[] GetCustomAttributes(bool inherit)
{
return _attributes == null ? new object[0] : _attributes.ToArray();
}
public override bool IsDefined(Type attributeType, bool inherit)
{
throw new NotImplementedException();
}
public override string Name
{
get { return _name; }
}
public override Type ReflectedType
{
get { throw new NotImplementedException(); }
}
}
private interface IDependantCustomPropertyInfo
{
string Name { get; }
string[] Properties { get; }
}
private class CustomPropertyInfo<V> : CustomPropertyInfo, IDependantCustomPropertyInfo
{
private Func<T, V> _get;
private Action<T, V> _set;
private string[] _properties;
public CustomPropertyInfo(string name, Func<T, V> get, Action<T, V> set = null, List<Attribute> attributes = null, string[] properties = null)
: base(name, typeof(V), attributes)
{
_get = get;
_set = set;
_properties = properties;
}
public string[] Properties
{
get { return _properties; }
}
public override object GetDefaultValue(DynamicBaseType entity)
{
return _get((T)entity);
}
public override bool CanWrite
{
get { return _set != null; }
}
public override object GetValue(object obj, BindingFlags invokeAttr, Binder binder, object[] index, System.Globalization.CultureInfo culture)
{
return _get((T)obj);
}
public override void SetValue(object obj, object value, BindingFlags invokeAttr, Binder binder, object[] index, System.Globalization.CultureInfo culture)
{
if (_set == null)
throw new InvalidOperationException();
_set((T)obj, (V)value);
}
}
}
Que je peux utiliser très facilement.
Avec ma classe Customer par exemple :
public class Customer : DynamicBaseType<Customer>
{
private String firstName;
public String FirstName
{
get { return firstName; }
set
{
firstName = value;
OnPropertyChanged("FirstName");
}
}
private String lastName;
public String LastName
{
get { return lastName; }
set
{
lastName = value;
OnPropertyChanged("LastName");
}
}
}
Dans le ViewModel, je peux désormais utiliser le code suivant :
Customer.AddProperty("Age", typeof(int));
Customer.AddProperty("Married", typeof(bool));
Customer.AddProperty("FullName", c => string.Format("{0} {1}", c.LastName, c.FirstName), properties:new string[] { "LastName", "FirstName" });
customers[0].SetPropertyValue("Age", 40);
customers[0].SetPropertyValue("Married", true);
customers[1].SetPropertyValue("Age", 45);
customers[1].SetPropertyValue("Married", true);
Ce qui est nouveau dans ma solution par rapport au code de départ c’est que FullName est calculé à partir de d’autres propriétés. Si on change la propriété LastName ou FirstName d’un Customer, celui-ci lèvera également l’évènement PropertyChanged avec FullName.
Comme je l’écrivais hier,
“Derrière la magie d’Entity Framework se cache la réalité du SQL. Je persiste et je signe, si vous ne connaissez pas le SQL, il sera très compliqué d’écrire des requêtes L2E optimales.
J’ai trouvé une bonne illustrations avec les fonctions de grouping.”
Hier, je vous ai montré comment exécuter un Count et un Max en une seule requête LINQ.
Maintenant imaginons que je veuille toujours récupérer le nombre de commandes et la date de la dernière commande mais par client.
Comment récupérer cela?
Si vous êtes sûr que chaque client à au moins une commande ou que vous voulez ignorer les clients sans commande, la meilleure façon d’écrire cette requête est probablement celle-ci:
from o in context.Orders
group o by o.Customer into g
select new { Customer = g.Key, OrdersCount = g.Count(), LastOrderDate = g.Max(o => o.OrderDate) };
Dans ce cas, le SQL est très proche de celui de la requête L2E :
SELECT
[GroupBy1].[K1] AS [CustomerId],
[GroupBy1].[K2] AS [LastName],
[GroupBy1].[K3] AS [FirstName],
[GroupBy1].[K4] AS [BirthDay],
[GroupBy1].[K5] AS [AddressLine],
[GroupBy1].[K6] AS [City],
[GroupBy1].[K7] AS [PostalCode],
[GroupBy1].[K8] AS [Region],
[GroupBy1].[K9] AS [Country],
[GroupBy1].[A1] AS [C1],
[GroupBy1].[A2] AS [C2]
FROM ( SELECT
[Extent2].[CustomerId] AS [K1],
[Extent2].[LastName] AS [K2],
[Extent2].[FirstName] AS [K3],
[Extent2].[BirthDay] AS [K4],
[Extent2].[AddressLine] AS [K5],
[Extent2].[City] AS [K6],
[Extent2].[PostalCode] AS [K7],
[Extent2].[Region] AS [K8],
[Extent2].[Country] AS [K9],
COUNT(1) AS [A1],
MAX([Extent1].[OrderDate]) AS [A2]
FROM [dbo].[Orders] AS [Extent1]
INNER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerId] = [Extent2].[CustomerId]
GROUP BY [Extent2].[CustomerId], [Extent2].[LastName], [Extent2].[FirstName], [Extent2].[BirthDay], [Extent2].[AddressLine], [Extent2].[City], [Extent2].[PostalCode], [Extent2].[Region], [Extent2].[Country]
) AS [GroupBy1]
Avec un plan d’exécution assez simple :
![image_thumb[7]](http://blogs.developpeur.org/blogs/matthieu/image_thumb7_thumb_613E9CB6.png "image_thumb[7]")
Si vous pouvez avoir des clients sans commandes, la requêtes devient plus compliquée.
Une idée assez simple serait d’utiliser la requête précédente union les clients sans commandes :
(from o in context.Orders
group o by o.Customer into g
select new { Customer = g.Key, OrdersCount = g.Count(), LastOrderDate = (DateTime?)g.Max(o => o.OrderDate) }).Union(
from c in context.Customers
where !c.Orders.Any()
select new { Customer = c, OrdersCount = 0, LastOrderDate = (DateTime?)null });
Dans ce cas, la requête SQL n’est pas performante :
SELECT
[Distinct1].[C1] AS [C1],
[Distinct1].[C2] AS [C2],
[Distinct1].[C3] AS [C3],
[Distinct1].[C4] AS [C4],
[Distinct1].[C5] AS [C5],
[Distinct1].[C6] AS [C6],
[Distinct1].[C7] AS [C7],
[Distinct1].[C8] AS [C8],
[Distinct1].[C9] AS [C9],
[Distinct1].[C10] AS [C10],
[Distinct1].[C11] AS [C11],
[Distinct1].[C12] AS [C12]
FROM ( SELECT DISTINCT
[UnionAll1].[C1] AS [C1],
[UnionAll1].[CustomerId] AS [C2],
[UnionAll1].[LastName] AS [C3],
[UnionAll1].[FirstName] AS [C4],
[UnionAll1].[BirthDay] AS [C5],
[UnionAll1].[AddressLine] AS [C6],
[UnionAll1].[City] AS [C7],
[UnionAll1].[PostalCode] AS [C8],
[UnionAll1].[Region] AS [C9],
[UnionAll1].[Country] AS [C10],
[UnionAll1].[C2] AS [C11],
[UnionAll1].[C3] AS [C12]
FROM (SELECT
1 AS [C1],
[GroupBy1].[K1] AS [CustomerId],
[GroupBy1].[K2] AS [LastName],
[GroupBy1].[K3] AS [FirstName],
[GroupBy1].[K4] AS [BirthDay],
[GroupBy1].[K5] AS [AddressLine],
[GroupBy1].[K6] AS [City],
[GroupBy1].[K7] AS [PostalCode],
[GroupBy1].[K8] AS [Region],
[GroupBy1].[K9] AS [Country],
[GroupBy1].[A1] AS [C2],
CAST( [GroupBy1].[A2] AS datetime2) AS [C3]
FROM ( SELECT
[Extent2].[CustomerId] AS [K1],
[Extent2].[LastName] AS [K2],
[Extent2].[FirstName] AS [K3],
[Extent2].[BirthDay] AS [K4],
[Extent2].[AddressLine] AS [K5],
[Extent2].[City] AS [K6],
[Extent2].[PostalCode] AS [K7],
[Extent2].[Region] AS [K8],
[Extent2].[Country] AS [K9],
COUNT(1) AS [A1],
MAX([Extent1].[OrderDate]) AS [A2]
FROM [dbo].[Orders] AS [Extent1]
INNER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerId] = [Extent2].[CustomerId]
GROUP BY [Extent2].[CustomerId], [Extent2].[LastName], [Extent2].[FirstName], [Extent2].[BirthDay], [Extent2].[AddressLine], [Extent2].[City], [Extent2].[PostalCode], [Extent2].[Region], [Extent2].[Country]
) AS [GroupBy1]
UNION ALL
SELECT
1 AS [C1],
[Extent3].[CustomerId] AS [CustomerId],
[Extent3].[LastName] AS [LastName],
[Extent3].[FirstName] AS [FirstName],
[Extent3].[BirthDay] AS [BirthDay],
[Extent3].[AddressLine] AS [AddressLine],
[Extent3].[City] AS [City],
[Extent3].[PostalCode] AS [PostalCode],
[Extent3].[Region] AS [Region],
[Extent3].[Country] AS [Country],
0 AS [C2],
CAST(NULL AS datetime2) AS [C3]
FROM [dbo].[Customers] AS [Extent3]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Orders] AS [Extent4]
WHERE [Extent3].[CustomerId] = [Extent4].[CustomerId]
)) AS [UnionAll1]
) AS [Distinct1]
![image_thumb[10]](http://blogs.developpeur.org/blogs/matthieu/image_thumb10_thumb_7CB378EC.png "image_thumb[10]")
Vous vous en doutez, j’espère, ce n’est probablement pas le meilleur moyen d’écrire cette requête…
Un autre serait d’utiliser ce que j’ai présenté hier : un group by sur une constant pour récupérer le count et le max sur c.Orders:
from c in context.Customers
let ordersInfo = (from o in c.Orders
group o by 1 into g
select new { OrdersCount = g.Count(), LastOrderDate = g.Max(o => o.OrderDate) }).FirstOrDefault()
select new { Customer = c, OrdersCount = (int?)ordersInfo.OrdersCount ?? 0, OrderDate = (DateTime?)ordersInfo.LastOrderDate };
Remarquez le cast en int? ou DateTime?. Contrairement à .NET, SQL retourne null pour OrdersCount et OrderDate si un client n’a pas de commande.
Si cette requête est bonne en L2O, elle ne l’est pas en L2E au vu du SQL généré !
SELECT
[Extent1].[CustomerId] AS [CustomerId],
[Extent1].[LastName] AS [LastName],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[BirthDay] AS [BirthDay],
[Extent1].[AddressLine] AS [AddressLine],
[Extent1].[City] AS [City],
[Extent1].[PostalCode] AS [PostalCode],
[Extent1].[Region] AS [Region],
[Extent1].[Country] AS [Country],
CASE WHEN ([Limit1].[C1] IS NULL) THEN 0 ELSE [Limit1].[C1] END AS [C1],
CAST( [Limit1].[C2] AS datetime2) AS [C2]
FROM [dbo].[Customers] AS [Extent1]
OUTER APPLY (SELECT TOP (1)
[GroupBy1].[A1] AS [C1],
[GroupBy1].[A2] AS [C2]
FROM ( SELECT
[Project1].[K1] AS [K1],
COUNT([Project1].[A1]) AS [A1],
MAX([Project1].[A2]) AS [A2]
FROM ( SELECT
1 AS [K1],
1 AS [A1],
[Project1].[OrderDate] AS [A2]
FROM ( SELECT
[Extent2].[OrderDate] AS [OrderDate]
FROM [dbo].[Orders] AS [Extent2]
WHERE [Extent1].[CustomerId] = [Extent2].[CustomerId]
) AS [Project1]
) AS [Project1]
GROUP BY [K1]
) AS [GroupBy1] ) AS [Limit1]
![image_thumb[12]](http://blogs.developpeur.org/blogs/matthieu/image_thumb12_thumb_2A711BE5.png "image_thumb[12]")
Comme vous pouvez le voir, cette requête est pire que la précédente. Donc contrairement à mon post d’hier, le group par une constant n’est pas une bonne idée ici.
On peut également essayer comme ceci :
from c in context.Customers
let orders = c.Orders
select new { Customer = c, OrdersCount = (int?)orders.Count ?? 0, LastOrderDate = (DateTime?)orders.Max(o => o.OrderDate) };
Mais le SQL généré n’est pas meilleur :
SELECT
[Project3].[CustomerId] AS [CustomerId],
[Project3].[LastName] AS [LastName],
[Project3].[FirstName] AS [FirstName],
[Project3].[BirthDay] AS [BirthDay],
[Project3].[AddressLine] AS [AddressLine],
[Project3].[City] AS [City],
[Project3].[PostalCode] AS [PostalCode],
[Project3].[Region] AS [Region],
[Project3].[Country] AS [Country],
CASE WHEN ([Project3].[C1] IS NULL) THEN 0 ELSE [Project3].[C2] END AS [C1],
CAST( [Project3].[C3] AS datetime2) AS [C2]
FROM ( SELECT
[Project2].[CustomerId] AS [CustomerId],
[Project2].[LastName] AS [LastName],
[Project2].[FirstName] AS [FirstName],
[Project2].[BirthDay] AS [BirthDay],
[Project2].[AddressLine] AS [AddressLine],
[Project2].[City] AS [City],
[Project2].[PostalCode] AS [PostalCode],
[Project2].[Region] AS [Region],
[Project2].[Country] AS [Country],
[Project2].[C1] AS [C1],
[Project2].[C2] AS [C2],
(SELECT
MAX([Extent4].[OrderDate]) AS [A1]
FROM [dbo].[Orders] AS [Extent4]
WHERE [Project2].[CustomerId] = [Extent4].[CustomerId]) AS [C3]
FROM ( SELECT
[Project1].[CustomerId] AS [CustomerId],
[Project1].[LastName] AS [LastName],
[Project1].[FirstName] AS [FirstName],
[Project1].[BirthDay] AS [BirthDay],
[Project1].[AddressLine] AS [AddressLine],
[Project1].[City] AS [City],
[Project1].[PostalCode] AS [PostalCode],
[Project1].[Region] AS [Region],
[Project1].[Country] AS [Country],
[Project1].[C1] AS [C1],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Orders] AS [Extent3]
WHERE [Project1].[CustomerId] = [Extent3].[CustomerId]) AS [C2]
FROM ( SELECT
[Extent1].[CustomerId] AS [CustomerId],
[Extent1].[LastName] AS [LastName],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[BirthDay] AS [BirthDay],
[Extent1].[AddressLine] AS [AddressLine],
[Extent1].[City] AS [City],
[Extent1].[PostalCode] AS [PostalCode],
[Extent1].[Region] AS [Region],
[Extent1].[Country] AS [Country],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Orders] AS [Extent2]
WHERE [Extent1].[CustomerId] = [Extent2].[CustomerId]) AS [C1]
FROM [dbo].[Customers] AS [Extent1]
) AS [Project1]
) AS [Project2]
) AS [Project3]
Quant-au plan d’exécution, il pique les yeux :
![image_thumb[16]](http://blogs.developpeur.org/blogs/matthieu/image_thumb16_thumb_6D8C7445.png "image_thumb[16]")
Ok j’arrête de jouer avec votre patience. Quelle est la meilleure requête ?
Je pense que c’est la suivante :
from c in context.Customers
from o in
(from o2 in context.Orders
where o2.CustomerId == c.CustomerId
group o2 by o2.CustomerId into g
select new { OrdersCount = g.Count(), LastOrderDate = (DateTime?)g.Max(o => o.OrderDate) }).DefaultIfEmpty()
select new { Customer = c, OrdersCount = (int?)o.OrdersCount ?? 0, o.LastOrderDate };
Avec celle-ci la requête SQL et le plan d’exécution reste simple et efficace :
SELECT
[Extent1].[CustomerId] AS [CustomerId],
[Extent1].[LastName] AS [LastName],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[BirthDay] AS [BirthDay],
[Extent1].[AddressLine] AS [AddressLine],
[Extent1].[City] AS [City],
[Extent1].[PostalCode] AS [PostalCode],
[Extent1].[Region] AS [Region],
[Extent1].[Country] AS [Country],
CASE WHEN ([GroupBy1].[A1] IS NULL) THEN 0 ELSE [GroupBy1].[A1] END AS [C1],
CASE WHEN ([GroupBy1].[K1] IS NULL) THEN CAST(NULL AS datetime2) ELSE CAST( [GroupBy1].[A2] AS datetime2) END AS [C2]
FROM [dbo].[Customers] AS [Extent1]
OUTER APPLY (SELECT
[Extent2].[CustomerId] AS [K1],
COUNT(1) AS [A1],
MAX([Extent2].[OrderDate]) AS [A2]
FROM [dbo].[Orders] AS [Extent2]
WHERE [Extent2].[CustomerId] = [Extent1].[CustomerId]
GROUP BY [Extent2].[CustomerId] ) AS [GroupBy1]
![image_thumb[18]](http://blogs.developpeur.org/blogs/matthieu/image_thumb18_thumb_7E43F926.png "image_thumb[18]")
Notez le coût de la requête 11%, comme la première requête qui ne remontait pas les clients sans commande ! Ca rocks !
Pourquoi ais-je écris ce post ? Pourquoi ais-je montré le SQL généré ou les plans d’exécution liés ?
Je pense que c’est important. Contrairement au discours marketing, je ne pense pas qu’EF soit magique et facile à utiliser. Bien sûr, pour une base de démo avec 3 rows dans 3 tables, vous pouvez utiliser la requête que vous voulez mais pour une application de la vraie vie avec de vraies problématique de performance, ce n’est pas si simple… Attention, je continue de trouver EF fantastique et je continue d’adorer ça. Cependant, je pense qu’il est important de comprendre les impactes des requêtes L2E sur le SQL et ce n’est pas simple…
Avec ce post, je voulais montrer à quel point les plans d’exécution du SQL peuvent être différent pour un même résultat en fonction de la manière d’écrire la requête L2E.
Je réalise souvent des audits sur Entity Framework, particulièrement pour résoudre des problèmes de performance et je n’ai, à ce jour, jamais vu de vrais problèmes liés à EF. Les problèmes de performance avec EF que j’ai pu observer étaient tous liés à une méconnaissance d’EF et / ou une mauvaise conception de la base.
