LINQ to SQL na Pr�tica com C#

Guia do artigo:

Banco de Dados
Consultas b�sicas
Classes An�nimas
Agregados
Filtros
Ordenaç�o
Operadores de Conjuntos

Para voc� que usar� esta apostila, est� subentendido que voc� conheça e j� tenha instalado em seu computador o SQL Server e .NET Framework 3.5/4.0 ou Visual Studio 2008/2010.

Esta apostila ser� feita com base no LinqPad, um aplicativo gr�tis que tem na rede e pode ser baixado na URL https://www.linqpad.net/.

O banco de dados que usaremos � o Northwind que � um banco de dados exemplo da Microsoft. Este banco de dados tem que est� instalado no SQL Server (pode ser o Express � Vers�o gr�tis) de sua m�quina.

Conectando com Banco de Dados

Existem dois tipos de conex�o, uma � direta no Banco de Dados, a outra � atrav�s do assembly gerado (DLL) que cont�m um ou mais DataContext (Classe que mapeia as tabelas do banco de dados).

Usaremos primeiramente a conex�o direta com o banco de dados, para isso, com o LINQPad aberto clique em Add connection.

Na janela LINQPad, no combo Data Context, selecione Automatic.

Propriedades do grupo Options:

Pluralize EntitySet and Table properties � Coloca os nomes das tabelas no plural (acrescenta a letra �S�).
Capitalize property names � Coloca a primeira letra dos campos em mai�sculas
Include Stored Procedures and Funcion � Acrescenta na lista os nomes das procedures.

Realizando consultas b�sicas

Neste cap�tulo vamos realizar algumas consultas para conhecer a estrutura do LINQPad, mas antes vamos conhecer os tipos de consulta dispon�veis.

C# Expression � express�o na estrutura do C# usando a estrutura a partir do from.
from e in Shippers select e
C# Statement � express�o feita com vari�veis e o uso do m�todo Dump para executar
var x = from e in Shippers select e; x.Dump();
C# Program � estrutura de classe contendo apenas m�todos em C#
void Main() { Console.Write("Exemplo de Consulta em Tabela de Empresas"); var tabela = from e in Shippers select e; tabela.Dump("Tabela de Empresas"); }

O resultado das consultas acima � semelhante a imagem a seguir:

Classes An�nimas no .NET Framework 3.5

A partir do framework 3.0, foi introduzido as classes an�nimas e o tipo var. Esse tipo de objeto recebe qualquer tipo de dados, s� que ele s� vai reconhecer o seu tipo quando for atribu�do um valor.

No c�digo ao lado estamos vendo o intellisense da vari�vel x que assume as propriedades e os m�todos do tipo string que � o tipo referente a propriedade Text do formul�rio.

Este tipo de vari�vel pode receber um tipo an�nimo. Tipos an�nimos fornecem uma maneira conveniente para encapsular um conjunto de propriedades somente leitura em um Simples objeto sem ter que primeiro definir um tipo explicitamente. O nome do tipo � gerado pelo compilador e n�o est� dispon�vel no n�vel do c�digo fonte. O tipo das propriedades � inferido pelo compilador.

Veja o exemplo a seguir como se comporta a inst�ncia de um tipo an�nimo e seu uso dentro do c�digo C#.

Perceba que o objeto pessoa � uma inst�ncia de um tipo an�nimo que cont�m duas propriedades: Nome e Idade. O intellisense do Visual Studio mostra os tipos que pessoa pode assumir.

Podemos fazer qualquer tipo an�nimo, at� listas, coleç�es, tipos enumerados entre outros, veja o exemplo abaixo de um LINQ acessando uma lista de tipo an�nimo.

O LINQ tamb�m retorna tipos an�nimos, e s�o atrav�s deles que podemos selecionar os campos que desejamos ou fazer campos calculados em tempo de execuç�o. Veja abaixo:

O m�todo First() retorna o primeiro da lista, no caso foi uma lista do tipo an�nima pois foi feito a partir do new{} que � a declaraç�o base para um tipo an�nimo.

Podemos fazer, como no exemplo abaixo, campos calculados que cont�m f�rmulas, express�es entre outros.

void Main()
 {
     var clientes = from c in Customers
                 select new {
                     Codigo = c.CustomerID,
                     Nome = c.CompanyName,
                     Tamanho = c.CompanyName.Length
                 };
     Console.Write("O primeiro cliente �:" +
         clientes.First().Nome);    
     clientes.Dump();    
 }

Dica: Toda vez que desejamos fazer uma consulta com o LINQ e retornar apenas alguns campos ou campos calculados atrav�s do select new{} onde gera um retorno do tipo An�nimo, use o var para receber este valor.

Agregados

