Entity Framework y LINQ to Entities (1) IQueryable<T> y Consulta remota

[ LINQ a través de la serie C# ]

[ Serie Entity Framework Core ]

[ Serie Entity Framework ]

Versión EF Core de este artículo: https://weblogs.asp.net/dixin/entity-framework-core-and-linq-to-entities-1-remote-query

Los capítulos anteriores trataron sobre LINQ to Objects, LINQ to XML (objetos) y Parallel LINQ (to Objects). Todas estas API consultan en objetos de memoria administrados por .NET. Este capítulo trata sobre Entity Framework, una biblioteca de Microsoft que proporciona un tipo diferente de tecnología LINQ, LINQ to Entities. LINQ to Entities puede acceder y consultar datos relacionales administrados por diferentes tipos de bases de datos, por ejemplo:

SQL Server y Azure SQL Database (también conocido como SQL Azure)
Oráculo
MySQL
PostgreSQL

etc. Este tutorial utiliza Microsoft SQL Server LocalDB con la base de datos de ejemplo de Microsoft AdventureWorks como fuente de datos. SQL Server LocalDB es una edición ligera y gratuita de SQL Server. Es extremadamente fácil de instalar/usar, pero con una gran capacidad de programación. Siga estos pasos para configurar:

Descargue SQL Server LocalDB y use el instalador para descargar SQL Server LocalDB e instalarlo. Se requiere configuración cero para la instalación.
Descargue las herramientas de administración de SQL Server e instálela. Esto incluye:

SQL Server Management Studio, un entorno de integración gratuito para administrar SQL Server y la base de datos SQL.
SQL Server Profiler, una herramienta de seguimiento gratuita. Este tutorial lo usará para descubrir cómo funciona Entity Framework con la fuente de datos SQL.

(Opcional) Descargue SQL Server Data Tools e instálelo. Es una extensión gratuita de Visual Studio y permite la administración de bases de datos SQL dentro de Visual Studio.
Descargue e instale las bases de datos de ejemplo de Microsoft SQL Server AdventureWorks. La base de datos completa de Microsoft tendrá unos 205 MB, por lo que se proporciona una versión compacta y reducida de la base de datos AdventureWorks para este tutorial. Solo tiene 34 MB y está disponible en GitHub. Simplemente descargue el archivo AdventureWorks_Data.mdf y el archivo AdventureWorks_Log.ldf en el mismo directorio.
Instalar la biblioteca de Entity Framework para codificar el proyecto:
```
Install-Package EntityFramework
```
De forma predeterminada, se agregarán 2 ensamblajes a las referencias:EntityFramework.dll y EntityFramework.SqlServer.dll. Entity Framework implementa un modelo de proveedor para admitir diferentes tipos de bases de datos, por lo que EntityFramework.dll tiene las funcionalidades generales para todas las bases de datos y EntityFramewwork.SqlServer.dll implementa funcionalidades específicas de la base de datos SQL.

Consulta remota frente a consulta local

LINQ to Objects y Parallel LINQ consultan objetos .NET en la memoria local del proceso .NET actual, estas consultas se denominan consultas locales. LINQ to XML consulta la fuente de datos XML, que también son objetos .NET XML en la memoria local, por lo que las consultas LINQ to XML también son consultas locales. Como se demostró al comienzo de este tutorial, LINQ también puede consultar datos en otro dominio, como tweets en Twitter, filas en tablas de bases de datos, etc. Aparentemente, estas fuentes de datos no son objetos .NET disponibles directamente en la memoria local. Estas consultas se denominan consultas remotas.

Un origen de datos local de LINQ to Objects se representa mediante IEnumerable. Un origen de datos LINQ remoto, como una tabla en la base de datos, se representa mediante IQueryable. Similar a ParallelQuery discutido en el capítulo Parallel LINQ, IQueryable es otra paridad con IEnumerbale:

LINQ secuencial	LINQ paralelo	LINQ a Entidades
IEnumerable	Consulta Paralela	IQueryable
IEnumerable	Consulta Paralela	IQueryable
IOrdenedEnumerable	ConsultaParalelaOrdenada	IOrdenedQueryable
Enumerables	ParaleloEnumerable	Consultable

namespace System.Linq
{
    public interface IQueryable : IEnumerable
    {
        Expression Expression { get; }

        Type ElementType { get; }

        IQueryProvider Provider { get; }
    }

    public interface IOrderedQueryable : IQueryable, IEnumerable
    {
    }

    public interface IQueryable<out T> : IEnumerable<T>, IEnumerable, IQueryable
    {
    }

    public interface IOrderedQueryable<out T> : IQueryable<T>, IEnumerable<T>, IOrderedQueryable, IQueryable, IEnumerable
    {
    }
}

IEnumerable tiene muchas implementaciones, como matriz en mscorlib.dll, Microsoft.Collections.Immutable.ImmutableList en System.Collections.Immutable.dll, etc. Aquí, Entity Framework proporciona varias implementaciones de IQueryable, como System. Data.Entity.Infrastructure.DbQuery y System.Data.Entity.DbSet en EntityFramework.dll, etc. DbQuery y DbSet se utilizarán en todo este capítulo. Consulte el capítulo LINQ to Objects para ver la jerarquía completa de implementación/herencia de IEnumerable, ParallelQuery e IQueryable.

La clase Queryable define todos los métodos de extensión para IQueryable, que son paridades con los métodos de la clase Enumerable. Por ejemplo, aquí están los métodos Where/Select/Concat uno al lado del otro:

namespace System.Linq
{
    public static class Enumerable
    {
        public static IEnumerable<TSource> Where<TSource>(
            this IEnumerable<TSource> source, Func<TSource, bool> predicate);

        public static IEnumerable<TResult> Select<TSource, TResult>(
            this IEnumerable<TSource> source, Func<TSource, TResult> selector);

        public static IEnumerable<TSource> Concat<TSource>(
            this IEnumerable<TSource> first, IEnumerable<TSource> second);

        // More query methods...
    }

    public static class Queryable
    {
        public static IQueryable<TSource> Where<TSource>(
            this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate);

        public static IQueryable<TResult> Select<TSource, TResult>(
            this IQueryable<TSource> source, Expression<Func<TSource, TResult>> selector);

        public static IQueryable<TSource> Concat<TSource>(
            this IQueryable<TSource> source1, IQueryable<TSource> source2);

        // More query methods...
    }
}

Y de manera similar, los métodos de pedido uno al lado del otro:

namespace System.Linq
{
    public static class Enumerable
    {
        public static IOrderedEnumerable<TSource> OrderBy<TSource, TKey>(
            this IEnumerable<TSource> source, Func<TSource, TKey> keySelector);

        public static IOrderedEnumerable<TSource> OrderByDescending<TSource, TKey>(
            this IEnumerable<TSource> source, Func<TSource, TKey> keySelector);

        public static IOrderedEnumerable<TSource> ThenBy<TSource, TKey>(
            this IOrderedEnumerable<TSource> source, Func<TSource, TKey> keySelector);

        public static IOrderedEnumerable<TSource> ThenByDescending<TSource, TKey>(
            this IOrderedEnumerable<TSource> source, Func<TSource, TKey> keySelector);
    }

    public static class Queryable
    {
        public static IOrderedQueryable<TSource> OrderBy<TSource, TKey>(
            this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector);

        public static IOrderedQueryable<TSource> OrderByDescending<TSource, TKey>(
            this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector);

        public static IOrderedQueryable<TSource> ThenBy<TSource, TKey>(
            this IOrderedQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector);

        public static IOrderedQueryable<TSource> ThenByDescending<TSource, TKey>(
            this IOrderedQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector);
    }
}

Con este diseño, el encadenamiento de métodos fluidos y el patrón de expresiones de consulta LINQ funcionan sin problemas para consultas LINQ remotas.

La clase consultable no proporciona los siguientes métodos de consulta:

AsEnumerable:devuelve un IEnumerable que representa una secuencia de objetos .NET, y este método ya lo proporciona Enumerable en LINQ to Objects
Vacío/Rango/Repetir:no tiene sentido que .NET genere una fuente de datos remota para más consultas remotas; el otro método de generación, DefaultIfEmpty, está disponible, porque DefaultIfEmpty genera desde una fuente de entrada IQuerable.
Sobrecargas máx./mín. para tipos primarios de .NET:es posible que estos tipos primitivos de .NET no existan en la fuente de datos remota, como una base de datos SQL/Oracle/MySQL; además, LINQ to Objects ha proporcionado estos métodos para consultar estos tipos primitivos de .NET valores en la memoria local.
ToArray/ToDictionary/ToList/ToLookup:de manera similar, los tipos de colección como matriz, diccionario, etc. pueden no existir en la fuente de datos remota, también LINQ to Objects ha proporcionado estos métodos para extraer valores de la fuente de datos y convertir a colecciones .NET .

Queryable proporciona un método de consulta adicional:

AsQueryable:a diferencia de AsSequential/AsParallel, AsEnumerable/AsQueryable no puede cambiar entre la consulta local de LINQ to Objects y la consulta remota de LINQ to Entities. Este método se discutirá más adelante.

Función frente a árbol de expresión

Como se explicó en el capítulo de C#, la principal diferencia es que los métodos de consulta enumerables aceptan funciones y los métodos consultables aceptan árboles de expresión. Las funciones son código .NET ejecutable y los árboles de expresión son objetos de datos .NET que representan árboles de sintaxis abstracta, que se pueden traducir a otro lenguaje específico del dominio. En el capítulo de C#, la parte del árbol de expresiones demostró compilar un árbol de expresiones aritméticas en código IL en tiempo de ejecución y ejecutarlo dinámicamente. El mismo enfoque se puede utilizar para traducir un árbol de expresiones aritméticas a una consulta SQL y ejecutarlo dentro de SQL Server.

public class InfixVisitor : BinaryArithmeticExpressionVisitor<string>
{
    protected override string VisitAdd
        (BinaryExpression add, LambdaExpression expression) => this.VisitBinary(add, "+", expression);

    protected override string VisitConstant
        (ConstantExpression constant, LambdaExpression expression) => constant.Value.ToString();

    protected override string VisitDivide
        (BinaryExpression divide, LambdaExpression expression) => this.VisitBinary(divide, "/", expression);

    protected override string VisitMultiply
        (BinaryExpression multiply, LambdaExpression expression) => this.VisitBinary(multiply, "*", expression);

    protected override string VisitParameter
        (ParameterExpression parameter, LambdaExpression expression) => $"@{parameter.Name}";

    protected override string VisitSubtract
        (BinaryExpression subtract, LambdaExpression expression) => this.VisitBinary(subtract, "-", expression);

    private string VisitBinary
        (BinaryExpression binary, string @operator, LambdaExpression expression) =>
            $"({this.VisitNode(binary.Left, expression)} {@operator} {this.VisitNode(binary.Right, expression)})";
}

Consulte la parte del árbol de expresiones en el capítulo de C# para obtener la definición de BinaryArithmeticExpressionVisitor. Arriba, InfixVisitor puede atravesar un árbol de expresiones aritméticas y generar una cadena de expresión infija, que puede funcionar en SQL:

internal static partial class ExpressionTree
{
    internal static void Translate()
    {
        InfixVisitor infixVisitor = new InfixVisitor();
        Expression<Func<double, double, double>> expression1 = (a, b) => a * a + b * b;
        string infixExpression1 = infixVisitor.VisitBody(expression1);
        Trace.WriteLine(infixExpression1); // ((@a * @a) + (@b * @b))

        Expression<Func<double, double, double, double, double, double>> expression2 =
            (a, b, c, d, e) => a + b - c * d / 2 + e * 3;
        string infixExpression2 = infixVisitor.VisitBody(expression2);
        Trace.WriteLine(infixExpression2); // (((@a + @b) - ((@c * @d) / 2)) + (@e * 3))
    }
}

Observe que @ se antepone al nombre del parámetro, de modo que la cadena de expresión de resultado se puede usar en una consulta SQL como expresión SELECT:

public static partial class BinaryArithmeticTranslator
{
    [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
    internal static double ExecuteSql(
        string connection,
        string arithmeticExpression,
        IEnumerable<KeyValuePair<string, double>> parameters)
    {
        using (SqlConnection sqlConnection = new SqlConnection(connection))
        using (SqlCommand command = new SqlCommand($"SELECT {arithmeticExpression}", sqlConnection))
        {
            sqlConnection.Open();
            parameters.ForEach(parameter => command.Parameters.AddWithValue(parameter.Key, parameter.Value));
            return (double)command.ExecuteScalar();
        }
    }
}

Y el siguiente método Sql puede aceptar un árbol de expresiones aritméticas y emitir un método dinámico en tiempo de ejecución. Cuando se llama al método dinámico devuelto, el árbol de expresiones aritméticas se traducirá a una consulta SQL y se ejecutará en SQL

public static partial class BinaryArithmeticTranslator
{
    private static readonly InfixVisitor InfixVisitor = new InfixVisitor();

    public static TDelegate Sql<TDelegate>(
        Expression<TDelegate> expression, string connection = ConnectionStrings.LocalDb)
        where TDelegate : class
    {
        DynamicMethod dynamicMethod = new DynamicMethod(
            string.Empty,
            expression.ReturnType,
            expression.Parameters.Select(parameter => parameter.Type).ToArray(),
            typeof(BinaryArithmeticTranslator).Module);
        EmitIL(dynamicMethod.GetILGenerator(), InfixVisitor.VisitBody(expression), expression, connection);
        return dynamicMethod.CreateDelegate(typeof(TDelegate)) as TDelegate;
    }

    private static void EmitIL<TDelegate>(ILGenerator ilGenerator, string infixExpression, Expression<TDelegate> expression, string connection)
    {
        // Dictionary<string, double> dictionary = new Dictionary<string, double>();
        ilGenerator.DeclareLocal(typeof(Dictionary<string, double>));
        ilGenerator.Emit(
            OpCodes.Newobj,
            typeof(Dictionary<string, double>).GetConstructor(Array.Empty<Type>()));
        ilGenerator.Emit(OpCodes.Stloc_0);

        for (int index = 0; index < expression.Parameters.Count; index++)
        {
            // dictionary.Add($"@{expression.Parameters[i].Name}", args[i]);
            ilGenerator.Emit(OpCodes.Ldloc_0); // dictionary.
            ilGenerator.Emit(OpCodes.Ldstr, $"@{expression.Parameters[index].Name}");
            ilGenerator.Emit(OpCodes.Ldarg_S, index);
            ilGenerator.Emit(
                OpCodes.Callvirt,
                typeof(Dictionary<string, double>).GetMethod(
                    nameof(Dictionary<string, double>.Add),
                    BindingFlags.Instance | BindingFlags.Public | BindingFlags.InvokeMethod));
        }

        // BinaryArithmeticTanslator.ExecuteSql(connection, expression, dictionary);
        ilGenerator.Emit(OpCodes.Ldstr, connection);
        ilGenerator.Emit(OpCodes.Ldstr, infixExpression);
        ilGenerator.Emit(OpCodes.Ldloc_0);
        ilGenerator.Emit(
            OpCodes.Call,
            typeof(BinaryArithmeticTranslator).GetMethod(
                nameof(ExecuteSql),
                BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.InvokeMethod));

        // Returns the result of ExecuteSql.
        ilGenerator.Emit(OpCodes.Ret);
    }
}

Cuando no se proporciona una cadena de conexión al método Sql, toma una cadena de conexión predeterminada de SQL Server LocalDB:

internal static partial class ConnectionStrings
{
    internal const string LocalDb = @"Data Source=(LocalDB)\MSSQLLocalDB;Integrated Security=True;Connect Timeout=30";
}

Así es como se usa el método Sql:

internal static void Execute()
{
    Expression<Func<double, double, double>> expression1 = (a, b) => a * a + b * b;
    Func<double, double, double> local1 = expression1.Compile();
    Trace.WriteLine(local1(1, 2)); // 5
    Func<double, double, double> remote1 = BinaryArithmeticTranslator.Sql(expression1);
    Trace.WriteLine(remote1(1, 2)); // 5

    Expression<Func<double, double, double, double, double, double>> expression2 =
        (a, b, c, d, e) => a + b - c * d / 2 + e * 3;
    Func<double, double, double, double, double, double> local2 = expression2.Compile();
    Trace.WriteLine(local2(1, 2, 3, 4, 5)); // 12
    Func<double, double, double, double, double, double> remote2 = BinaryArithmeticTranslator.Sql(expression2);
    Trace.WriteLine(remote2(1, 2, 3, 4, 5)); // 12
}

Como se mencionó anteriormente, el método Expression.Compile emite un método que ejecuta el cálculo aritmético localmente en CLR. Por el contrario, BinaryArithmeticTranslator.Sql emite un método que llama a ExecuteSql y ejecuta el cálculo aritmético de forma remota en un servidor SQL.

Rastrear la ejecución de consultas SQL

Sería bueno si se pudiera observar la ejecución real de la consulta SQL. SQL Server proporciona una herramienta gratuita SQL Server Profiler para esto. Para este tutorial, se necesita un poco de configuración. Inicie SQL Server Profiler, vaya a Archivo => Plantillas => Nueva plantilla. En la pestaña General, escriba un nombre de plantilla de rastreo:

En la pestaña Selección de eventos, seleccione algunos eventos para rastrear:

Procedimientos almacenados

RPC:Completado
RPC:Iniciando

TSQL

SQL:lote completado
SQL:Inicio por lotes

Transacciones

TM:Comenzar Tran completado
TM:Comenzar Tran comenzando
TM:Commit Tran completado
TM:Commit Trans comenzando
TM:Rollback Tran completado
TM:Inicio de Rollback Tran

Haga clic en Guardar para guardar esta plantilla de seguimiento.

Otra configuración opcional es la fuente. La fuente predeterminada es Lucida Console. Se puede cambiar a la fuente de Visual Studio (Consolas de forma predeterminada) para lograr una coherencia visual.

Para iniciar el seguimiento, haga clic en Archivo => Nuevo seguimiento, especifique el nombre del servidor como (LocalDB)\MSSQLLocalDB, que es el mismo que el valor del origen de datos en la cadena de conexión anterior:

Haga clic en Conectar, aparece el cuadro de diálogo Propiedades de seguimiento. Seleccione la plantilla de seguimiento que acaba de crear:

Haga clic en Ejecutar, se inicia el seguimiento. Ahora, ejecute el código anterior que llama a BinaryArithmeticTranslator.Sql, se rastrean los siguientes eventos:

Y los comandos SQL ejecutados prueban que las expresiones aritméticas se ejecutan de forma remota en SQL Server:

exec sp_executesql N'SELECT ((@a * @a) + (@b * @b))',N'@a float,@b float',@a=1,@b=2

exec sp_executesql N'SELECT (((@a + @b) - ((@c * @d) / 2)) + (@e * 3))',N'@a float,@b float,@c float,@d float,@e float',@a=1,@b=2,@c=3,@d=4,@e=5