Selecting multiple columns for updating in Linq

I have a products table which contains thousands of products. I want to update only two columns (price, isAvailable) of this table. So is there is a way to select only those two columns from this table?

This is the code that I am using. But I don’t want to select all columns.

     var dbModels = await DbContext.Products
                    .Where(x => x.SellerId == sellerId)
                    .ToListAsync(); 

I have tried this

       var db = await DbContext.ProductSkuDetail
                .Where(x => x.SellerId == sellerId)
                .Select(y => new
                {
                    Price = y.Price,
                    IsAvailable = y.IsAvailable
                }).ToListAsync();

But this is read-only. I want to update those columns.

Answer

Yes, there is a way to specify exactly which columns you want.

No, you can’t use that method to update data.

When fetching data using entity framework DbSet<...>, there are two methods: fetch the complete row of the table, or only fetch certain properties of the row.

The first method is used, if you execute the query without using Select. If you do this, the data is copied to the DbContext.ChangeTracker.

Other methods like DbSet.Find and IQueryble.Include will also copy the fetched data to the ChangeTracker.

If you use Select to specify the data that you want to fetch, then the fetched data will not be copied into the ChangeTracker.

When you call DbContext.SaveChanges, the ChangeTracker is used to determine what items are changed or removed and thus need to be updated.

The ChangeTracker keeps the original fetched data, and a copy of it. You get the reference to the copy as the result of your changes. So whenever you change the values of properties of your reference to the copy, they are changed in the copy that is in the ChangeTracker.

When you call SaveChanges, the copy is compared to the original in the ChangeTracker, to detect which properties are changed.

To improve efficiency, if you don’t plan to update the fetched data, it is wise to make sure that the fetched data is not in the ChangeTracker.

When using entity framework to fetch data, always use Select and fetch only the properties that you actually plan to use. Only query without Select if you plan to change the fetched data.

Change = update properties, or remove the complete row. Also: only use Find and Include if you plan to update the fetched data.

You want to update the fetched row

Hence you have to fetch the complete row: don’t use Select, fetch the complete row.

If you want to fetch the item by primary key, consider to use DbSet.Find. This has the small optimization that if it is already in the ChangeTracker, then the data won’t be fetched again.

Consider to write SQL for this

Usually you don’t have to update thousands of items on a regular basis. However, if you have to do this often, consider to update using sql:

using (var dbContext = new MyDbContext(...))
{
    const string sqlText = @"Update products
        SET Price = @Price, IsAvailable = @IsAvailable....
        Where SellerId = @SellerId;";
    var parameters = new object[]
    {
        new SqlParameter("@SellerId", sellerId),
        new SqlParameter("@Price", newPrice),
        new SqlParameter("@IsAvailable", newAvailability),
    };
    dbContext.DataBase.ExecuteSqlCommand(sqlText, parameters);
}

(You’ll have to check the validity of the SQL command in my example. Since I use entity framework, my SQL is a bit rusty.)

By the way: although this method is very efficient, you’ll lose the advantages of entity framework: the decoupling of the actual database from the table structure: the names of your tables and columns seep through until this statement.

My advice would be only to use direct SQL for efficiency: if you have to update quite often. Your DbContext hides the internal layout of your database, so make this method part of your DbContext

public void UpdatePrice(int sellerId, bool IsAvailable, decimal newPrice)
{
    const string sqlText = ...
    var params = ...
    this.Database.ExecuteSqlCommand(sqlText, params);
}

Alas, you’ll have to call this once per update, there is no SQL command that will update thousands of items with different prices in one SQLcommand }