我一直在尝试将LINQKit整合到共享数据访问层中,但是遇到了一个难题。当使用
当您删除
ExpandableQuery
构建嵌套查询时,表达式解析器无法正确地展开ExpandableQuery
并构建有效的SQL查询语句。抛出的错误消息如下:
System.NotSupportedException:无法创建类型为“Store”的常量值。只支持原始类型或枚举类型。
通过以下示例程序,我们可以轻松重现此问题,并且很明显是在`table的AsExpandable()
`调用中隔离出来的。
class Program
{
static void Main(string[] args)
{
Database.SetInitializer<MyContext>(null);
var cs = MY_CONNECTION_STRING;
var context = new MyContext(cs);
var table = (IQueryable<Store>)context.Set<Store>();
var q = table
.AsExpandable()
.Select(t => new {Id = t.StoreId, less = table.Where(tt => tt.StoreId > t.StoreId) })
.Take(1)
.ToArray();
}
}
public class MyContext : DbContext
{
public MyContext(string connection) : base(connection) {}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Store>();
base.OnModelCreating(modelBuilder);
}
}
[Table("stores")]
public class Store
{
[Key]
public int StoreId { get; set; }
}
当您删除
AsExpandable()
调用时,生成的SQL语句将执行预期的三角连接操作。SELECT
[Project1].[StoreId] AS [StoreId],
[Project1].[C1] AS [C1],
[Project1].[StoreId1] AS [StoreId1]
FROM ( SELECT
[Limit1].[StoreId] AS [StoreId],
[Extent2].[StoreId] AS [StoreId1],
CASE WHEN ([Extent2].[StoreId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM (SELECT TOP (1) [c].[StoreId] AS [StoreId]
FROM [dbo].[stores] AS [c] ) AS [Limit1]
LEFT OUTER JOIN [dbo].[stores] AS [Extent2] ON [Extent2].[StoreId] > [Limit1].[StoreId]
) AS [Project1]
ORDER BY [Project1].[StoreId] ASC, [Project1].[C1] ASC
然而,当您包含 AsExpandable()
时,Entity Framework 将整个 stores 表格加载到内存中,然后出现“无法创建常量”错误。
是否有已知的解决方法可以强制 LINQKit 展开 ExpandableQuery
并在表达式解析器中评估嵌套子查询?