ExecuteUpdate is not working with Sqlite

[drjackson]

ExecuteUpdate is not working with Sqlite in unit tests. ExecuteDelete works, but ExecuteUpdate simply fails to update the specified fields even though it reports affecting a number of records.

Here is the logged output from EF for the command:

dbug: 1/26/2023 14:38:16.240 CoreEventId.QueryCompilationStarting[10111] (Microsoft.EntityFrameworkCore.Query) Compiling query expression: 'DbSet<Budget>() .Where(b => b.BudgetId == 1) .ExecuteUpdate(b => b.SetProperty( propertyExpression: b => b.Name, valueExpression: "Updated name"))' dbug: 1/26/2023 14:38:16.424 CoreEventId.QueryExecutionPlanned[10107] (Microsoft.EntityFrameworkCore.Query) Generated query execution expression: 'queryContext => RelationalShapedQueryCompilingExpressionVisitor.NonQueryResultAsync( relationalQueryContext: (RelationalQueryContext)queryContext, relationalCommandCache: RelationalCommandCache.QueryExpression( UPDATE Budget AS b SET b.Name = 'Updated name' SELECT 1 FROM BPMS.Budget AS b WHERE b.BudgetId == 1), contextType: ECS360.BPMS.Data.BpmsContext, commandSource: BulkUpdate, threadSafetyChecksEnabled: True)' dbug: 1/26/2023 14:38:16.447 RelationalEventId.CommandCreating[20103] (Microsoft.EntityFrameworkCore.Database.Command) Creating DbCommand for 'ExecuteNonQuery'. dbug: 1/26/2023 14:38:16.447 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) Created DbCommand for 'ExecuteNonQuery' (0ms). dbug: 1/26/2023 14:38:16.447 RelationalEventId.CommandInitialized[20106] (Microsoft.EntityFrameworkCore.Database.Command) Initialized DbCommand for 'ExecuteNonQuery' (0ms). dbug: 1/26/2023 14:38:16.448 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] UPDATE "Budget" AS "b" SET "Name" = 'Updated name' WHERE "b"."BudgetId" = 1 info: 1/26/2023 14:38:16.449 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] UPDATE "Budget" AS "b" SET "Name" = 'Updated name' WHERE "b"."BudgetId" = 1

Perhaps the lack of an alias for SET "Name" is the issue?

[drjackson]

Well, I think the generated SQL should work, but even though the log says the statement was executed, the row isn't actually updated. I'm using Microsoft.EntityFramework.Sqlite 7.0.2.

[ajcvickers]

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[drjackson]

If you follow this getting started tutorial: https://learn.microsoft.com/en-us/ef/core/get-started/overview/first-app?tabs=netcore-cli

But modify Program.cs to do the following:

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;

using var db = new BloggingContext();

// Create
Console.WriteLine("Inserting a new blog");
db.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
db.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
db.SaveChanges();

db.Blogs.ExecuteUpdate(b => b.SetProperty(p => p.Url, "https://www.testupdated.com"));

//the update will not be reflected here. 
var blogs = db.Blogs.ToList();

I realize now that it may just be the change tracker is not reflecting the ExecuteUpdate. When I call bpmsContext.ChangeTracker.Clear(), the updates are reflected. This isn't the same behavior as ExecuteDelete though. In that case, db.Blogs.ToList() will return the right records even without the call to ChangeTracker.Clear().

[ajcvickers]

@drjackson Queries using a DbSet are always executed against the database, so if rows have been deleted in the database, then the query results will reflect that. But any entity that is already tracked will not be re-created or updated based on the query results.

[drjackson]

I assume this is expected behavior then and so I'll close this issue. Thanks for your help.