使用原始SQL更新数据库中的字段？
使用原始SQL更新数据库中的字段？
提问于 2018-12-11 05:17:55
当我签入where条件时，我需要基于IsIgnored字段更新senderId字段，它没有识别出与loop.It相比的senderId抛出异常，比如模糊的列名‘senderid’，指导我解决这个问题。
foreach (var senderId in senderIdList)
{
using (var context = new BSoftWEDIIContext())
{
var ediDocuments = context.EDIDocuments.SqlQuery("Update EDIDocument SET IsIgnored=1 from EDIDocument edi inner JOIN FileDetails files on edi.FileDetailsId = files.Id where edi.IsDeleted = 0 and edi.SenderID =senderId and edi.DocumentTypeID != 3 and edi.DocumentTypeID != 5 and edi.DocumentTypeID != 2 and edi.IsIgnored = 0 and files.IsDeleted = 0" );
}像这样尝试过：
var ediDocuments = context.EDIDocuments.SqlQuery("Update EDIDocument SET IsIgnored=1 from EDIDocument edi inner JOIN FileDetails files on edi.FileDetailsId = files.Id where edi.IsDeleted = 0 and edi.SenderID ='149825353' and edi.DocumentTypeID != 3 and edi.DocumentTypeID != 5 and edi.DocumentTypeID != 2 and edi.IsIgnored = 0 and files.IsDeleted = 0", new SqlParameter
{
ParameterName = "senderId",
DbType = System.Data.DbType.String,
Value = senderId
});
foreach (var senderId in senderIdList)
{
using (var context = new BSoftWEDIIContext())
{
var ediDocuments = context.EDIDocuments.SqlQuery("Update EDIDocument SET IsIgnored=1 from EDIDocument edi inner JOIN FileDetails files on edi.FileDetailsId = files.Id where edi.IsDeleted = 0 and edi.SenderID=@senderId and edi.DocumentTypeID != 3 and edi.DocumentTypeID != 5 and edi.DocumentTypeID != 2 and edi.IsIgnored = 0 and files.IsDeleted = 0", new SqlParameter("@senderId", senderId));回答 3
Stack Overflow用户
回答已采纳
发布于 2018-12-11 05:35:30
需要将sql参数添加到查询中。
using (var context = new BSoftWEDIIContext())
{
foreach (var senderId in senderIdList)
{
context.EDIDocuments.ExecuteSqlCommand("Update EDIDocument SET IsIgnored=1 from EDIDocument edi inner JOIN FileDetails files on edi.FileDetailsId = files.Id where edi.IsDeleted = 0 and edi.SenderID=@senderId and edi.DocumentTypeID != 3 and edi.DocumentTypeID != 5 and edi.DocumentTypeID != 2 and edi.IsIgnored = 0 and files.IsDeleted = 0",
new SqlParameter
{
ParameterName = "senderId",
DbType = DbType.Int32,
Value = senderId
});
}
}Stack Overflow用户
发布于 2018-12-11 06:21:52
foreach (var senderId in senderIdList)
{
using (var context = new BSoftWEDIIContext())
{
var ediDocuments = context.EDIDocuments.SqlQuery($"Update EDIDocument SET IsIgnored=1 from EDIDocument edi inner JOIN FileDetails files on edi.FileDetailsId = files.Id where edi.IsDeleted = 0 and edi.SenderID={senderId} and edi.DocumentTypeID != 3 and edi.DocumentTypeID != 5 and edi.DocumentTypeID != 2 and edi.IsIgnored = 0 and files.IsDeleted = 0" );
}
}但是这种方法有可能注入SQL。
Stack Overflow用户
发布于 2018-12-11 07:10:11
首先要知道的是，DbSet.SqlQuery()方法主要用于执行SELECT语句，该语句根据相应的实体类型(即DbSet名称)返回结果集。如果您想执行像UPDATE命令这样的操作查询，则应该将Database.ExecuteSqlCommand()与SqlParameter[]数组一起用于参数，如下例所示：
string rawQuery = @"Update EDIDocument SET IsIgnored = 1 From EDIDocument AS edi
INNER JOIN FileDetails AS files on edi.FileDetailsId = files.Id
where edi.IsDeleted = 0 and edi.SenderID = @senderId
and edi.DocumentTypeID <> 3 and edi.DocumentTypeID <> 5
and edi.DocumentTypeID <> 2 and edi.IsIgnored = 0 and files.IsDeleted = 0";
using (var context = new BSoftWEDIIContext())
{
foreach (var senderId in senderIdList)
{
var queryParams = new List<SqlParameter>();
queryParams.Add(new SqlParameter("@senderId", senderId));
var ediDocuments = context.Database.ExecuteSqlCommand(rawQuery, queryParams.ToArray());
}
}注：
DbSet.SqlQuery()和Database.ExecuteSqlCommand()方法的第二个参数都使用object[]数组，因此需要将参数传递到数组中，而不是直接使用它们。
参考资料：
使用实体框架执行原始SQL查询
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53717797
复制相关文章
相似问题
腾讯云开发者