Agora vamos trabalhar com algumas funç�es agregadas que j� conhecemos na estrutura do SQL-Ansi utilizando o LAMBDA e o LINQ.

No exemplo abaixo ser� exibido a quantidade de registros que satisfez a consulta realizada.

void Main()
{
    var clientes = from c in Customers
    select c;
    Console.Write(string.Format("Foram encontrados  registro(s).",clientes.Count()));
    clientes.Dump();    
}

Neste exemplo abaixo, foi feito uma consulta que retornasse quantos territ�rios faz parte da mesma regi�o:

void Main()
{
    var Territorio = from c in Territories
      group c.RegionID by c.RegionID into agrupamento
      select new {
          CodigoDaRegiao = agrupamento.Key, Quantidade = agrupamento.Count()
      };
    
    Territorio.Dump();    
}

Outro exemplo quer retorna a lista completa separada pela regi�o:

void Main()
{
    var clientes = from c in Territories
        group c by c.RegionID into agrupamento
        select agrupamento;
    
    clientes.Dump();    
}

Para saber quanto custou todos os Frentes das Vendas de cada Vendedor:

void Main()
{
    var vendas = from v in Orders
    group v.Freight by v.EmployeeID into grupo
    select new {
          Vendedor = grupo.Key,
          TotalFrete = grupo.Sum()
    };
    Console.Write(vendas.Count());
    vendas.Dump();
}

Aprimorando a consulta, e se desejarmos consultar todas as vendas com o nome do Vendedor que se encontra em outra tabela ordenado pelo maior somat�rio do frete.

void Main()
{
    var vendas = from venda in Orders
         join vendedor in Employees on venda.EmployeeID equals vendedor.EmployeeID
         group venda by new {venda.EmployeeID, vendedor.FirstName} into grupoVenda
         orderby grupoVenda.Sum(o => o.Freight) descending
         select new {
             CodigoVendedor = grupoVenda.Key.EmployeeID,
            NomeVendedor = grupoVenda.Key.FirstName,
            MediaFrete = grupoVenda.Average(o=> o.Freight),
            MaiorFrete = grupoVenda.Max(o => o.Freight),
            MenorFrete = grupoVenda.Min(o => o.Freight),
            TotalFrete = grupoVenda.Sum( o => o.Freight),
            QuantFrete = grupoVenda.Count()
                };    
    vendas.Dump();    
}

Filtros

Neste cap�tulo veremos exemplos de como realizar filtros no LINQ para retornar os dados desejados. Vamos iniciar procurando Clientes que s�o da Alemanha.

void Main()
{
    var clientes = from c in Customers
        where c.Country == "Germany"
        select c;
    
    clientes.Dump();
}

Clientes que s�o da Alemanha ou M�xico:

void Main()
{
    var clientes = from c in Customers
        where c.Country == "Germany" || c.Country == "Mexico"
        select c;
    
    clientes.Dump();
}

Mesmo que o filtro anterior, acrescentando que o nome n�o pode exceder a 15 caracteres:

void Main()
{
    var clientes = from c in Customers
        where (c.Country == "Germany" || c.Country == "Mexico" ) &&
          c.CompanyName.Length<=15
        select c;
    
    clientes.Dump();
}

Retornar a lista de vendas realizadas, agrupadas por pa�s, cujo a quantidade seja maior a 100:

void Main()
{
    var ListaNotas = from Notas in Orders
    group Notas by Notas.ShipCountry into grupo
    where grupo.Count() > 100
    select new {
       Pais = grupo.Key,
       Quantidade = grupo.Count()
    };    
    ListaNotas.Dump();
}

Ordenaç�o

Sempre desejamos que nossas pesquisas tenham ordenaç�o por um ou mais campos, ou at� mesmo por campos calculados entre outras formas. Neste cap�tulo ser� dado alguns exemplos de ordenaç�o.

Clientes em ordem alfab�tica crescente pelo nome da empresa:

void Main()
 {
     var clientes = 
         (
             from c in Customers
             orderby c.CompanyName // Ordenar pelo nome da empresa
             select new {c.CustomerID, c.CompanyName, c.ContactName, c.Country}
         ).Take(5);
         
     clientes.Dump();
 }

Ordenando pelo Pa�s depois pelo Nome da Empresa:

void Main()
 {
     var clientes = 
         (
             from c in Customers
             orderby c.Country, c.CompanyName
             select new {c.CustomerID, c.CompanyName, c.ContactName, c.Country}
         ).Take(5);
         
     clientes.Dump();
 }

Ordenando pelo Pa�s depois pelo comprimento do nome da Empresa:

