Búsqueda dinámica en SQL Server con EntityLite

A mi siempre me ha gustado mucho el famoso artículo  Dynamic search conditions
in T-SQL
de Erland Sommarskog. De hecho, he hecho referecia a él en algunas
de mis respuestas en los foros de SQL Server.

Sin embargo, en este artículo voy a implementar la búsqueda dinámica para el
mismo caso de studio con EntityLite.

Primero se crea la siguiente vista:

CREATE VIEW [dbo].[OrderDetail_Extended]
AS
	SELECT
		O.OrderDate, O.CustomerID,
		C.CompanyName AS CustomerName, C.[Address], C.City, C.Region, C.PostalCode, C.Country, C.Phone,
		OD.[OrderDetailID], OD.[OrderID], OD.[ProductID], OD.[UnitPrice], OD.Quantity, OD.Discount,
		(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS SubTotal,
		P.ProductName, P.UnitsInStock, P.UnitsOnOrder,
		 Cat.CategoryName
	FROM
		dbo.Orders O
		INNER JOIN dbo.OrderDetails OD ON O.OrderID = OD.OrderID
		INNER JOIN dbo.Customers C ON O.CustomerID = C.CustomerID
		INNER JOIN dbo.Products P ON OD.ProductID = P.ProductID
		INNER JOIN dbo.Categories Cat ON P.CategoryID = Cat.CategoryID
GO

Luego extendemos el repositorio de OrderDetails con el siguiente código. Observa que primero definimos una clase que represente el criterio de búsqueda. La búsqueda incluirá la columna correspondiente si la propiedad no es nula, y se combirarán con AND.

public class OrderDetailSearchCriteria
{

    public int? OrderId { get; set;}
    public DateTime? FromDate { get; set;}
    public DateTime? ToDate { get; set;}
    public Decimal? MinPrice { get; set;}
    public Decimal? MaxPrice { get; set;}
    public string CustomerId { get; set;}
    public string CustomerName { get; set;}
    public string City { get; set;}
    public string Region { get; set;}
    public string Country { get; set;}
    public int? ProductId { get; set;}
    public string ProductName { get; set;}

}

public partial class OrderDetailRepository
{
    public IQueryLite SearchQuery(OrderDetailSearchCriteria criteria)
    {
        var query = this.Query(Projection.Extended);
        if (criteria.City != null) query.And(OrderDetailFields.City, OperatorLite.Equals, criteria.City);
        if (criteria.Country != null) query.And(OrderDetailFields.Country, OperatorLite.Equals, criteria.Country);
        if (criteria.CustomerId != null) query.And(OrderDetailFields.CustomerId, OperatorLite.Equals, criteria.CustomerId);
        if (criteria.CustomerName != null) query.And(OrderDetailFields.CustomerName, OperatorLite.StartsWith, criteria.CustomerName);
        if (criteria.FromDate.HasValue) query.And(OrderDetailFields.OrderDate, OperatorLite.GreaterOrEquals, criteria.FromDate);
        if (criteria.MaxPrice.HasValue) query.And(OrderDetailFields.UnitPrice, OperatorLite.LessOrEquals, criteria.MaxPrice);
        if (criteria.MinPrice.HasValue) query.And(OrderDetailFields.UnitPrice, OperatorLite.GreaterOrEquals, criteria.MinPrice);
        if (criteria.OrderId.HasValue) query.And(OrderDetailFields.OrderId, OperatorLite.Equals, criteria.OrderId);
        if (criteria.ProductId.HasValue) query.And(OrderDetailFields.ProductId, OperatorLite.Equals, criteria.ProductId);
        if (criteria.ProductName != null) query.And(OrderDetailFields.ProductName, OperatorLite.StartsWith, criteria.ProductName);
        if (criteria.Region != null) query.And(OrderDetailFields.Region, OperatorLite.Equals, criteria.Region);
        if (criteria.ToDate.HasValue) query.And(OrderDetailFields.OrderDate, OperatorLite.LessOrEquals, criteria.ToDate);
        return query;
    }
}

 

Y lo usamos de la siguiente manera:

using (var ds = new NorthwindDataService("Northwind"))
{
    var criteria = new OrderDetailSearchCriteria
    {
        ProductName = "C",
        CustomerId = "ALFKI"
    };

    var orderDetails = ds.OrderDetailRepository.SearchQuery(criteria)
        .Fields(OrderDetailFields.OrderId, OrderDetailFields.OrderDate, OrderDetailFields.ProductName, OrderDetailFields.SubTotal)
        .OrderByDesc(OrderDetailFields.OrderDate)
        .OrderBy(OrderDetailFields.ProductName)
        .ToList(0, 9);

    foreach(var od in orderDetails)
    {
        Console.WriteLine("{0}, {1},  {2}, {3}", od.OrderId, od.OrderDate, od.ProductName, od.SubTotal);
    }
                    
}

 

Bueno, realmente este código funcionaría igualmente para ORACLE, MySQL, Postgre-SQL y SQLite. Pero observa que esta manera de hacer la búsqueda dinámica tiene una serie de ventajas con respecto al artículo de Erland:

  • Puedes especificar las columna a recuperar.
  • Puedes epecificar el orden de las columnas
  • Puedes hacer paginación de consultas.

 

Si lo ejecutamos con el SQL Profiler abierto veremos lo siguiente:

 

exec sp_executesql N'
SELECT [OrderDate], [OrderDetailID], [OrderID], [ProductName], [SubTotal]
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY [OrderDate] DESC,
    [ProductName]) AS __RowNumber__
FROM [dbo].[OrderDetail_Extended]

WHERE
    ([CustomerID] = @P0
    AND [ProductName] LIKE @P1 + ''%'')
) T
WHERE __RowNumber__ BETWEEN @P2 AND @P3
ORDER BY __RowNumber__;',N'@P0 nvarchar(5),@P1 nvarchar(40),@P2 int,@P3 int',@P0=N'ALFKI',@P1=N'C',@P2=1,@P3=10

 

O sea, código sql eficiente, controlado y predecible. Algo bastante diferente de lo que nos encontramos con Entity Framework.

EntityLite publicado

EntiLite es un micro ORM que he desarrollado y que está publicado en versión reléase desde hace unas semanas en Nuget: https://www.nuget.org/packages/EntityLite/

El código fuente está en GitHub: https://github.com/jesuslpm/EntityLite

Y como documentación hay un artículo en CodeProject recientemente publicado: http://www.codeproject.com/Articles/753796/i-nercya-EntityLite-A-Lightweight-Database-First-M

EntityLite es diferente de otros ORM’s, su enfoque es relacional, y está diseñado para sacarle todo el partido posible a la base de datos. Con EntityLite puedes hacer cualquier consulta que se te pueda ocurrir por muy complicada que sea.