Entity Framework 4 – Query Rewrite mit EFTracingProvider simulieren

Mit dem EFTracingProviderToolkit gibt es neben der Protokollierung der SQL-Befehle noch eine weitere interessante Möglichkeit das SQL anzupassen. Es ist damit beispielsweise denkbar eine Art Dictionary für schlecht generierte SQL-Befehle aufzubauen, die durch effizientere Abfragen ersetzt werden.

Möglich macht dies das Providermodell und das Event CommandExecuting, mit dessen Hilfe das generierte SQL zu Tage gefördert wird. Innerhalb des Events wird dieses über die Eigenschaft Command der CommandExecutionEventArgs im Lese- und Schreibzugriff bereitgestellt.

Probieren wir es an folgendem Beispiel:

: Abbildung 1 Beispielmodell

Hier soll jetzt die Aufgabe darin bestehen, zu allen Bestellungen die Anzahl der Bestellpositionen und das Total zu ermitteln.

Die Linq-Abfrage dazu:

C# (Linq)
var resultList = from o in ctx.Orders.Include("OrderItems")
                        select new
                        {
                          Id = o.Id,
                          AnzahlItems = o.OrderItems.Count,
                          Total = o.OrderItems.Sum(s => s.Price * s.Quantity)
                        };

Für diese Abfrage wird folgendes SQL-Statement erstellt:

SQL (EF-Generiert)
SELECT 
[Project1].[Id] AS [Id], 
[Project1].[C1] AS [C1], 
(SELECT 
	SUM([Filter2].[A1]) AS [A1]
	FROM ( SELECT 
		[Extent3].[Price] * [Extent3].[Quantity] AS [A1]
		FROM [QuerySchema].[OrderItem] AS [Extent3]
		WHERE [Project1].[Id] = [Extent3].[OrderId]
	)  AS [Filter2]) AS [C2] -- Sub-Select
FROM ( SELECT 
	[Extent1].[Id] AS [Id], 
	(SELECT 
		COUNT(1) AS [A1]
		FROM [QuerySchema].[OrderItem] AS [Extent2]
		WHERE [Extent1].[Id] = [Extent2].[OrderId]) AS [C1]
	FROM [QuerySchema].[Order] AS [Extent1]
)  AS [Project1]

Dieses Statement verwendet ein Sub-Select für die Bildung der Summe für das Total. Handgeschrieben könnte es in etwa so aussehen:

SQL
SELECT
    o.Id
    , COUNT(oi.Id) as C1 -- AnzahlItems
    , SUM(oi.Price * oi.Quantity) as C2 --Total
FROM QuerySchema.[Order] o
INNER JOIN QuerySchema.OrderItem oi ON oi.OrderId = o.Id
GROUP BY
o.Id

Werden die beiden Abfragen im Ausführungsplan gegenübergestellt, so ist die handgeschriebene Abfrage auch etwas weniger ressourcenhungrig.

: Abbildung 2 Ausführungsplan der Abfragen in Relation

Mit dem EFTracingProvider wird es möglich, das SQL auszutauschen. So kann mithilfe eines Dictionary das bestehende SQL durch das effizientere ersetzt werden. Quasi kann so das Query Rewrite simuliert werden.

In diesem Beispiel kann im Kontext des Event CommandExecuting abonniert werden. Der Codeausschnitt dafür sieht so aus:

C# (Codeausschnitt)
   [TestMethod]
    public void ChangeGeneratedSqlTest()
    {
      // Arrange
      string fileContent;
      string efGeneratedSqlString;
      var ctx = new ORMSamplesEntities();
      ctx.CommandExecuting += ctx_CommandExecuting;
      
      this.queryRewriteSqlString = @"SELECT
	            o.Id
	            , COUNT(oi.Id) as C1 -- AnzahlItems
	            , SUM(oi.Price * oi.Quantity) as C2 --Total
            FROM QuerySchema.[Order] o
            INNER JOIN QuerySchema.OrderItem oi ON oi.OrderId = o.Id
            GROUP BY
            o.Id";

      // Act
      // ctx_CommandExecuting
      var resultList = from o in ctx.Orders.Include("OrderItems")
                        select new
                        {
                          Id = o.Id,
                          AnzahlItems = o.OrderItems.Count,
                          Total = o.OrderItems.Sum(s => s.Price * s.Quantity)
                        };

      efGeneratedSqlString = (resultList as ObjectQuery).ToTraceString();
      // ctx_CommandExecuting
      resultList.ToList();

      fileContent = File.ReadAllText(this.filePath);

      // Assert
      Assert.IsTrue(fileContent.Contains(this.queryRewriteSqlString));
      Assert.IsFalse(fileContent.Contains(efGeneratedSqlString));
      ctx.Dispose();
    }

    private void ctx_CommandExecuting(object sender, CommandExecutionEventArgs e)
    {
      e.Command.CommandText = this.queryRewriteSqlString;
    }

Mit diesem Event sind aber auch andere Szenarien denkbar.

Ein Hinweis zum Schluss, dieses Beispiel rechtfertigt in meinen Augen nicht unbedingt, das SQL zu ersetzen, es soll nur die Möglichkeit des simulierten Query Rewrites mit dem EFTracingProvider aufzeigen. Zum Einsatz kann es auch bei kompromisslosen Richtlinien kommen, zum Beispiel wenn in einer OLTP-Umgebung der Einsatz von Sub-Selects nicht erlaubt ist. Dann ist diese Variante effizienter als der Austausch des O/R-Mappers in einem fortgeschrittenen Projektstatus, nur wegen ein paar einzelner Statements.

Alternativ kann dafür aber auch ExecuteStoreQuery bzw. eine gespeicherte Prozedur verwendet werden. Es muss jedoch dabei berücksichtigt werden, dass diese beiden Varianten nicht mit dem Laden der Objekthierarchien klarkommen und durch das Nachladen das Select N+1-Problem auftreten kann.