Truques & Dicas De LINQ Para SQL: Operações De Texto
O LINQ trouxe-nos uma forma muito amigável de escrever consultas de forma independente do domínio das mesmas.
O facto de que o modo como as consultas são escritas é independente do domínio não quer dizer que todas vão ser compiladas e executadas do mesmo modo. É sempre necessário saber como o provedor se vai comportar.
O LINQ Para Objectos, por exemplo, vai compilar as consultas para chamadas a funções do tipo Func<> que retornam implementações de IEnumerable(T).
Por outro lado, o LINQ Para SQL vai compilar as consultas para uma árvore de expressões do tipo Expression<Func<>> e a sua execução retornará implementações de <IQueryable(T).
Porque as consultas LINQ Para SQL são compiladas para uma árvore de expressões, é possível que o provedor trate os elementos da árvore como bem entender.
Neste caso, isto quer dizer que todas as operações que poderem ser executadas na base de dados serão executadas na base de dados e o programador(a) tem de ter noção disto quando escrever as consultas.
Consideremos um exemplo usando a base de dados AdventureWorks (se não tiverem, podem descarregar daqui).
Eu quero construír uma lista de saudações para todos os empregados que tenham a marcaSalariedFlag, na forma:
[Mr.|Mrs.|Miss] <first name> <middle name> <last name>
Mas há um pequeno detalhe na base de dados: FirstName, MiddleName e LastName podem ter espaços no fim e eu não os quero.
É algo tão simples como isto:
var q1 = from e in context.Employees
where e.SalariedFlag
select
((e.Gender == 'F') ? ((e.MaritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " " +
e.Person.FirstName.Trim() +
(e.Person.MiddleName == null || e.Person.MiddleName.Trim().Length == 0 ? " " : " " + e.Person.MiddleName.Trim() + " ") +
e.Person.LastName.Trim();
que será executado na base de dados como:
SELECT ((((
(CASE
WHEN UNICODE([t0].[Gender]) = @p0 THEN
(CASE
WHEN UNICODE([t0].[MaritalStatus]) = @p1 THEN @p2
ELSE @p3
END)
ELSE CONVERT(NVarChar(4),@p4)
END)) + @p5) + LTRIM(RTRIM([t1].[FirstName]))) + (
(CASE
WHEN ([t1].[MiddleName] IS NULL) OR (LEN(LTRIM(RTRIM([t1].[MiddleName]))) = @p6) THEN CONVERT(NVarChar(MAX),@p7)
ELSE (@p8 + LTRIM(RTRIM([t1].[MiddleName]))) + @p9
END))) + LTRIM(RTRIM([t1].[LastName])) AS [value]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [70]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [83]
-- @p2: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [Miss]
-- @p3: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [Mrs.]
-- @p4: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [Mr.]
-- @p5: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- @p6: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
-- @p7: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- @p8: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- @p9: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926
Como podem ver, há um elevado número de operações sobre texto a serem feitas, na base de dados, por cada linha retornada.
Dependendo do número de linhas ou da carga na base de dados isto pode ser muito mau. Pode inclusive resultar num timeout.
Sendo assim, como é que forçamos as operações de texto a ocorrer no cliente em vez da base de dados?
Apenas os IQueryable<T> serão traduzidos para T-SQL. Por isso, tudo o que é necessário fazer é mudar o tipo de enumerador a iterar.
Uma forma de o fazer é usar o método AsEnumerable da classe Enumerable.
A nova consulta será escrita assim:
var q2 = from e in context.Employees.Where(e => e.SalariedFlag).AsEnumerable()
select
((e.Gender == 'F') ? ((e.MaritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " " + e.Person.FirstName.Trim() +
(e.Person.MiddleName == null || e.Person.MiddleName.Trim().Length == 0 ? " " : " " + e.Person.MiddleName.Trim() + " ") +
e.Person.LastName.Trim();
e executada na base de dados como:
SELECT
[t0].[BusinessEntityID],
[t0].[LoginID],
[t0].[NationalIDNumber],
[t0].[JobTitle],
[t0].[MaritalStatus],
[t0].[BirthDate],
[t0].[Gender],
[t0].[HireDate],
[t0].[SalariedFlag],
[t0].[VacationHours],
[t0].[SickLeaveHours],
[t0].[CurrentFlag],
[t0].[rowguid],
[t0].[ModifiedDate],
[t1].[BusinessEntityID] AS [BusinessEntityID2],
[t1].[PersonType],
[t1].[NameStyle],
[t1].[Title],
[t1].[FirstName],
[t1].[MiddleName],
[t1].[LastName],
[t1].[Suffix],
[t1].[EmailPromotion],
[t1].[AdditionalContactInfo],
[t1].[Demographics],
[t1].[rowguid] AS [rowguid2],
[t1].[ModifiedDate] AS [ModifiedDate2]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926
Como podem notar, as operações de texto já não são executadas na base de dados mas, em contrapartida, todas as colunas de ambas as tabelas estão a ser retornadas. E isto continua a ser mau porque está a ser consumida largura de banda desnecessáriamente.
A forma de escolher as columas que serão retornadas na consulta é seleccionar apenas as colunas pretendidas. Mas porque continuamos a querer que as operações sobre texto sejam executadas no cliente, temos de projectar essas colunas num objecto intremédio. Prque não necessitamos desse objecto fora da consulta, usaremos um tipo anónimo.
A consulta será agora escrita assim:
var q3 = from n in
(
from e in context.Employees
where e.SalariedFlag
select new
{
Gender = e.Gender,
MaritalStatus = e.MaritalStatus,
FirstName = e.Person.FirstName,
MiddleName = e.Person.MiddleName,
LastName = e.Person.LastName
}
).AsEnumerable()
select ((n.Gender == 'F') ? ((n.MaritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " " + n.FirstName.Trim()
+ (n.MiddleName == null || n.MiddleName.Trim().Length == 0 ? " " : " " + n.MiddleName.Trim() + " ")
+ n.LastName.Trim();
e executada na base de dados como:
SELECT
[t0].[Gender],
[t0].[MaritalStatus],
[t1].[FirstName],
[t1].[MiddleName],
[t1].[LastName]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926
Note-se que a chamada a Enumerable.AsEnumerable para traduzir de LINQ Para SQL para LINQ Para Objectos.
E, para terminar, Se não se usarem operações de texto na consulta, estas não serão, obviamente, traduzidas para T-SQL:
var q4 = from e in context.Employees
where e.SalariedFlag
select BuildSalutation(e.Gender, e.MaritalStatus, e.Person.FirstName, e.Person.MiddleName, e.Person.LastName);
em que BuildSalutation é implementado como:
private static object BuildSalutation(char gender, char maritalStatus, string firstName, string middleName, string lastName)
{
return ((gender == 'F') ? ((maritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " "
+ firstName.Trim()
+ (middleName == null || middleName.Trim().Length == 0 ? " " : " " + middleName.Trim() + " ")
+ lastName.Trim();
}
e executado na base de dados como:
SELECT
[t0].[Gender] AS [gender],
[t0].[MaritalStatus] AS [maritalStatus],
[t1].[FirstName] AS [firstName],
[t1].[MiddleName] AS [middleName],
[t1].[LastName] AS [lastName]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926
É de notar que a consulta T-SQL gerada é praticamente a mesma que no caso anterior.
Se ainda está a ler. espero que tenha ficado com a noção de que, a forma como escrever as consultas LINQ Para SQL afecta o T-SQL gerado.