O LINQ entrou no .NET como uma nova e poderosa linguagem de manipulação de dados. LINQ to SQL como parte dele permite que você se comunique de maneira bastante conveniente com um SGBD usando, por exemplo, Entity Framework. No entanto, ao usá-lo com frequência, os desenvolvedores esquecem de observar que tipo de consulta SQL o provedor consultável, no seu caso, o Entity Framework, irá gerar.
Vejamos dois pontos principais usando um exemplo.
Para fazer isso, crie um banco de dados de teste no SQL Server e crie duas tabelas nele usando a seguinte consulta:
Criando tabelas
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ref](
[ID] [int] NOT NULL,
[ID2] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
CONSTRAINT [PK_Ref] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ref] ADD CONSTRAINT [DF_Ref_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[ID] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Ref_ID] [int] NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
[Ref_ID2] [int] NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_Ref_ID] DEFAULT ((0)) FOR [Ref_ID]
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO
Agora vamos preencher a tabela Ref executando o seguinte script:
Preenchendo a tabela Ref
USE [TEST]
GO
DECLARE @ind INT=1;
WHILE(@ind<1200000)
BEGIN
INSERT INTO [dbo].[Ref]
([ID]
,[ID2]
,[Name])
SELECT
@ind
,@ind
,CAST(@ind AS NVARCHAR(255));
SET @ind=@ind+1;
END
GO
Vamos preencher de forma semelhante a tabela Cliente usando o seguinte script:
Preenchendo a tabela Cliente
USE [TEST]
GO
DECLARE @ind INT=1;
DECLARE @ind_ref INT=1;
WHILE(@ind<=12000000)
BEGIN
IF(@ind%3=0) SET @ind_ref=1;
ELSE IF (@ind%5=0) SET @ind_ref=2;
ELSE IF (@ind%7=0) SET @ind_ref=3;
ELSE IF (@ind%11=0) SET @ind_ref=4;
ELSE IF (@ind%13=0) SET @ind_ref=5;
ELSE IF (@ind%17=0) SET @ind_ref=6;
ELSE IF (@ind%19=0) SET @ind_ref=7;
ELSE IF (@ind%23=0) SET @ind_ref=8;
ELSE IF (@ind%29=0) SET @ind_ref=9;
ELSE IF (@ind%31=0) SET @ind_ref=10;
ELSE IF (@ind%37=0) SET @ind_ref=11;
ELSE SET @ind_ref=@ind%1190000;
INSERT INTO [dbo].[Customer]
([ID]
,[Name]
,[Ref_ID]
,[Ref_ID2])
SELECT
@ind,
CAST(@ind AS NVARCHAR(255)),
@ind_ref,
@ind_ref;
SET @ind=@ind+1;
END
GO
Assim, recebemos duas tabelas, uma das quais possui mais de 1 milhão de linhas de dados e a outra possui mais de 10 milhões de linhas de dados.
Agora, no Visual Studio, você precisa criar um projeto de teste do aplicativo de console do Visual C# (.NET Framework):
Em seguida, você precisa adicionar uma biblioteca para o Entity Framework interagir com o banco de dados.
Para adicioná-lo, clique com o botão direito no projeto e selecione Gerenciar pacotes NuGet no menu de contexto:
Em seguida, na janela de gerenciamento de pacotes NuGet que aparece, digite a palavra “Entity Framework” na janela de pesquisa e selecione o pacote Entity Framework e instale-o:
A seguir, no arquivo App.config, após fechar o elemento configSections, você precisa adicionar o seguinte bloco:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Em connectionString você precisa inserir a string de conexão.
Agora vamos criar 3 interfaces em arquivos separados:
- Implementando a interface IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementação da interface IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementação da interface IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
E em um arquivo separado criaremos uma classe base BaseEntity para nossas duas entidades, que incluirá campos comuns:
Implementação da classe base BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
A seguir, criaremos nossas duas entidades em arquivos separados:
- Implementação da classe Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementação da classe Cliente
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Customer")] public class Customer: BaseEntity { public int Ref_ID { get; set; } public int Ref_ID2 { get; set; } } }
Agora vamos criar um contexto UserContext em um arquivo separado:
Implementação da classe UserContex
using System.Data.Entity;
namespace TestLINQ
{
public class UserContext : DbContext
{
public UserContext()
: base("DbConnection")
{
Database.SetInitializer<UserContext>(null);
}
public DbSet<Customer> Customer { get; set; }
public DbSet<Ref> Ref { get; set; }
}
}
Recebemos uma solução pronta para realização de testes de otimização com LINQ to SQL via EF para MS SQL Server:
Agora insira o seguinte código no arquivo Program.cs:
Arquivo programa.cs
using System;
using System.Collections.Generic;
using System.Linq;
namespace TestLINQ
{
class Program
{
static void Main(string[] args)
{
using (UserContext db = new UserContext())
{
var dblog = new List<string>();
db.Database.Log = dblog.Add;
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
&& (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
var result = query.Take(1000).ToList();
Console.WriteLine(dblog[1]);
Console.ReadKey();
}
}
}
}
A seguir, vamos lançar nosso projeto.
Ao final do trabalho, será exibido no console:
Consulta SQL gerada
SELECT TOP (1000)
[Extent1].[Ref_ID] AS [Ref_ID],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
INNER JOIN [dbo].[Ref] AS [Extent2] ON ([Extent1].[Ref_ID] = [Extent2].[ID]) AND ([Extent1].[Ref_ID2] = [Extent2].[ID2])
Ou seja, em geral, a consulta LINQ gerou muito bem uma consulta SQL para o SGBD MS SQL Server.
Agora vamos alterar a condição AND para OR na consulta LINQ:
Consulta LINQ
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
|| (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
E vamos lançar nosso aplicativo novamente.
A execução irá travar com um erro devido ao tempo de execução do comando exceder 30 segundos:
Se você observar a consulta gerada pelo LINQ:
, então você pode ter certeza de que a seleção ocorre através do produto cartesiano de dois conjuntos (tabelas):
Consulta SQL gerada
SELECT TOP (1000)
[Extent1].[Ref_ID] AS [Ref_ID],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
CROSS JOIN [dbo].[Ref] AS [Extent2]
WHERE [Extent1].[Ref_ID] = [Extent2].[ID] OR [Extent1].[Ref_ID2] = [Extent2].[ID2]
Vamos reescrever a consulta LINQ da seguinte maneira:
Consulta LINQ otimizada
var query = (from e1 in db.Customer
join e2 in db.Ref
on e1.Ref_ID equals e2.ID
select new { Data1 = e1.Name, Data2 = e2.Name }).Union(
from e1 in db.Customer
join e2 in db.Ref
on e1.Ref_ID2 equals e2.ID2
select new { Data1 = e1.Name, Data2 = e2.Name });
Então obtemos a seguinte consulta SQL:
Consulta SQL
SELECT
[Limit1].[C1] AS [C1],
[Limit1].[C2] AS [C2],
[Limit1].[C3] AS [C3]
FROM ( SELECT DISTINCT TOP (1000)
[UnionAll1].[C1] AS [C1],
[UnionAll1].[Name] AS [C2],
[UnionAll1].[Name1] AS [C3]
FROM (SELECT
1 AS [C1],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
INNER JOIN [dbo].[Ref] AS [Extent2] ON [Extent1].[Ref_ID] = [Extent2].[ID]
UNION ALL
SELECT
1 AS [C1],
[Extent3].[Name] AS [Name],
[Extent4].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent3]
INNER JOIN [dbo].[Ref] AS [Extent4] ON [Extent3].[Ref_ID2] = [Extent4].[ID2]) AS [UnionAll1]
) AS [Limit1]
Infelizmente, em consultas LINQ só pode haver uma condição de junção, então aqui é possível fazer uma consulta equivalente usando duas consultas para cada condição e depois combiná-las através de Union para remover duplicatas entre as linhas.
Sim, as consultas geralmente serão não equivalentes, levando em consideração que podem ser retornadas linhas duplicadas completas. No entanto, na vida real, linhas duplicadas completas não são necessárias e as pessoas tentam se livrar delas.
Agora vamos comparar os planos de execução dessas duas consultas:
- para CROSS JOIN o tempo médio de execução é de 195 segundos:
- para INNER JOIN-UNION o tempo médio de execução é inferior a 24 segundos:
.
Também neste repositório, na pasta Planos, existem planos para execução de consultas com condições OR.
Fonte: habr.com