quinta-feira, 12 de agosto de 2010

Declarações IN e NOT IN no Linq to Entity

Esses dias eu quebrei a cabeça para fazer uma declaração IN e NOT IN em minha query.
Enfim, eu tinha pensado que já tinha arrumado uma solução, que seria utilizar o Contains:

var query =

from i in dm.inspecoes

where i.idtipo_operacao == 6

&& !(from i2 in dm.inspecoes

where i.idtipo_operacao == 5

select i2.chassi)

.Contains(i.chassi)

select i;

foreach (var c in query) Console.WriteLine( c );

Nossa, fiquei super alegre em saber que eu poderia utilizar o Contains, que geraria uma clausula CONTAINS no comando SQL final.
Dai fui implementar...e...nada! :S
Dava o seguinte erro:

LINQ to Entities does not recognize the method 'Boolean Contains[String](System.Linq.IQueryable`1[System.String], System.String)' method, and this method cannot be translated into a store expression.

Igual diz o Milton Leite: "Que beleza!"
Pesquisando na internet, vi que o Entity não tem suporte para o método Contains. Dai pensei: Que padronização maravilhosa! essa é a Microsoft! :(

A SUBQUERY
Uma forma que eu arrumei de solucionar esse problema, é utilizar a clausula Where da minha entidade.
Primeiro de tudo, eu separei a subquery em uma outra variavel.
Nessa subquery, nós possuimos todas as ocorrências que nós queremos inserir dentro da clausula IN:

var subQuery = (from i2 in dm.inspecoes
where idtipo_operacao == 5
select new { chassi = "'" + i2.chassi + "'" });

Como o campo do meu critério é do tipo VARCHAR e não INT, eu preciso inserir uma apostrofe antes e depois do valor de cada campo, dai o resultado seria: { '123455abcde', '987654fedcba'}
Até ai beleza, montamos nossa SubQuery.
Agora, precisamos passar os valores para um array, para que posteriormente tenhamos os valores dentro da clausula IN. Nossa array fica dessa forma:

string[] resultados;
resultados = subQuery.Select(r => r.chassi).ToArray();

Para finalizar, basta inserirmos a clausula IN ou NOT IN em nossa query principal:

A QUERY PRINCIPAL E A SOLUÇÃO
var queryFinal = dm.inspecoes.Where("it.idtipo_operacao = 6 AND it.chassi IN {" + string.Join(",", resultados) + "}");

Notem que eu usei como alias para a minha tabela a sigla "it". Esse alias é criado pelo Emtity por padrão para a entidade selecionada.

Depois, com esse resultado você pode voltar um list ou um array.
return queryFinal.ToList();
return queryFinal.ToArray();

OBSERVAÇÕES
Como o Entity Framework não tem suporte para o Contains, esse foi um método que consegui obter o resultado que queria. Porém, se formos pensar bem, teremos uma queda de desempenho considerável em nossa query.
O que a query acima faz, é o seguinte:
SELECT * FROM inspecoes i WHERE i.chassi NOT IN ('331311233', '2323434244', '2342324234', '2343232423');

Lembra que passamos o resultado da subquery para um array? Então, esse array nada mais é que o seguinte resultado: { '331311233', '2323434244', '2342324234', '2343232423' }

Se o Entity desse suporte para o Contains, o resultado final seria:
SELECT * FROM inspecoes i WHERE i.idtipo_operacao = 6 AND i.chassi NOT IN (SELECT chassi FROM i i2 WHERE i2.idtipo_operacao = 5)

A consulta SQL seria mais rápida, porque o SGBD não precisará verificar valor por valor do conteudo que esta na clausula IN. Sem contar que podemos ter o campo "chassi" indexado, o que ajudaria no desempenho de nossas consultas.

To passando um perrengue danado com esse Entity, mas chego lá!!