我有以下代码:
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 并执行它)。但我在投射时遇到问题result
to ObjectQuery
。给出的例外是
无法转换类型的对象
'系统.数据.实体.基础设施.DbQuery1[System.Int32]' to type
'System.Data.Objects.ObjectQuery
1[系统.Int32]'。
有人可以给出任何提示来解决这个问题吗?谢谢!
EDIT:Ladislav第一个解决方案帮助我解决了这个问题,但是生成的SQL查询的SQL参数发生了一点问题,即生成的SQL查询query.ToString()
是这样的:
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
and @p__linq__1
未声明的地方,因此查询给出错误“必须声明标量变量@p_linq_0”(我确信它会给变量带来相同的错误@p__linq__1
)。要“声明”它们,我需要将它们作为参数传递ExecuteSqlCommand()
。因此,最初答案的最终解决方案是以下代码:
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__
,除非 Microsoft 的实体框架团队在任何未来的 EF 更新中更改它......