Entity Framework: mejoras en la generación de SQL para IN

Introducción

Devart fue la primera empresa en suministrar proveedores de Entity Framework para Oracle, MySQL, PostgreSQL, SQLite. Y ahora seguimos siendo líderes en el soporte de nuevas versiones y funciones de Entity Framework.

Se continúan mejorando las mejoras en el soporte de Entity Framework v4, como se describe aquíhemos optimizado el SQL generado para nuestros proveedores de datos ADO.NET - dotConnect para Oracle, dotConnect para MySQL, dotConnect para PostgreSQLy dotConnect para SQLite.

La generación de SQL se ha mejorado y simplificado mucho en Entity Framework 4. La mayoría de estas mejoras pueden ser utilizadas por otros proveedores, pero solo se ha mejorado un aspecto para SqlClient. esto es una transformacion У expresión en Entity SQL y el método Contiene para colecciones en LINQ to Entities.

Las nuevas versiones de nuestro dotConnect implementan esta mejora tanto para Entity Framework v4 como para Entity Framework v1. Nuestros usuarios de EF v1 pueden obtener SQL optimizado para У Las expresiones de entidad son SQL, pero no se admite LINQ to Entities Contiene método para colecciones en EF v1.

Veamos ejemplos de generación mejorada de SQL Devart dotConnect para Oracle y comparémoslos con el código generado por SqlClient. Se ha creado un modelo EF para la tabla "Categorías":

Guión DDL de Oracle:

CREATE TABLE NORTHWINDEF."Categories" (
  "CategoryID" NUMBER(9),
  "CategoryName" VARCHAR2(15) NOT NULL,
  "Description" VARCHAR2(4000),
  "Picture" BLOB,
  CONSTRAINT "PK_Categories" PRIMARY KEY ("CategoryID")
)

Secuencia de comandos DDL MS SQL Server:

CREATE TABLE [Categories] (
  [CategoryID] int IDENTITY (1, 1) NOT NULL,
  [CategoryName] nvarchar (15) NOT NULL,
  [Description] nvarchar(4000),
  [Picture] varbinary(8000),
  CONSTRAINT "PK_Categories" PRIMARY KEY ([CategoryID])
)

Todos los ejemplos se realizaron en C# para EF v4 (.NET 4.0) usando LINQ to Entities. Todas las colecciones en todos los ejemplos son matrices con datos predefinidos, pero los principios de funcionamiento son los mismos para cualquier colección que implemente IEnumerable.

Una colección con elementos únicos

Miremos a Contiene método para una sola colección que consta de elementos únicos:

Entities ctx = new Entities();
int[] collection1 = new int[] { 1, 2, 3, 4, 5, 6, 7, 8 };

var query = from c in ctx.Categories
            where collection1.Contains(c.CategoryID)
            select c;

Declaración equivalente de Entity SQL:

SELECT VALUE Categories 
FROM Entities.Categories
WHERE Categories.CategoryID IN { 1, 2, 3, 4, 5, 6, 7, 8 }

SQL generado por dotConnect para Oracle 5.XX:

SELECT
   "Extent1"."CategoryID" AS "CategoryID", "Extent1"."CategoryName" AS "CategoryName", 
   "Extent1"."Description" AS "Description", "Extent1"."Picture" AS "Picture"
FROM 
   NORTHWINDEF."Categories" "Extent1"
WHERE 
   (((1 = "Extent1"."CategoryID") OR (2 = "Extent1"."CategoryID")) 
   OR ((3 = "Extent1"."CategoryID") OR (4 = "Extent1"."CategoryID"))) 
   OR (((5 = "Extent1"."CategoryID") OR (6 = "Extent1"."CategoryID")) 
   OR ((7 = "Extent1"."CategoryID") OR (8 = "Extent1"."CategoryID")))

SQL generado por dotConnect para Oracle 6.00:

SELECT
   "Extent1"."CategoryID" AS "CategoryID", "Extent1"."CategoryName" AS "CategoryName", 
   "Extent1"."Description" AS "Description", "Extent1"."Picture" AS "Picture"
FROM 
   NORTHWINDEF."Categories" "Extent1"
WHERE 
   "Extent1"."CategoryID" IN (1, 2, 3, 4, 5, 6, 7, 8 )

SQL generado por SqlClient en EF v4:

SELECT
   [Extent1].[CategoryID] AS [CategoryID], [Extent1].[CategoryName] AS [CategoryName], 
   [Extent1].[Description] AS [Description], [Extent1].[Picture] AS [Picture]
FROM 
   [dbo].[Categories] AS [Extent1]
WHERE 
   [Extent1].[CategoryID] IN (1, 2, 3, 4, 5, 6, 7, 8 )

Como podemos ver, dotConnect for Oracle 6.00 genera una У elementos en lugar de múltiples O elementos. El proveedor de Microsoft Entity Framework para SQL Server proporciona un resultado similar.

Dos o más colecciones con elementos repetitivos

Veamos dos colecciones que contienen artículos únicos; a estas colecciones se une una nueva colección con elementos repetitivos.

LINQ a entidades:

Entities ctx = new Entities();
int[] collection1 = new int[] { 1, 2, 3, 4, 5, 6, 7, 8 };
int[] collection2 = new int[] { 1, 2, 7, 8, 9, 10 };

var query = from c in ctx.Categories
            where collection1.Contains(c.CategoryID) 
            || collection2.Contains(c.CategoryID)
            select c;

La esencia de SQL:

SELECT VALUE Categories 
FROM Entities.Categories
WHERE Categories.CategoryID IN{ 1, 2, 3, 4, 5, 6, 7, 8}
   OR Categories.CategoryID IN { 1, 2, 7, 8, 9, 10 }

SQL generado por dotConnect para Oracle 5.XX:

SELECT
   "Extent1"."CategoryID" AS "CategoryID", "Extent1"."CategoryName" AS "CategoryName",
   "Extent1"."Description" AS "Description", "Extent1"."Picture" AS "Picture"
FROM 
   NORTHWINDEF."Categories" "Extent1"
WHERE 
   ((((1 = "Extent1"."CategoryID") OR (2 = "Extent1"."CategoryID")) 
   OR ((3 = "Extent1"."CategoryID") OR (4 = "Extent1"."CategoryID"))) 
   OR (((5 = "Extent1"."CategoryID") OR (6 = "Extent1"."CategoryID")) 
   OR ((7 = "Extent1"."CategoryID") OR (8 = "Extent1"."CategoryID")))) 
   OR ((((1 = "Extent1"."CategoryID") OR (2 = "Extent1"."CategoryID")) 
   OR ((7 = "Extent1"."CategoryID") OR (8 = "Extent1"."CategoryID"))) 
   OR ((9 = "Extent1"."CategoryID") OR (10 = "Extent1"."CategoryID")))

SQL generado por dotConnect para Oracle 6.00:

SELECT
   "Extent1"."CategoryID" AS "CategoryID", "Extent1"."CategoryName" AS "CategoryName",
   "Extent1"."Description" AS "Description", "Extent1"."Picture" AS "Picture"
FROM 
   NORTHWINDEF."Categories" "Extent1"
WHERE 
   "Extent1"."CategoryID" IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

SQL generado por SqlClient en EF v4:

SELECT
   [Extent1].[CategoryID] AS [CategoryID], [Extent1].[CategoryName] AS [CategoryName],
   [Extent1].[Description] AS [Description], [Extent1].[Picture] AS [Picture]
FROM 
   [dbo].[Categories] AS [Extent1]
WHERE 
   [Extent1].[CategoryID] IN ( 1, 2, 3, 4, 5, 6, 7, 8, 1, 2, 7, 8, 9, 10)

Aunque la consulta LINQ contiene dos colecciones y dos Contiene artículos el proveedor creará solo uno У una expresión que contiene una sola colección de elementos. Como puede ver, los elementos duplicados se eliminan de У colección en dotConnect para Oracle.

Podemos obtener un resultado similar si lo hacemos Contiene para una colección con artículos duplicados:

LINQ a entidades:

int[] collection1 = new int[] { 1, 2, 3, 4, 5, 1, 1, 2, 3, 3, 4, 5, 5, 5, 5, 5, 5 };
var query = from c in ctx.Categories
            where collection1.Contains(c.CategoryID)
            select c;

Declaración equivalente de EntitySQL:

SELECT VALUE Categories 
FROM Entities.Categories
WHERE Categories.CategoryID 
IN { 1, 2, 3, 4, 5, 1, 1, 2, 3, 3, 4, 5, 5, 5, 5, 5, 5 }

En este caso, EF generará las siguientes declaraciones SQL.

SQL generado por dotConnect para Oracle 5.XX:

SELECT
   "Extent1"."CategoryID" AS "CategoryID", "Extent1"."CategoryName" AS "CategoryName",
   "Extent1"."Description" AS "Description", "Extent1"."Picture" AS "Picture"
FROM 
   NORTHWINDEF."Categories" "Extent1"
WHERE 
   ((((1 = "Extent1"."CategoryID") OR (2 = "Extent1"."CategoryID")) 
   OR ((3 = "Extent1"."CategoryID") OR (4 = "Extent1"."CategoryID"))) 
   OR (((5 = "Extent1"."CategoryID") OR (1 = "Extent1"."CategoryID")) 
   OR ((1 = "Extent1"."CategoryID") OR (2 = "Extent1"."CategoryID")))) 
   OR ((((3 = "Extent1"."CategoryID") OR (3 = "Extent1"."CategoryID")) 
   OR ((4 = "Extent1"."CategoryID") OR (5 = "Extent1"."CategoryID"))) 
   OR (((5 = "Extent1"."CategoryID") OR (5 = "Extent1"."CategoryID")) 
   OR (((5 = "Extent1"."CategoryID") OR (5 = "Extent1"."CategoryID")) 
   OR (5 = "Extent1"."CategoryID"))))

