DbContext对于标量系统函数(Count,Any,Sum,Max)获取IQueryable [英] DbContext get IQueryable for scalar system functions (Count, Any, Sum, Max)
问题描述
我有一个带有DbSet的DBContext,称为Assignments. 创建可枚举表达式的查询并将其连接起来不是问题,但是我看不到通过延迟执行来获得IQueryable的方法,例如Count,Any,Max,Sum.
I have DBContext with DbSet called Assignments. It's not a problem to create queryable for enumerable expressions and concatenated them, however I don't see the way to get IQueryable with deferred execution for functions like Count, Any, Max, Sum.
基本上我想拥有一些IQueryable扩展名,以便可以像这样执行它:
Basically I want to have some IQueryable extension so I can execute it like this:
IQueryable<int> query =
myDbContext.SelectValue((ctx)=>ctx.Assignments.Where(...).Count())
.UnionAll(myDbContext.SelectValue((ctx)=>ctx.Assignments.Where(...).Count()));
并获取以下SQL(query.ToString()):
and get the following SQL (query.ToString()):
SELECT
[UnionAll1].[C1] AS [C1]
FROM (SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT([Extent1].[UserId]) AS [A1]
FROM [dbo].[Assignments] AS [Extent1]
WHERE ...
) AS [GroupBy1]
UNION ALL
SELECT
[GroupBy2].[A1] AS [C1]
FROM ( SELECT
COUNT([Extent2].[UserId]) AS [A1]
FROM [dbo].[Assignments] AS [Extent2]
WHERE ...
) AS [GroupBy2]) AS [UnionAll1]
重要提示:如您所见,我需要能够在带有联合和联接的子查询中使用它,并在最后生成一个SQL REQUEST.我不能使用RAW SQL,也不能为实体使用字符串名称,这就是为什么我看不到ObjectContextAdapter.ObjectContext.CreateQuery对我有用的原因.
IMPORTANT: As you see I need to be able to use it in sub queries, with unions and joins, having ONE SQL REQUEST GENERATED at the end. I cannot use RAW SQL and I cannot use string names for entities, that's why I don't see ObjectContextAdapter.ObjectContext.CreateQuery working for me.
此处 一种使用ObjectContext实现它的方法,但是我不能在我的情况下使用这种方法,因为它会引发错误:
Here you can find a way to achieve it using ObjectContext, but I cannot use this approach for my case, because it throws error:
无法创建赋值"类型的常量值.只有原始的 上下文中支持类型或枚举类型.
Unable to create a constant value of type 'Assignment'. Only primitive types or enumeration types are supported in this context.
推荐答案
与我对其他问题的回答相同的方法在这里也适用.这是使用EF5的独立测试程序:
The same approach as in my answer to that other question works here too. Here is a self-contained test program using EF5:
using System;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
namespace ScratchProject
{
public class A
{
public int Id { get; set; }
public string TextA { get; set; }
}
public class B
{
public int Id { get; set; }
public string TextB { get; set; }
}
public class MyContext : DbContext
{
public DbSet<A> As { get; set; }
public DbSet<B> Bs { get; set; }
protected IQueryProvider QueryProvider
{
get
{
IQueryable queryable = As;
return queryable.Provider;
}
}
public IQueryable<TResult> CreateScalarQuery<TResult>(Expression<Func<TResult>> expression)
{
return QueryProvider.CreateQuery<TResult>(
Expression.Call(
method: GetMethodInfo(() => Queryable.Select<int, TResult>(null, (Expression<Func<int, TResult>>)null)),
arg0: Expression.Call(
method: GetMethodInfo(() => Queryable.AsQueryable<int>(null)),
arg0: Expression.NewArrayInit(typeof(int), Expression.Constant(1))),
arg1: Expression.Lambda(body: expression.Body, parameters: new[] { Expression.Parameter(typeof(int)) })));
}
static MethodInfo GetMethodInfo(Expression<Action> expression)
{
return ((MethodCallExpression)expression.Body).Method;
}
}
static class Program
{
static void Main()
{
using (var context = new MyContext())
{
Console.WriteLine(context.CreateScalarQuery(() => context.As.Count(a => a.TextA != "A"))
.Concat(context.CreateScalarQuery(() => context.Bs.Count(b => b.TextB != "B"))));
}
}
}
}
输出:
SELECT
[UnionAll1].[C1] AS [C1]
FROM (SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[A] AS [Extent1]
WHERE N'A' <> [Extent1].[TextA]
) AS [GroupBy1]
UNION ALL
SELECT
[GroupBy2].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[B] AS [Extent2]
WHERE N'B' <> [Extent2].[TextB]
) AS [GroupBy2]) AS [UnionAll1]
是的,实际上执行查询也可以按预期工作.
And yes, actually executing the query works as expected too.
更新:
根据要求,您可以添加以下内容以使其也适用于Expression<Func<MyContext, TResult>> expression)
:
As requested, here is what you can add to get it working for Expression<Func<MyContext, TResult>> expression)
as well:
public IQueryable<TResult> CreateScalarQuery<TResult>(Expression<Func<MyContext, TResult>> expression)
{
var parameterReplacer = new ParameterReplacer(expression.Parameters[0], Expression.Property(Expression.Constant(new Tuple<MyContext>(this)), "Item1"));
return CreateScalarQuery(Expression.Lambda<Func<TResult>>(parameterReplacer.Visit(expression.Body)));
}
class ParameterReplacer : ExpressionVisitor
{
readonly ParameterExpression parameter;
readonly Expression replacement;
public ParameterReplacer(ParameterExpression parameter, Expression replacement)
{
this.parameter = parameter;
this.replacement = replacement;
}
protected override Expression VisitParameter(ParameterExpression node)
{
if (node == parameter)
return replacement;
return base.VisitParameter(node);
}
}
即使从当前上下文内部调用也可以:
This works even if called from inside the current context:
// member of MyContext
public void Test1()
{
Console.WriteLine(this.CreateScalarQuery(ctx => ctx.As.Count(a => a.TextA != "A"))
.Concat(this.CreateScalarQuery(ctx => ctx.Bs.Count(b => b.TextB != "B"))));
}
参数替换将上下文直接存储在Tuple<MyContext>
中而不是MyContext
中,因为EF不知道如何处理Expression.Constant(this)
.那是C#编译器永远不会产生的东西,因此EF不需要知道如何处理它.将上下文作为类的成员获得是C#编译器确实产生的东西,因此使EF知道如何处理它.
The parameter replacement stores the context in a Tuple<MyContext>
instead of MyContext
directly, because EF does not know how to handle Expression.Constant(this)
. That's something that the C# compiler will never produce anyway, so EF does not need to know how to handle it. Getting a context as a member of a class is something that the C# compiler does produce, so EF has been made to know how to handle that.
但是,如果将this
保存在本地变量中,则可以使CreateScalarQuery
的更简单版本起作用:
However, the simpler version of CreateScalarQuery
can be made to work too, if you save this
in a local variable:
// member of MyContext
public void Test2()
{
var context = this;
Console.WriteLine(this.CreateScalarQuery(() => context.As.Count(a => a.TextA != "A"))
.Concat(this.CreateScalarQuery(() => context.Bs.Count(b => b.TextB != "B"))));
}
这篇关于DbContext对于标量系统函数(Count,Any,Sum,Max)获取IQueryable的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!