Segue um exemplo de como utilizar Linq para a construção de consultas com agregação, aquelas do tipo <Coluna>, Count(). Vou utilizar as tabelas de categorias e produtos do banco de dados AdventureWorksLT, utilizando em outros posts.

Isto pode ser feito de várias formar mas vou listar aqui duas formas que fiz rapidamente. A primeira:

DataClassesDataContext db = new DataClassesDataContext();
var query = from a in db.ProductCategories
from b in db.Products
where a.ProductCategoryID == b.ProductCategoryID
select new { a.ProductCategoryID, a.Name };

var query2 = from a in query.AsQueryable()
group a by a.Name into g
select new { g.Key, NumItems = g.Count() };

GridView1.DataSource = query2;
GridView1.DataBind();

Neste exemplo anterior, crio primeira uma consulta trazendo as categorias por produto e em um segunda consulta agrupo o resultado da primeira pelo nome da categoria e realizo uma contagem pelo agrupamento. Aparentemente pode parecer que estarei realizando mais de uma consulta no banco de dados, mas, devido a propriedade de Deferred das consultas linq apenas um sql contendo a agregação vai ao banco de dados:

SELECT COUNT(*) AS [NumItems], [t0].[Name] AS [Key]

FROM [SalesLT].[ProductCategory] AS [t0], [SalesLT].[Product] AS [t1]

WHERE ([t0].[ProductCategoryID]) = [t1].[ProductCategoryID]

GROUP BY [t0].[Name]

Isto pode ficar mais evidente se re-escrevermos a consulta Linq para:

DataClassesDataContext db = new DataClassesDataContext();

var query = from a in (from a in db.ProductCategories

from b in db.Products

where a.ProductCategoryID == b.ProductCategoryID

select new { a.ProductCategoryID, a.Name }).AsQueryable()

group a by a.Name into g

select new { g.Key, NumItems = g.Count() };

T+

No último post terminei utilizando o LoadWith para exemplificar junção de consulta. Agora vamos utilizar o método AssociateWith. Seja o código:

BlogPerformanceDataContext dbContext = new BlogPerformanceDataContext();

DataLoadOptions dOptions = new DataLoadOptions();

dOptions.AssociateWith<Customer>(c => c.SalesOrderHeaders.Where<SalesOrderHeader>(p => p.ShipDate != DateTime.Today));

dbContext.LoadOptions = dOptions;

var query = from c in dbContext.Customers

where c.CustomerID == 29485

select c;

foreach (Customer cust in query)

{

Console.WriteLine(cust.LastName);

foreach (SalesOrderHeader sales in cust.SalesOrderHeaders)

Console.WriteLine("\t{0} - {1} - {2}", sales.SalesOrderNumber, sales.ShipDate, sales.SubTotal);

}

Ao executar o código obtemos as seguintes consultas no banco de dados:

Para o loop mais externo:

exec sp_executesql N'SELECT [t0].[CustomerID], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[CompanyName], [t0].[SalesPerson], [t0].[EmailAddress], [t0].[Phone], [t0].[PasswordHash], [t0].[PasswordSalt], [t0].[rowguid], [t0].[ModifiedDate]
FROM [SalesLT].[Customer] AS [t0]
WHERE [t0].[CustomerID] = @p0',N'@p0 int',@p0=29485

E, e para o loop de interno (obter os pedidos do cliente), temos:

exec sp_executesql N'SELECT [t0].[SalesOrderID], [t0].[RevisionNumber], [t0].[OrderDate], [t0].[DueDate], [t0].[ShipDate], [t0].[Status], [t0].[OnlineOrderFlag], [t0].[SalesOrderNumber], [t0].[PurchaseOrderNumber], [t0].[AccountNumber], [t0].[CustomerID], [t0].[ShipToAddressID], [t0].[BillToAddressID], [t0].[ShipMethod], [t0].[CreditCardApprovalCode], [t0].[SubTotal], [t0].[TaxAmt], [t0].[Freight], [t0].[TotalDue], [t0].[Comment], [t0].[rowguid], [t0].[ModifiedDate]

FROM [SalesLT].[SalesOrderHeader] AS [t0]

WHERE ([t0].[ShipDate] <> @p0) AND ([t0].[CustomerID] = ((

SELECT [t2].[CustomerID]

FROM (

SELECT TOP (1) [t1].[CustomerID]

FROM [SalesLT].[Customer] AS [t1]

WHERE [t1].[CustomerID] = @p1

) AS [t2]

)))',N'@p0 datetime,@p1 int',@p0='2009-06-10 00:00:00',@p1=29485

Vemos aqui uma construção de sql diferente do exemplo do post anterior.