void Main()
 {
     var clientes = 
         (
             from c in Customers
             orderby c.Country, c.CompanyName.Length
             select new {c.CustomerID, c.CompanyName, c.ContactName, c.Country}
         ).Take(5);
         
     clientes.Dump();
 }

Ordenando em ordem decrescente pelo nome do Pa�s e crescente pelo Nome do Contato:

void Main()
 {
     var clientes = 
         (
             from c in Customers
             orderby c.Country descending, c.ContactName
             select new {c.CustomerID, c.CompanyName, c.ContactName, c.Country}
         ).Take(5);
         
     clientes.Dump();
 }

Detalhamento (Downdrill)� Sabemos que os empregados possuem v�rias vendas, e n�o necessariamente precisamos entrar na tabela de Ordens para selecionar as vendas. Podemos localizar o empregado e dentro dele selecionar as vendas realizadas.

Vamos localizar o vendedor de sobrenome Davolio e listar as cinco primeiras vendas:

var emp = (from e in Employees
  where e.LastName.Contains("Davolio")
  select e).Single();
         
 var ord = from o in emp.Orders
  select o;
     
 Console.WriteLine("As vendas feitas por ( ) s�o:", 
 emp.FirstName, emp.LastName);            
 ord.Dump();

Operadores de Conjuntos

Fazem parte dos operadores de conjuntos os: Distinct, Union, Intersect e Except.

Distinct (Distinto) � n�o aparece duplicado os dados dos campos selecionados.

Exemplo sem uso do Distinct:

void Main()
 {
     var clientes = from c in Customers
     where c.Region != null
     orderby c.Country , c.Region
     select new {c.Country, c.Region};
     clientes.Take(12).Dump();
 }

Exemplo com Distinct:

void Main()
 {
     var clientes = (from c in Customers
     where c.Region != null
     orderby c.Country , c.Region
     select new {c.Country, c.Region}).Distinct();
     clientes.Take(12).Dump();
 }

Union � Serve para unir duas tabelas iguais (estrutura iguais) com resultados diferentes

Exemplo: Lista os 5 primeiros vendedores que a data de contrataç�o seja feita no dia 17 independe de m�s e ano, e todos os vendedores que sejam dos Estados Unidos, os campos listados ser�o: nome, < span class=�lf-badge�>>pa�s e < span class=�lf-badge�>>data de contrato:

void Main()
 {
     var tabela1 = from e in Employees
                   where e.HireDate.Value.Day == 17
                   select e;        
     var tabela2 = from e in Employees
                   where e.Country == "USA"
                   select e;
     var resultado = from r in tabela1.Union(tabela2)
                     select new {r.FirstName, r.Country, r.HireDate};    
     resultado.Take(5).Dump();
 }

Intersect � Faz a interseç�o entre duas tabelas retornando apenas as linhas que satisfazem ambas as tabelas.

No exemplo abaixo faremos a listagem de todos os vendedores que foram admitidos no dia 17, e que sejam de UK:

void Main()
 {
     var tabela1 = from e in Employees
                   where e.HireDate.Value.Day == 17
                   select e;        
     var tabela2 = from e in Employees
                   where e.Country == "UK"
                   select e;
     var resultado = from r in tabela1.Intersect(tabela2)
                     select new {r.FirstName, r.Country, r.HireDate};    
     resultado.Take(5).Dump();
 }

O Intersect tamb�m pode ser usado para realizar subquerys como no exemplo que segue em SQL Ansi e em LINQ.

No exemplo abaixo vamos visualizar todas as vendas (orders) feitas por vendedores (employee) cujo seu pa�s seja Inglaterra (UK).

select * from Orders
where EmployeeID in (select EmployeeID from Employees where Country = 'UK')

Em LINQ ficar�:

// Retorna a lista de Employee do pa�s UK
var emp = (from a in Employees
where a.Country == "UK"
select a.EmployeeID);
 
// Lista todos as vendas pelos Employees de UK
var order = from o in Orders
where emp.Contains((int)o.EmployeeID)
select o;

var order = from o in Orders
where 
(from a in Employees
where a.Country == "UK" select a.EmployeeID
).Contains((int)o.EmployeeID)
select o;

Confira tamb�m

Curso de SQL Server

Curso

O que � .NET?

Curso

Minha primeira aplicaç�o C#

Curso

LINQ to SQL na Pr�tica com C# - Parte 1

Neste artigo você aprenderá LINQ to SQL na Prática com C#.

Guia do artigo:

Conectando com Banco de Dados

Realizando consultas b�sicas

Classes An�nimas no .NET Framework 3.5

Agregados

Filtros

Ordenaç�o

Operadores de Conjuntos

Confira tamb�m

Curso de SQL Server

O que � .NET?

Minha primeira aplicaç�o C#

Artigos relacionados