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

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.

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();    
 }

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 {0} 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 ({0} {1}) 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;

Ou

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