Isto ocorre devido ao fato de que o método LoadWith indica que os dados relacionados ao objeto principal deverão ser carregados juntamente com o mesmo (obtemos assim uma consulta utilizando join como visto no post ), já o método  AssociateWith atua como um filtro para os dados principais, semelhante ao uso de uma subquery para filtrar uma consulta. Deve-se utilizar o AssociateWith para as situações nas quais queremos filtrar os dados obtidos através de uma subquery e não iremos precisar das informações da subquery.

Nesses dois post pode-se notar a qualidade das consultas obtidas com Linq e creio que esta tecnologia apresenta excelentes resultados para o desenvolvimento de novas aplicações diminuindo o grande “abismo” entre os mundos OO e Relacionais.

T+

A idéia desse artigo é verificar o desempenho do uso do Linq para realização de consulta a uma banco de dados Sql Server. Para isto irei utilizar Linq to Sql, consultando um banco Sql Server 2008. A database de teste aqui utilizada é AdventureWorksLite que pode ser baixada no endereço: http://www.codeplex.com/MSFTDBProdSamples.

Nesse artigo, foram realizadas varias consultas em Linq, desde simples consultas até consulta mais complexas. Utilizei uma ferramenta do Sql Server para capturar as querys enviadas ao banco de dados na execução da aplicação e as mesmas serão expostas aqui.

Em muitas dessas consultas o Linq to Sql utiliza a procedure do Sql Server sp_executesql. Com esta procedure podemos passar para o SQL Server um string contendo um comando ou batch de comandos que serão executados no banco de dados como se fossem digitados diretamente no SSMS. A vantagem dessa procedure é o fato da mesma ainda suportar a criação de parâmetros.

No projeto utilizado para o desenvolvimento desse artigo foi criado um DataBaseContext, com da imagem:

Realizando uma consulta simples

Primeiro foi realizado uma simples consulta da tabelas Customer com o código:

BlogPerformanceDataContext dbContext = new LinqPerformanceLibrary.BlogPerformanceDataContext();
Table<Customer> tbCustomer = dbContext.Customers;

foreach(Customer cust intbCustomer)
 Console.WriteLine(cust.FirstName);

A idéia seria listar todos os clientes cadastrados, exibindo apenas o primeiro nome dos mesmos. O SQL criado pelo Linq foi:

SELECT [t0].[CustomerID],
       [t0].[NameStyle], [t0].[Title],
       [t0].[FirstName], [t0].[MiddleName],
       [t0].[LastName], [t0].[Suffix], [t0].[CompanyName],
       [t0].[SalesPerson], [t0].[EmailAddress], [t0].[Phone],
       [t0].[PasswordHash], [t0].[PasswordSalt], [t0].[rowguid], [t0].[ModifiedDate]
FROM [SalesLT].[Customer] AS [t0]

Esta consulta não é muito diferente do que se esperava – mas vale ressaltar aqui esta consulta foi enviada ao banco de dados apenas quando o código atingiu a instrução de loop e não na declaração do Linq (no Linq esta propriedade é chamada de Deferred Loading)

Realizando uma consulta com filtro simples

Vamos realizar uma consulta utilizando um filtro simples, como no exemplo de código:

BlogPerformanceDataContext dbContext = new BlogPerformanceDataContext();

 var query = from cust in dbContext.Customers
 where cust.FirstName == "John"
 select cust.LastName;

 foreach (string Nome in query)
 Console.WriteLine(Nome);

 Console.ReadLine();

Nessa consulta estou filtrando dos os clientes cujo o primeiro nome seja John, o SQL criado foi:

exec sp_executesql

N'SELECT [t0].[LastName]

FROM [SalesLT].[Customer] AS [t0]

WHERE [t0].[FirstName] = @p0',N'@p0 nvarchar(4)',@p0=N'John'

Aqui passa a se usar a procedure SP_executesql. Reparem que o filtro é passado como parâmetro para a consulta, podemos re-escrever esta consulta para o formato que será enviado ao engine do banco de dados:

SELECT [t0].[LastName]
FROM [SalesLT].[Customer] AS [t0]
WHERE [t0].[FirstName] = N'John'

Obtendo os registro pais e filhos

Vamos listar um cliente (filtrando pelo CustemerID) e os seus respectivos pedidos usando o código:

BlogPerformanceDataContext dbContext = new
BlogPerformanceDataContext();

var query = from c in dbContext.Customers
where c.CustomerID == 1

select c;

foreach (Customer cust in query)
{

Console.WriteLine(cust.LastName);

 foreach (SalesOrderHeader sales in cust.SalesOrderHeaders)
 Console.WriteLine("\t{0} - {1} - {2}", sales.SalesOrderNumber, sales.ShipDate, sales.SubTotal);

}

Nesse caso temos o seguinte:

