Update Statements

Using dbExpression, you can compose and execute direct updates against the target database. Unlike some ORM frameworks, it's not standard or required to retrieve data in order to execute an update.

In addition to Execute, Update includes ExecuteAsync to asynchronously update data.

To update a field value, use the Set method of the field expression. The following QueryExpression issues an update to the Person table where Person.Id is equal to the literal value 1 and sets that person's credit limit to the literal value 25,000.

int affected = db.Update(dbo.Person.CreditLimit.Set(25_000))
    .From(dbo.Person)
    .Where(dbo.Person.Id == 1)
    .Execute();

Execution of an update query returns the affected row count.

It's also possible to perform arithmetic within the Set method. For example, if you needed to increase the list price of products in a specific category by n%, the adjustment is accomplished (without data retrieval) using server side arithmetic.

int affected = db.Update(
        dbo.Product.ListPrice.Set(dbo.Product.ListPrice * 1.1)
    )
    .From(dbo.Product)
    .Where(dbo.Product.ProductCategoryType == ProductCategoryType.Books)
    .Execute();

Let's look at another couple of examples. The following uses the dbex.Null helper method (we'll cover it in a bit) to set Line2 of an Address to null.

int affected = db.Update(dbo.Address.Line2.Set(dbex.Null))
    .From(dbo.Address)
    .Where(dbo.Address.Id == 7)
    .Execute();

And of course, you can update multiple fields at once:

int affected = db.Update(
        dbo.Person.FirstName.Set("Jane"), 
        dbo.Person.LastName.Set("Smith")
    )
    .From(dbo.Person)
    .Where(dbo.Person.Id == 12)
    .Execute();

And one more example with a bunch of stuff (inner join, derived table, aliases, function, and group by) to update a person's credit limit to the max total amount they've made on a single purchase:

int affectedCount = db.Update(dbo.Person.CreditLimit.Set(("newCreditLimit", "creditLimit")))
    .From(dbo.Person)
    .InnerJoin(
        db.SelectMany(
                dbo.Purchase.PersonId,
                db.fx.Max(dbo.Purchase.TotalPurchaseAmount).As("creditLimit")
            )
            .From(dbo.Purchase)
            .GroupBy(dbo.Purchase.PersonId)
    ).As("newCreditLimit").On(dbo.Person.Id == ("newCreditLimit", "PersonId"))
    .Execute();