Unable to cast from DbQuery to ObjectQuery in Entity Framework 4.1

c# ef-code-first entity-framework

Question

I own the next 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());
}

Basically, I'm attempting to remove a significant amount of info from a context (get a query result, get SQL and execute it). However, I'm having trouble casting.result to ObjectQuery The one that stands out is

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

Can somebody provide a solution to this? Thanks!

I was able to address the issue using Ladislav's initial approach, however there was a little issue with the SQL parameters of the created SQL query, i.e.query.ToString() as follows:

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

The variables were the issue.@p__linq__0 and @p__linq__1 were not defined, and the query returned the message "Must declare the scalar variable @p linq 0" (I'm sure it would return the same message for variable).@p__linq__1 Since I need to pass them off as arguments of theExecuteSqlCommand() . The following code is the final response to the original question:

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

By the way, I'm assuming that the produced variables are always formatted as@p__linq__ , until the Entity Framework Team at Microsoft fixes it in a later EF version...

1
18
8/27/2011 11:28:36 PM

Accepted Answer

This is due to your_database originates fromDbContext plus yourAccountsOfMonth is DbSet<> . Therefore, you are unable to utilizeObjectQuery directly sinceDbSet<> produces DbQuery<> which cannot be converted toObjectQuery<> .

If necessary, useDbQuery<> directly:

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

Alternatively, you must first change your context toObjectContext and produceObjectSet<> :

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;

The issue with the initial strategy is thatDbQuery doesn't provideParameters Collection is just another example of how DbContext API has been simplified, which only makes it more difficult to use.

23
7/1/2014 9:47:07 PM

Popular Answer

I discovered this solution since I absolutely required the settings in my situation:

var query = (DbQuery<int>)result;

FieldInfo internalQueryField = query.GetType().GetFields(BindingFlags.NonPublic | BindingFlags.Instance).Where(f => f.Name.Equals("_internalQuery")).FirstOrDefault();
var internalQuery = internalQueryField.GetValue(query);
FieldInfo objectQueryField = internalQuery.GetType().GetFields(BindingFlags.NonPublic | BindingFlags.Instance).Where(f => f.Name.Equals("_objectQuery")).FirstOrDefault();
ObjectQuery<int> objectQuery = objectQueryField.GetValue(internalQuery) as ObjectQuery<int>;

foreach (ObjectParameter objectParam in objectQuery.Parameters)
{
    SqlParameter sqlParam = new SqlParameter(objectParam.Name, objectParam.Value);
    // Etc...
}

I'm implementing SqlCacheDependency with Entity Framework. :-)



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow