Fix/use simple update statements if possible 312

[StasPerekrestov]

Fixes #312

The goal of the PR is to avoid additional joins if a IQueryable<T> is pretty simple, e.g. it references only a target query and doesn't use joins, unions etc.

It allows to simplify a query from the issue to something like

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 ( 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 A

The PR only works for SqlServer syntax.

Also batchDeletes look like

exec sp_executesql N'
DECLARE @rowAffected INT
DECLARE @totalRowAffected INT

SET @totalRowAffected = 0

WHILE @rowAffected IS NULL
    OR @rowAffected > 0
    BEGIN
        DELETE TOP (1000)
        FROM    A 
        FROM    ( SELECT 
    [Extent1].[Id] AS [Id]
    FROM [dbo].[TableToUpdate] AS [Extent1]
    WHERE ([Extent1].[Id] >= @p__linq__0) AND ([Extent1].[Id] <= @p__linq__1)
                           ) A
        SET @rowAffected = @@ROWCOUNT
        SET @totalRowAffected = @totalRowAffected + @rowAffected
    END

SELECT  @totalRowAffected
',N'@p__linq__0 int,@p__linq__1 int',@p__linq__0=34,@p__linq__1=34`

[JonathanMagnan]

Hello @StasPerekrestov , @smad2005 ,

Thank you for the pull, one of my employee will review it probably on Thursday.

Best Regards,

Jonathan

[StasPerekrestov]

Hey. Any updates on the subject? BTW, my team's been using these changes in PROD for two weeks already.

[JonathanMagnan]

Hello @StasPerekrestov ,

Unfortunately, we have been quite busy recently. We are trying to find some time for free libraries but it's sometimes hard.

I don't think I will find time during the weekend but it should be very soon.

Best Regards,

Jonathan

[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.