WHERE IN Raw SQL Query used with ExecuteSqlCommand not working for multiple records

Question

I'm trying to make efficient use of a UPDATE query while updating multiple records (instead of looping for every Id).

I have this simple method:

    public static async Task EfficientlyUpdateSentTimeforSomeTicketsAsync(List<int> Ids)
    {
        var parameters = Ids.Select((id, index) => new SqlParameter(string.Format("@p{0}", index), id));
        var parameterNames = string.Join(", ", parameters.Select(p => p.ParameterName));
        var query = string.Format("Update [TicketQueueTable] Set SentDatetime = {1} WHERE TicketQueueID IN ({0})", parameterNames, DateTime.Now);

        try
        {
            using (var db = GetDbContext())
            {
                var affectedRows = await db.Database.ExecuteSqlCommandAsync(query, parameters.ToArray());
            }
        }
        catch (Exception ex)
        {
            await LogErrorAsync(ex);
            throw ex;
        }
    }

I took help from this SO question: https://stackoverflow.com/a/43969812/8644294 but I'm unable to make this work. It hits me with exception that says:

Incorrect syntax near '2'

For simplicity, the Ids that I'm sending for now is new List<int>() { 1, 2 }.

Thank you for your time.

You should create a parameter for the date you want to set as well. But the issue is that you format that date into the query without putting single quotes around it. — juharr, Mar 05 '20 at 19:27
Hi @juharr , Sorry I didn't get it. Can you please elaborate? And when I put quotes around the Date, I'm getting exception: `No mapping exists from object type System.Data.SqlClient.SqlParameter[] to a known managed provider native type.` — Ash K, Mar 05 '20 at 19:35
Can you please show me the exact message that exception is throwing — Malik Haseeb, Mar 05 '20 at 19:38
you can't use parameters as inputs to elements of an in statement. an in statement requires constants, instead use a values statement and construct a table which you link to — Hogan, Mar 05 '20 at 19:47
Try: `var query = string.Format("Update [TicketQueueTable] Set SentDatetime = NOW() WHERE TicketQueueID IN ({0})", parameterNames);` always best to use the datetime on the sql server rather than the web server, especially once you start running more than one webserver. — Robert McKee, Mar 05 '20 at 20:13
I mean the SQL should be `SentDatetime = @SendDatetime` and you should create a parameter for it `new SqlParameter("@SendDatetime", DateTime.Now)` — juharr, Mar 05 '20 at 20:54
@Hogan You can do stuff like `Blah in (@param1, @parm2)` The down side is if you hit the parameter limit. — juharr, Mar 05 '20 at 21:01

score 3 · Accepted Answer · answered Mar 05 '20 at 20:41

The string you are creating looks like this

Update [TicketQueueTable] Set SentDatetime = 3/6/2020 2:30:18 AM WHERE TicketQueueID IN (@p0, @p1)

If you try to execute that command using a sql tool like SQL Server Management Studio, you will get this error.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '2'.

Plus the 2 in the 2:30:18 AM is underlined red.

You have a couple of options:

quote the date time

var query = string.Format("Update [TicketQueueTable] Set SentDatetime = '{1}' WHERE TicketQueueID IN ({0})", parameterNames, DateTime.Now);

use a sql function to get the current time of the sql server

var query = string.Format("Update [TicketQueueTable] Set SentDatetime = GETDATE() WHERE TicketQueueID IN ({0})", parameterNames, DateTime.Now); // GETDATE() for MSSQL NOW() for MySQL

pass the date time in as a parameter

public static async Task EfficientlyUpdateSentTimeforSomeTicketsAsync(List<int> Ids)
{
    var parameters = Ids.Select((id, index) => new SqlParameter(string.Format("@p{0}", index), id)).ToList();
    var parameterNames = string.Join(", ", parameters.Select(p => p.ParameterName));
    var query = string.Format("Update [TicketQueueTable] Set SentDatetime = @p{1} WHERE TicketQueueID IN ({0})", parameterNames, parameters.Count());

    parameters.Add(new SqlParameter($"@p{parameters.Count()}", DateTime.Now));
    try
    {
        using (var db = GetDbContext())
        {
            var affectedRows = await db.Database.ExecuteSqlCommandAsync(query, parameters.ToArray());
        }
    }
    catch (Exception ex)
    {
        await LogErrorAsync(ex);
        throw ex;
    }
}

Thank you for this great detailed answer! – Ash K Mar 05 '20 at 21:02 — Ash K, Mar 05 '20 at 21:02

Hogan · Answer 2 · 2020-03-05T21:06:58.840

Something like this: (I didn't test, might have a typo)

var parameterNames = string.Join(", ", Ids.Select(p => "("+ x.id.ToString()+")"));
var query = string.Format(@"Update TicketQueueTable 
                              Set SentDatetime = '{1}'
                             FROM TicketQueueTable
                             JOIN (
                               VALUES
                                {0}
                             ) AS L(ID) ON TicketQueueTable.Ticket.TicketQueueID = L.ID;", parameterNames, DateTime.Now)

If you want to use a parameter (which you should) then make it a table parameter and have the list of values in the table and join to it.

WHERE IN Raw SQL Query used with ExecuteSqlCommand not working for multiple records

2 Answers2