Update yields unwanted join

[sshukurov]

I have the following code:

DbContext.Set<Configuration>().Where(c => c.Id == configDto.Id).Update(c => new Configuration {
	Name = configDto.Name,
	Description = configDto.Description,
	LastModificationDate = configDto.LastModificationDate,
	Group = configDto.Group,
	Enabled = configDto.Enabled,
	DisableDate = configDto.DisableDate
});

which generates the following SQL query:

UPDATE A 
SET A.[Name] = @zzz_BatchUpdate_0,
A.[Description] = @zzz_BatchUpdate_1,
A.[LastModificationDate] = @zzz_BatchUpdate_2,
A.[Group] = @zzz_BatchUpdate_3,
A.[Enabled] = @zzz_BatchUpdate_4,
A.[DisableDate] = @zzz_BatchUpdate_5
FROM [dbo].[Configuration] AS A
INNER JOIN ( SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Description] AS [Description], 
    [Extent1].[LastModificationDate] AS [LastModificationDate],
	[Extent1].[Group] AS [Group], 
	[Extent1].[Enabled] AS [Enabled], 
	[Extent1].[DisableDate] AS [DisableDate]
    FROM [dbo].[Configuration] AS [Extent1]
    WHERE [Extent1].[Id] = @p__linq__0
           ) AS B ON A.[Id] = B.[Id]

This query has the following execution plan:

Without the join I get the following execution plan:

My table has the following schema:

Clearly, the join is unwanted, but why must the library generate it?

[JonathanMagnan]

Hello @sshukurov ,

The SQL has been generated this way because it was easier when I created the library to do it without having to check if I could make a simple update statement or not.

We simply take the SQL that is normally generated by Entity Framework and plug in on the UPDATE STATEMENT

For optimization reason, we will for sure try to optimize it eventually.

However not on short-term plan Roadmap

We currently prioritize to allow .NET Fiddle to be compatible with Entity Framework and then create a dynamic provider library to make it easier to do features like the one you are asking.

Let me know if that answer correctly to your question

Best Regards,

Jonathan

[rgroenewoudt]

This is also getting more important for us, sometimes we are running into the update query having a deadlock with itself. If executed without the join, there is no issue.

[dansoper]

Just a note to say that the INNER JOIN has caused me issues too, and for now I'm having to abandon EF Plus and just run the SQL query directly.

This is because the table I'm trying to update contains a varbinary column, and because the INNER JOIN SELECTS all the columns in the table, I've had timeout issues.

[xumix]

Any news on this topic? Unnecessary join or select with all columns may kill all the benefits of the library when a table is massive.