如何使用Dynamic Linq使存在 [英] How to make Exists with Dynamic Linq

问题描述

如何使动态LINQ存在?

How to do I make exists with dynamic LINQ?

我正在尝试使用动态LINQ创建一个SQL子句.我研究了很多，但没有找到满意的答案.

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

我尝试如下转换SQL查询:

I try convert the SQL query as follows:

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')

在常见的LINQ中，我这样做如下:

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

或

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

这就是我所学的

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:

"DynamicClass3"类型中不存在适用的索引器.

No applicable indexer exists in type 'DynamicClass3'.

推荐答案

这似乎有效...它使用outerIt ...，而且我不知道Where()内的Any通过哪种魔术可以...

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)");

在Where()中的

中，it是子查询元素，而outerIt是documento.

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

我已经检查了SQL Profiler，查询的结果是:

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])
    )

等同于您的查询.

请注意，在Dynamic Linq中，甚至可以使用.Contains()来编写与您在Linq中编写的查询非常相似的查询(但请注意，与.Contains()相比，我更喜欢.Any()).这两个查询使用Entity Framework产生相同的SQL查询.

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)");

这篇关于如何使用Dynamic Linq使存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！