How to make Exists with Dynamic Linq

Question

How to do I make exists with dynamic LINQ?

I'm trying to create an SQL clause with dynamic LINQ. I researched a lot of and not found an satisfactory answer.

I try convert the SQL query as follows:

SQL

select * from Documento where exists(
    select 1 from Titulo
    where Documento.codDocumento = Titulo.codDocumento
    and Documento.codEmpresa = Titulo.codEmpresaDocumento
    and Titulo.codFluxoConta = 'SomeValue'
    and Titulo.codEmpresaFluxoConta = 'StringNumerical')

In common LINQ I did as follows:

var subquery2 = from T in db.Titulos
    where T.codFluxoConta == "SomeValue"
    && T.codEmpresaFluxoConta == "StringNumerical"
    select new {
        codDoc = (int?)T.codDocumento,
        codEmp = (string)T.codEmpresaDocumento
    };

var query2 = from D in db.Documentos
    where subquery2.Contains(new { codDoc = (int?)D.codDocumento, codEmp = (string)D.codEmpresa })
    select new{
        D.codDocumento,
        D.codEmpresa
    };

or

var query4 = db.Documentos.Where(d =>
(db.Titulos.Where(t => t.codFluxoConta == "SomeValue" && t.codEmpresaFluxoConta == "StringNumerical").Select(t2 => new { codDoc = (int?)t2.codDocumento, codEmp = (string)t2.codEmpresaDocumento })).Contains(new { codDoc = (int?)d.codDocumento, codEmp = (string)d.codEmpresa }));

And this is what I did with my knowledge

IQueryable linq = db.Set(T);
var exists = linq.Where("codFluxoConta == @0 && codEmpresaFluxoConta == @1", "SomeValue", "StringNumerical").Select("new(\"codDocumento\" as codDoc, \"codEmpresaDocumento\" as codEmp)");
var query = db.Documentos.Where("@0.Contains(new (it[\"codDocumento\"] as codDocumento, it[\"codEmpresa\"] as codEmpresaDocumento))", exists);

But when I execute this code the following exception occurs:

No applicable indexer exists in type 'DynamicClass3'.

Answer 1

This seems to work... It uses outerIt... and I don't know through which magic the Any inside the Where() works...

var sq = titulo.Where("codFluxoConta == @0 && codEmpresaFluxoConta == @1", "SomeValue", "StringNumerical");
var result = documento.Where("@0.Any(it.codDocumento == outerIt.codDocumento && it.codEmpresaDocumento == outerIt.codEmpresaDocumento)", sq)
    .Select("new(codDocumento, codEmpresaDocumento)");

in the Where(), it is the subquery element, while outerIt is the documento.

I've checked with the SQL Profiler and the result of the query is:

SELECT 
    [Extent1].[codDocumento] AS [codDocumento], 
    [Extent1].[codEmpresaDocumento] AS [codEmpresaDocumento]
    FROM [dbo].[Documento] AS [Extent1]
    WHERE  EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Titulo] AS [Extent2]
        WHERE (N'SomeValue' = [Extent2].[codFluxoConta]) AND (N'StringNumerical' = [Extent2].[codEmpresaFluxoConta]) AND ([Extent2].[codDocumento] = [Extent1].[codDocumento]) AND ([Extent2].[codEmpresaDocumento] = [Extent1].[codEmpresaDocumento])
    )

that is equivalent to your query.

Note that in Dynamic Linq it is even possible to write a query much similar to the one you wrote in Linq, using .Contains() (but note that I do prefer the .Any() to the .Contains()). The two queries produce the same SQL query with Entity Framework.

var sq2 = db.Titulo.Where("codFluxoConta == @0 && codEmpresaFluxoConta == @1", "SomeValue", "StringNumerical")
    .Select("new(codDocumento, codEmpresaDocumento)");
var result2 = db.Documento.Where("@0.Contains(new(outerIt.codDocumento, outerIt.codEmpresaDocumento))", sq2)
    .Select("new(codDocumento as codDoc, codEmpresaDocumento as codEmp)");

Answer 2

Any() (https://msdn.microsoft.com/en-us/library/vstudio/bb534972%28v=vs.100%29.aspx) is what you should probably be focusing on. Any() will return True if if can find at least one item in the collection that satisfies the predicate given.

I don't have VS to test this but this should work for you.

db.Documento.Where(d => 
  db.Titulos.Any(t => 
    t.codDocumento == d.codDocumento &&
    t.codEmpresaDocumento == d.codEmpresa && 
    t.codFluxoConta == "" && 
    t.codEmpresaFloxoConta == ""
  )
);

Be careful that since you are likely using variables to compare against the codFluxoConta and codEmpresaFloxoConta, it won't read the values for the statement until you execute the query (http://blogs.msdn.com/b/charlie/archive/2007/12/09/deferred-execution.aspx). So if you're running this with different values (like in a loop) and saving the result, be sure to .ToList() or something the result before the value changes.