Selecting multiple columns for updating in Linq

Question

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 1

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 }

Answer 2

You must include the primary key in the anonymous type:

var models = await context.Products
    .Where(p => p.SellerId == sellerId)
    .Select(p => new
    {
        Id = p.Id, // primary key
        Price = p.Price,
        IsAvailable = p.IsAvailable
    })
    .ToListAsync();

Then, when you need to save the data back to the database, you need to create entities with the same primary key and attach them to the context.

foreach (var x in models)
{
    var product = new Product
    {
        Id = x.Id,
        Price = newPrice, // get new price somehow
        IsAvailable = false // get new availability somehow
    };
    context.Attach(product);

    var entry = context.Entry(product);
    entry.Property("Price").IsModified = true;
    entry.Property("IsAvailable").IsModified = true;
}

await context.SaveChangesAsync();

More info about Attach

Answer 3

EF doesn't fit well for batch operations.

It works with objects, not tables, records or fields. If you want to update or delete object(s), you need to read it(them) first. This allows EF change tracker to track changes in field values or the whole object state, and generate appropriate SQL.

For batch operations consider using raw SQL queries, light-weight libraries like Dapper, or third-party packages like Entity Framework Plus.

Answer 4

You can use the ExecuteSqlCommandAsync Command to write the query which will be executed in your SQL Server.

await dbContext
.Database
.ExecuteSqlCommandAsync("UPDATE Products SET Price = {0}, IsAvailable = {1} WHERE SelleriId = {2}", new object[] { priceValue, isAvailableValue, sellerId });