SQL generado por dotConnect para Oracle 6.00:

SELECT
   "Extent1"."CategoryID" AS "CategoryID", "Extent1"."CategoryName" AS "CategoryName",
   "Extent1"."Description" AS "Description", "Extent1"."Picture" AS "Picture"
FROM NORTHWINDEF."Categories" "Extent1"
WHERE "Extent1"."CategoryID" IN ( 1, 2, 3, 4, 5)

SQL generado por SqlClient en EF v4:

SELECT
   [Extent1].[CategoryID] AS [CategoryID], [Extent1].[CategoryName] AS [CategoryName],
   [Extent1].[Description] AS [Description], [Extent1].[Picture] AS [Picture]
FROM 
   [dbo].[Categories] AS [Extent1]
WHERE 
   [Extent1].[CategoryID] IN ( 1, 2, 3, 4, 5, 1, 1 , 2, 3, 3, 4, 5, 5, 5, 5, 5, 5)

Optimización de consultas sin recopilación explícita

Consideremos un ejemplo donde una colección no se declara explícitamente.

LINQ a entidades:

var query = from c in ctx.Categories
            where c.CategoryID == 1 || c.CategoryID == 3 
             || c.CategoryID == 5 || c.CategoryID > 200
            select c;
SELECT VALUE Categories 
FROM Entities.Categories
WHERE Categories.CategoryID = 1 OR Categories.CategoryID == 3 
OR Categories.CategoryID == 5 OR Categories.CategoryID > 200

En este caso, EF creará el siguiente SQL.

SQL generado por dotConnect para Oracle 5.XX:

SELECT
   "Extent1"."CategoryID" AS "CategoryID", "Extent1"."CategoryName" AS "CategoryName",
   "Extent1"."Description" AS "Description", "Extent1"."Picture" AS "Picture"
FROM 
   NORTHWINDEF."Categories" "Extent1"
WHERE 
   (((1 = "Extent1"."CategoryID") OR (3 = "Extent1"."CategoryID")) 
   OR (5 = "Extent1"."CategoryID")) OR ("Extent1"."CategoryID" > 200)

SQL generado por dotConnect para Oracle 6.00:

SELECT
   "Extent1"."CategoryID" AS "CategoryID", "Extent1"."CategoryName" AS "CategoryName",
   "Extent1"."Description" AS "Description", "Extent1"."Picture" AS "Picture"
FROM 
   NORTHWINDEF."Categories" "Extent1"
WHERE 
   ("Extent1"."CategoryID" IN (1,3,5)) OR ("Extent1"."CategoryID" > 200)

SQL generado por SqlClient en EF v4:

SELECT
   [Extent1].[CategoryID] AS [CategoryID], [Extent1].[CategoryName] AS [CategoryName],
   [Extent1].[Description] AS [Description], [Extent1].[Picture] AS [Picture]
FROM 
   [dbo].[Categories] AS [Extent1]
WHERE 
   (1 = [Extent1].[CategoryID]) OR (3 = [Extent1].[CategoryID]) 
   OR (5 = [Extent1].[CategoryID]) OR ([Extent1].[CategoryID] > 200)

Como vemos, dotConnect for Oracle 6.00 es capaz de transformar un conjunto O elementos en singular У expresión en algunos casos.

Específico de Oracle SQL

Hay un límite de 1000 artículos para У expresión en Oracle. Si el número de elementos supera este límite, la ejecución de la consulta SQL fallará con la excepción de ORA-01975. dotConnect for Oracle hace frente a esta limitación al generar correctamente SQL (varios У expresiones relacionadas con O punto). Así que ahora no necesita dividir su colección en otras más pequeñas, dotConnect for Oracle lo hace automáticamente.

SELECT
   "Extent1"."CategoryID" AS "CategoryID", "Extent1"."CategoryName" AS "CategoryName",
   "Extent1"."Description" AS "Description", "Extent1".Picture AS "Picture"
FROM 
   NORTHWINDEF."Categories" "Extent1"
WHERE 
   "Extent1"."CategoryID" IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, ... 994, 995, 996, 997, 998, 999) 
   OR "Extent1"."CategoryID" IN (1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008)

Las nuevas versiones de los proveedores de datos Devart ADO.NET para Oracle, MySQL, PostgreSQL y SQLite generan consultas SQL más compactas y rápidas. En el futuro, continuaremos optimizando el SQL generado para casos específicos de uso de Entity Framework, teniendo en cuenta las características de cada base de datos.

Artículos de interés

Subir