具有List.CompiledQuery包含(在列表中的位置)功能? [英] CompiledQuery with List.Contains (where...in list) functionality?

问题描述

我正在尝试使用Linq-to-Entities编写一个CompiledQuery，它将替换一个采用两个数组(在这种情况下为逗号分隔的TEXT)参数的存储过程.本质上，SQL是这样的:

I'm attempting to write a CompiledQuery using Linq-to-Entities that will replace a stored procedure that takes two array (in this case, comma-delimited TEXT) parameters. Essentially, the SQL is be something like this:

*Stored Proc definition*
@ArrayParm1    TEXT,
@ArrayParm2    TEXT
-- etc. 
SELECT [fieldList] 
FROM someTable
WHERE someTable.Field1 IN (SELECT * FROM dbo.fncCSVToTable(@ArrayParm1))
AND someTable.Field2 IN (SELECT * FROM dbo.fncCSVToTable(@ArrayParm2))

dbo.fncCSVToTable使用数组值创建一个单列临时表.

dbo.fncCSVToTable creates a single-column temp table with the array values.

将其转换为已编译的Linq-to-Entities查询似乎并不困难:

Converting this to a compiled Linq-to-Entities query didn't seem difficult:

public static Func<EntityContext, List<int>, List<string> IQueryable<EntityType>>
    SomeQuery = 
        CompiledQuery.Compile((EntityContext context, List<int> arrayParm1, 
                               List<string> arrayParm2) =>
            from c in context.SomeTableEntities
            where arrayParm1.Contains(c.Field1)
                && arrayParm2.Contains(c.Field2)
            select new EntityType
            { 
                //projection
            });

但是，我收到一个运行时错误，指出Func<>的参数不能为List，并且仅支持标量参数.这对我来说很有意义，但是我仍然觉得必须有一种方法可以在已编译的查询中执行此操作.有人知道在L2E CompiledQuery中做List.Contains或WHERE ... IN类型功能的任何方式吗?

However, I get a runtime error stating that the parameters of Func<> cannot be a List, and that only scalar parameters are supported. This makes sense to me, but I still feel like there's got to be a way to do this in a compiled query. Does anyone know of any way to do List.Contains or WHERE ... IN type functionality in a L2E CompiledQuery?

推荐答案

我怀疑在未编译查询的生成SQL中，它正在使用

I suspect that in the generated SQL for the non-compiled query, it's using

WHERE someTable.Field1 In (?, ?, ?)

例如，

代表三个值，而不是fncCSVToTable函数.

现在，编译查询的一部分是预先计算SQL ...在这里，确切的SQL将取决于列表中的项目数(因为它将需要许多查询参数).我怀疑这很尴尬，以至于它不被支持.

Now part of the point of compiling the query is to work out the SQL in advance... and here, the exact SQL will depend on the number of items in the lists (because it will need that many query parameters). I suspect that makes it sufficiently awkward that it's just not supported.

这篇关于具有List.CompiledQuery包含(在列表中的位置)功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！