Add whereClause to UpdateList

[marcelagraz]

It'll be nice if we could do something like:

public async Task<bool> UpdateListAsync<T>(IEnumerable<string> columnsToUpdate, T values, string whereClause) where T : class

[dallasbeek]

Can you provide the use case for this request?

[marcelagraz]

Hi @dallasbeek , thanks in advance for the fast response

I need for example to update several items from a table, and I need to update just the ones specified in a where clause and only specific columns, I saw that you can do a delete with a where clause and an update with specific column but don't see the option for update specific columns for several items filtered by a where clause

[dallasbeek]

I'd probably filter out the items using Linq.

db.UpdateList(lst.Where(item => item.CountryCode == "US"), new[] { "LastName" });

[marcelagraz]

The thing here is that I don't have a list of items, I would have to access database one more time in order to query the list and it could be very expensive to load all items from database.

[dallasbeek]

I'm going to assume you are setting all the rows to the same value? I'd suggest just wring the query and using Execute

_repo.Execute("update table set columna = @columna, columnb = @columnb where columnid = @columnid" new { columna = 3, columnb "wow" columnid = "where" });

[marcelagraz]

Sorry, I have an error in the post, I'm currently doing something like:

public async Task<bool> UpdateAllLineStatus(int deliveryInternalId, int lineStatus)
{
    var wtfDeliveryOrderDtl = new WtfDeliveryOrderDtl
    {
        DeliveryInternalId = deliveryInternalId,
        LineStatus = lineStatus,
        LastUpdatedDttm = _dateTimeService.Now
    };

    var sql = @"
        UPDATE 
            wtf_delivery_orders_dtl
        SET 
            line_status = @LineStatus,
            last_updated_dttm = @LastUpdatedDttm
        WHERE
            delivery_internal_id = @DeliveryInternalId
    ;";

    var affectedRowsNumber = _connection.ExecuteAsync(sql, wtfDeliveryOrderDtl);

    return affectedRowsNumber > 0;
}

And I would like to do something like:

public async Task<bool> UpdateAllLineStatus(int deliveryInternalId, int lineStatus)
{
    var columnsToUpdate = new List<string>
    {
        nameof(WtfDeliveryDtlOrder.LineStatus),
        nameof(WtfDeliveryDtlOrder.LastUpdatedDttm)
    };

    var values = new WtfDeliveryOrderDtl
    {
        LineStatus = lineStatus,
        LastUpdatedDttm = _dateTimeService.Now
    };

    var whereClause = $"delivery_internal_id = {DeliveryInternalId}";

    return UpdateListAsync(columnsToUpdate, values, whereClause);
}