Quando o código atinge o primeiro loop (em Customers) a seguinte query é enviada ao banco de dados:

exec
sp_executesql
N'SELECT [t0].[CustomerID], [t0].[NameStyle],
[t0].[Title], [t0].[FirstName],
[t0].[MiddleName], [t0].[LastName],
[t0].[Suffix], [t0].[CompanyName],
[t0].[SalesPerson], [t0].[EmailAddress],
[t0].[Phone], [t0].[PasswordHash],
[t0].[PasswordSalt],
[t0].[rowguid],
[t0].[ModifiedDate]
FROM [SalesLT].[Customer] AS [t0]
WHERE [t0].[CustomerID] = @p0',N'@p0 int',@p0=1

E, para o loop de pedidos temos a consulta:

exec
sp_executesql
N'SELECT [t0].[SalesOrderID],

[t0].[RevisionNumber],

[t0].[OrderDate], [t0].[DueDate],

[t0].[ShipDate], [t0].[Status],

[t0].[OnlineOrderFlag], [t0].[SalesOrderNumber],

[t0].[PurchaseOrderNumber], [t0].[AccountNumber],

[t0].[CustomerID], [t0].[ShipToAddressID],

[t0].[BillToAddressID], [t0].[ShipMethod],

[t0].[CreditCardApprovalCode], [t0].[SubTotal],

[t0].[TaxAmt], [t0].[Freight], [t0].[TotalDue],

[t0].[Comment], [t0].[rowguid], [t0].[ModifiedDate]

FROM [SalesLT].[SalesOrderHeader] AS [t0]

WHERE [t0].[CustomerID] = @p0',N'@p0 int',@p0=1

Se a consulta inicial retorna-se mais de um registro, a segunda consulta seria executada para cada um desses registros. Ou seja, se a consulta inicial retorna 10 registros, para cada um desses 10 registros será executada mais uma query para obter os pedidos. Isto em um cenário de alta concorrência poderá ser um fator de contenção e degradação.

Utilizando o DataLoadOptions

Realizando uma consulta semelhante a anterior mas agora utilizando o DataLoadOption:

BlogPerformanceDataContext dbContext = new
BlogPerformanceDataContext();

DataLoadOptions dOptions = new
DataLoadOptions();

dOptions.LoadWith<Customer>(c => c.SalesOrderHeaders);
dbContext.LoadOptions = dOptions;

var query = from c in dbContext.Customers
where c.FirstName == "Walter"
select c;

foreach (Customer cust in query)
{

Console.WriteLine(cust.LastName);

foreach (SalesOrderHeader sales in cust.SalesOrderHeaders)
Console.WriteLine("\t{0} - {1} - {2}", sales.SalesOrderNumber, sales.ShipDate, sales.SubTotal);

}

O interessante que agora é realizada apenas uma única consulta ao banco dados, quando o código do primeiro loop é executado:

exec
sp_executesql
N'SELECT [t0].[CustomerID], [t0].[NameStyle], [t0].[Title], [t0].[FirstName],

[t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[CompanyName],

[t0].[SalesPerson], [t0].[EmailAddress], [t0].[Phone], [t0].[PasswordHash],

[t0].[PasswordSalt], [t0].[rowguid], [t0].[ModifiedDate], [t1].[SalesOrderID],

[t1].[RevisionNumber], [t1].[OrderDate], [t1].[DueDate], [t1].[ShipDate],

[t1].[Status], [t1].[OnlineOrderFlag], [t1].[SalesOrderNumber],

[t1].[PurchaseOrderNumber], [t1].[AccountNumber], [t1].[CustomerID] AS [CustomerID2],

[t1].[ShipToAddressID], [t1].[BillToAddressID], [t1].[ShipMethod],

[t1].[CreditCardApprovalCode], [t1].[SubTotal], [t1].[TaxAmt],

[t1].[Freight], [t1].[TotalDue], [t1].[Comment],

[t1].[rowguid] AS [rowguid2], [t1].[ModifiedDate] AS [ModifiedDate2], (

SELECT COUNT(*)

FROM [SalesLT].[SalesOrderHeader] AS [t2]

WHERE [t2].[CustomerID] = [t0].[CustomerID]

) AS [value]

FROM [SalesLT].[Customer] AS [t0]

LEFT OUTER JOIN [SalesLT].[SalesOrderHeader] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]

WHERE [t0].[LastName] = @p0

ORDER BY [t0].[CustomerID], [t1].[SalesOrderID]',N'@p0 nvarchar(4)',@p0=N'King'

O que está bem mais coerente ao esperado e apresenta uma performance melhor do que o exemplo anterior. Em um primeiro momento podemos notar o impacto da utilização ou não do DataLoadOption para performance de consultas Linq.

Continuaremos em outros artigos….

T+