How to perform UPDATE query with multiple conditions?

[verdysh]

Hi!
Microsoft.EntityFrameworkCore.SqlServer, 6.0.5
I have following entity:

public class ContentFilter
{
	public int Id { get; set; }
	public int TenantId { get; set; }
	public string Name { get; set; }
}

I need to update it. I want to perform the next UPDATE query:
UPDATE ContentFilters SET Name=@name WHERE Id=@id and TenantId=@tenantId

If I write following code

var entity = new ContentFilter()
{
	Id = 1,
	TenantId = 1
};
entity.Name = "New name";

db.ContentFilters.Attach(entity);
var entry = db.Entry(entity);
entry.Property(r => r.Name).IsModified = true;

await db.SaveChangesAsync();

I get the next UPDATE query:
UPDATE [ContentFilters] SET [Name] = @p0 WHERE [Id] = @p1;

But I want EF to add another condition and tenantId = @p2 into WHERE clause.

Is it possible?

[roji]

@verdysh SaveChanges, always sends updates based on the primary key. If Id is your primary key above, then it already uniquely identifies the row (and the additional TenantId WHERE clause wouldn't do anything). Otherwise, you may be looking to have a composite primary key consisting of both the Id and the TenantId. If you configure your entity type that way, SaveChanges should automatically reference both in the WHERE clause.

[verdysh]

I have a request sent from the client. Client sends following object:

public class ContentFilter
{
	public int Id { get; set; }
	public string Name { get; set; }
}

Client's object doesn't have TenantId property, because TenantId set by application based on some authentication token.
Having this object from the client I need to update appropriate row in my database.

To update the entity I have to check if ContentFilter row in database belongs to the same Tenant as a user making request. In order to do that I have to make a database call with a query which answers to a question if there is a row with accepted Id and set TenantId. Only in a case when such row exists, I'm allowed to make an UPDATE.

If I could say UPDATE ContentFilters SET Name=@name WHERE Id=@id and TenantId=@tenantId I wouldn't need to check if there is appropriate row, therefore I would get rid of additional query. The condition and TenantId=@tenantId ensures the correct row will be updated if such exists. If such row doesn't exist, it's a client issue, I don't care about it.

[roji]

In that case, you can define TenantId as an application-managed concurrency token. This means that it gets included in SaveChanges-generated WHERE clauses, and if the UPDATE fails to find a matching row, an exception is thrown (which you can catch and ignore).

[verdysh]

It works, thanks! But 🙂 ... I have different cases where I need to update the entity in different ways.

The case I described above is "unsafe" because data comes from the client and I must check if the user belongs the same tenant as the row.

Also there are other cases where I only have Id of the entity and I don't have tenantId there because things happen out of user session. In such cases I need to update entity without additional condition by tenant, only where Id = @id.

Ideally, I want to be able to say, not for all cases, but for a specific case: EF, please add an additional condition here for the UPDATE statement. Is it possible?

[ajcvickers]

@verdysh That should be covered by #10443.

[roji]

Also, just to make sure the picture is complete - you can very precisely control your WHERE clause by using ExecuteUpdate. This does bypass change tracking entirely, so you may need to do more manual management, depending on what exactly you're doing.