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 IQueryLiteSearchQuery(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.