To update the field in database using Raw SQL?

Question

I need to update IsIgnored field based on senderId when I check in where condition,It not identifying senderId which I compared from loop.It throws exception like ambiguous column name 'senderid'.Guide me on this to resolve this issue.

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

                        }

Tried like this:

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

What is the `senderId`? If it's a parameter, you need to use `@` prefix and use `SqlParameter`; otherwise put table alias to distinguish it from same column name in other tables e.g. `files.senderId`. — Tetsuya Yamamoto, Dec 11 '18 at 05:34

Maxim Saltanov · Accepted Answer · 2018-12-11T07:14:59.200

0

You need to add the sql parameter to your query.

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

edited Dec 11 '18 at 07:14

answered Dec 11 '18 at 05:35

Maxim Saltanov

16
4

edi.SenderID=@senderId - are you sure that you added @ symbol to the param in the sql query? – Maxim Saltanov Dec 11 '18 at 06:45
added ,now it showing like The data reader is incompatible with the specified 'BSoftWEDII.Data.EDIDocument'. A member of the type, 'Id', does not have a corresponding column in the data reader with the same name. – Achu_L Dec 11 '18 at 06:50
1

Try to use ExecuteSqlCommand instead SqlQuery. Also check the types and existence of edi.SenderID, files.Id fields in the database – Maxim Saltanov Dec 11 '18 at 06:56
Note: you can't return values in your sql (var ediDocuments =) - it will shouldn't work because it is not SELECT sql query. so you should decide if you want to update rows of select some rows – Maxim Saltanov Dec 11 '18 at 07:01
can anyone guide my how to mention nullable datetime type in sql parameter , new SqlParameter { ParameterName = "startDate", DbType = System.Data.DbType.DateTime, Value = startDate }, – Achu_L Dec 11 '18 at 08:42
I hope this article should helps - https://stackoverflow.com/questions/7497786/passing-null-as-sqlparameter-datetime-value – Maxim Saltanov Dec 11 '18 at 08:53

score 0 · Answer 2 · answered Dec 11 '18 at 06:21

0

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

  }

But this approach has places to possible SQL-injections.

answered Dec 11 '18 at 06:21

Denis Derkach

381
3
7

It not throwing exception but still not gets updated in database – Achu_L Dec 11 '18 at 06:33
@Achu_L,try `context.Database.ExecuteSqlCommand(commandText);` – Denis Derkach Dec 11 '18 at 06:46

score 0 · Answer 3 · answered Dec 11 '18 at 07:10

First thing you should know is DbSet.SqlQuery() method primarily used to execute SELECT statement which returns result set based on corresponding entity type, i.e. DbSet name. If you want to execute action queries like UPDATE command, you should use Database.ExecuteSqlCommand() with SqlParameter[] array for parameters instead, as provided in example below:

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

Note:

The second parameter of both DbSet.SqlQuery() and Database.ExecuteSqlCommand() methods uses object[] array, hence you need to pass parameters into an array instead of using them directly.

Reference:

Execute Raw SQL Queries Using Entity Framework

To update the field in database using Raw SQL?

3 Answers3