实体框架4.1：无法从投来的DBQuery的ObjectQuery [英] Entity Framework 4.1: Unable to cast from DbQuery to ObjectQuery

问题描述

我有以下的code：

public void DeleteAccountsForMonth(int year, int month)
{
    var result = from acm in this._database.AccountsOnMonth
                 where ((acm.Year == year) && (acm.Month == month))
                 select acm.Id;
    var query = (ObjectQuery<int>)result;

    string sql = string.Format(
        "DELETE FROM [AccountsOnMonth] WHERE [AccountsOnMonth].[Id] IN ({0})",
        query.ToTraceString()
    );

    var parameters = new List<System.Data.SqlClient.SqlParameter>();
    foreach (ObjectParameter parameter in query.Parameters)
    {
        parameters.Add(new System.Data.SqlClient.SqlParameter {
            ParameterName = parameter.Name,
            Value = parameter.Value
        });
    }

    this._database.Database.ExecuteSqlCommand(sql, parameters.ToArray());
}

基本上，我想要做的就是从环境中删除的大量数据（获得查询结果，让SQL和执行它）。但我铸造结果到的ObjectQuery 时有问题。 ，让唯一的例外是

Basically, what I'm trying to do is to delete a bulk of data from a context (get a query result, get SQL and execute it). But I'm having a problem when casting result to ObjectQuery. The exception that gives is

无法投类型的对象
  System.Data.Entity.Infrastructure.DbQuery 1 [System.Int32]'键入
  System.Data.Objects.ObjectQuery 1 [System.Int32]'。

Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery1[System.Int32]' to type 'System.Data.Objects.ObjectQuery1[System.Int32]'.

任何人都可以给任何暗示解决呢？谢谢！

Can anybody give any hint to solve this? Thanks!

编辑：拉吉斯拉夫第一个解决方案帮我解决了问题，但它happenned有点问题，生成的SQL查询的SQL参数，即通过生成的SQL查询查询的ToString（）是这样的：

Ladislav first solution helped me solve the problem, but it happenned a little problem with the SQL parameters of the generated SQL query, i.e. the SQL query generated by query.ToString() was this:

DELETE FROM [SncAccountOnMonths] WHERE [SncAccountOnMonths].[Id] IN (
    SELECT [Extent1].[Id] AS [Id]
    FROM [dbo].[SncAccountOnMonths] AS [Extent1]
    WHERE ([Extent1].[Year] = @p__linq__0) AND ([Extent1].[Month] = @p__linq__1))

问题是变量 @ p__linq__0 和 @ p__linq__1 其中没有申报等查询了错误必须声明标量变量@p_的 LINQ 的_0（我知道它会给出相同的变量误差 @ p__linq__1 ）。以申报他们我需要他们作为传递的 ExecuteSqlCommand（）参数。因此，对于最初的回答最终的解决方案低于code：

The problem was that the variables @p__linq__0 and @p__linq__1 where not declared and so the query gave the error "Must declare the scalar variable @p_linq_0" (I sure it would give the same error for variable @p__linq__1). To "declare" them I need to pass them as arguments of the ExecuteSqlCommand(). And so, the final solution for the initial answer is the code below:

public void DeleteAccountsForMonth(int year, int month)
{
    var result = (this._database.AccountsOnMonth
        .Where(acm => (acm.Year == year) && (acm.Month == month)))
        .Select(acm => acm.Id);
    var query = (DbQuery<int>)result;

    string sql = string.Format(
        "DELETE FROM [AccountsOnMonth] WHERE [AccountsOnMonth].[Id] IN ({0})",
        query.ToString()
    );

    this._database.Database.ExecuteSqlCommand(sql,
        new SqlParameter("p__linq__0", year),
        new SqlParameter("p__linq__1", month)
    );
}

顺便说一句，我认为总是产生的变量的格式 @p__linq __ ，除非微软的实体框架团队改变它在今后的任何EF更新...

By the way, I assume the variables generated always have the format @p__linq__, unless Microsoft's Entity Framework Team changes it in any future EF update...

推荐答案

这是因为你的 _database 是的DbContext 和您的 AccountsOfMonth 是 DbSet＆LT;＆GT; 。在这种情况下，你不能使用的ObjectQuery 直接，因为 DbSet＆LT;＆GT; 产生的DBQuery＆LT;＆GT; 这是不转换为的ObjectQuery＆LT;方式＆gt;

That is because your _database is derived from DbContext and your AccountsOfMonth is DbSet<>. In such case you cannot use ObjectQuery directly because DbSet<> produces DbQuery<> which is not convertible to ObjectQuery<>.

您必须使用的DBQuery＆LT;＆GT; 直接

var result = from acm in this._database.AccountsOnMonth
             where ((acm.Year == year) && (acm.Month == month))
             select acm.Id;
var query = (DbQuery<int>)result;

string sql = string.Format(
    "DELETE FROM [AccountsOnMonth] WHERE [AccountsOnMonth].[Id] IN ({0})",
    query.ToString()
);

或者，您必须首先上下文转换为的ObjectContext 并创建对象集＆LT;＆GT; ：

var objectContext = ((IObjectContextAdapter)_database).ObjectContext;
var set = objectContext.CreateObjectSet<AccountsOnMonth>();
var resut = from acm in set
            where ((acm.Year == year) && (acm.Month == month))
            select acm.Id;

与第一种方法的问题是，的DBQuery 不提供参数收集 - 只是一个在简化的例子的DbContext API只使得它更难使用。

The problem with first approach is that DbQuery doesn't offer Parameters collection - just another example of simplification in DbContext API which only makes it harder to use.

这篇关于实体框架4.1：无法从投来的DBQuery的ObjectQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！