Generating a sql query

[Mr0N]

There is the following code

  public void ChangeDescription()
  {
      int skip = 0;
      Temp[] response;
      int count = 0;
      do
      {
          response = dbContext.TempDbContext.
                                           .OrderBy(a => a.Id)
                                           .Skip(skip)
                                           .Take(1000)
                                           .ToArray();
          Console.WriteLine("Start");

          foreach (var item in response)
          {

              Console.WriteLine($"Desc:{count++}");
             
              item.DescriptionJoin = ChangeJsonTitleAndDescription(item.Description, item.UpdateDescriptionInfo, item.UpdateDescriptionLanguageInfo);
              ;
          }
          dbContext.SaveChanges();
          dbContext.ChangeTracker.Clear();
          skip += 1000;
      }
      while (response.Any());

  }

this code generates the following sql query

 UPDATE temp SET "DescriptionJoin" = @p1782
      WHERE "Id" = @p1783;
      UPDATE wikimedia SET "DescriptionJoin" = @p1784
      WHERE "Id" = @p1785;
      UPDATE wikimedia SET "DescriptionJoin" = @p1786
      WHERE "Id" = @p1787;
      UPDATE wikimedia SET "DescriptionJoin" = @p1788
      WHERE "Id" = @p1789;
      UPDATE wikimedia SET "DescriptionJoin" = @p1790
      WHERE "Id" = @p1791;
      UPDATE wikimedia SET "DescriptionJoin" = @p1792
      WHERE "Id" = @p1793;
      UPDATE wikimedia SET "DescriptionJoin" = @p1794
      WHERE "Id" = @p1795;

In these sql queries, minus what is here, the operation of filtering data by id is repeated, and it all does not work very well.

The following sql can be generated

UPDATE temp
SET "DescriptionJoin" = CASE 
    WHEN "Id" = @p1783 THEN @p1782
    WHEN "Id" = @p1785 THEN @p1784
    WHEN "Id" = @p1787 THEN @p1786
    WHEN "Id" = @p1789 THEN @p1788
    WHEN "Id" = @p1791 THEN @p1790
    WHEN "Id" = @p1793 THEN @p1792
    WHEN "Id" = @p1795 THEN @p1794
    ELSE "DescriptionJoin" 
END
WHERE "Id" IN (@p1783, @p1785, @p1787, @p1789, @p1791, @p1793, @p1795);

Such code should, in theory, run many times faster than generating many update requests.

[roji]

@Mr0N this is supported by EF Core via ExecuteUpdateAsync - have you read the docs?

[roji]

Otherwise, if you're referring to optimizing SaveChanges itself to use a single UPDATE instead of many, then that's possible - but note that SaveChanges can be used to persist any number of INSERT, UPDATE and DELETE operations - on possibly different tables - in a single unit of work. Having SaveChanges attempt to find optimizable groups of updates within the incoming unit-of-work is possible, but would mean additional detection work which would also not be trivial. We currently do something like this for bulk insert (multiple inserts into the same table), but not for update or delete.

[Mr0N]

@Mr0N this is supported by EF Core via ExecuteUpdateAsync - have you read the docs?

Well, ExecuteUpdateAsync is just an Update query, but if you need to generate some IL (C#) information and write it to the database, it's not suitable.

[mseada94]

It might be faster for single column update with few rows, I am not sure if this will be efficient with many columns and many rows.
It's a good idea for optimization but should not be applied before testing the best threshold for the number of rows and columns to limit the command complexity, how and when to group update statement.
Please note also, each update statement might be used to update different set of columns and combine these queries might lead to unnecessary CASE WHEN computations.

[roji]

@Mr0N what kind of IL (C#) information are you referring to, which EF would generate and write to the database?

[Mr0N]

@Mr0N what kind of IL (C#) information are you referring to, which EF would generate and write to the database?

yes

[roji]

Opened #35401 to track optimizing SaveChanges for multiple identical updates, similar to how #27550 would optimize deleting.