Having problems getting UpdateFromQuery

[PMJMendes]

Description

I'm trying to translate a reasonably dense SQL Server update query to Linq, using UpdateFromQuery, but I seem to be hitting a wall.

Here's the original query:

UPDATE receita
                        SET taco_uid = ( SELECT TOP 1 taco_uid from tabela_comparticipacao
                                         WHERE flag_elect = 1 AND ec_uid IN (
                                               SELECT ec_uid from tabela_comparticipacao WHERE TaCo_UID = Receita.taco_uid
                                               )
                                        )
                        FROM Receita
                        INNER JOIN tabela_comparticipacao tc on tc.taco_uid = receita.taco_uid
                        WHERE Receita.v_uid = { receitaEletronica.VendaUid} AND 
                              Receita.numeroreceita = '{ receitaEletronica.NumeroReceita }' AND
                              tc.ec_uid in ( { string.Join(",", entidades) })

As you can see, the query is reasonably poorly written. It's part of a legacy codebase that's being steadily migrated to C#, using Linq as much as possible.

This is our first attempt at actually migrating an update query. Up to now, we've been pulling the entities to memory and saving changes one by one.

I tried two different approaches.

Attempt 1:

using (var context = contextFactory())
{
    var modified = context.Receita.
        Where(r => r.V_UID == receitaEletronica.VendaUid && r.numeroreceita == receitaEletronica.NumeroReceita).
        Join(context.Tabela_Comparticipacao.
            Where(t => entidades.Contains(t.ec_uid)).
            Join(context.Tabela_Comparticipacao.Where(t => t.flag_elect == 1),
                source => source.ec_uid,
                dest => dest.ec_uid,
                (source, dest) => new { Source = source, Dest = dest }),
            r => r.TaCo_UID,
            t => t.Source.TaCo_UID,
            (r, t) => new { Receita = r, NovoTaCo = t.Dest.TaCo_UID }).
        UpdateFromQuery(r => new Receita { TaCo_UID = r.NovoTaCo });
}

Attempt 2:

using (var context = contextFactory())
{
    context.Receita.
        Where(r => r.V_UID == receitaEletronica.VendaUid && r.numeroreceita == receitaEletronica.NumeroReceita).
        UpdateFromQuery(r => new Receita { TaCo_UID = context.Tabela_Comparticipacao.
            Where(t => t.TaCo_UID == r.TaCo_UID && entidades.Contains(t.ec_uid)).
            Join(context.Tabela_Comparticipacao.Where(t => t.flag_elect == 1),
                source => source.ec_uid,
                dest => dest.ec_uid,
                (source, dest) => (int?)dest.TaCo_UID).
            DefaultIfEmpty(r.TaCo_UID).FirstOrDefault()
        });
}

Exception

Attempt 1 threw the following exception:

Message: 
    Test method winphar.services.receitas.ReceitasDAOTest.TestAtualizarReceitaElectronicaTaCoUidEntidadesCentralizadas threw exception: 
    System.Exception: Oops! Invalid update expression, the body is not a NewExpression. Please refer to the documentation to get examples about how to use this feature.
  Stack Trace: 
    DbContextExtensions.[](IQueryable`1 , Expression`1 , Action`1 , Boolean )
    DbContextExtensions.UpdateFromQuery[TEntity](IQueryable`1 query, Expression`1 updateExpression, Action`1 bulkOperationFactory)
    DbContextExtensions.UpdateFromQuery[TEntity](IQueryable`1 query, Expression`1 updateExpression)
    ReceitasDAO.AtualizarReceitaElectronicaTaCoUidEntidadesCentralizadas(ReceitaEletronica receitaEletronica, List`1 entidades) line 114
    ReceitasDAOTest.TestAtualizarReceitaElectronicaTaCoUidEntidadesCentralizadas() line 267

Attempt 2 threw the following exception:

Message: 
    Test method winphar.services.receitas.ReceitasDAOTest.TestAtualizarReceitaElectronicaTaCoUidEntidadesCentralizadas threw exception: 
    System.Data.SqlClient.SqlException: The multi-part identifier "Project3.C1" could not be bound.
  Stack Trace: 
    SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
    SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
    SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
    SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
    SqlCommand.ExecuteNonQuery()
    .(DbCommand , BulkOperation , Int32 )
    .(DbCommand , BulkOperation , Int32 )
    .( )
    .Execute(List`1 actions)
    .(List`1 )
    BulkOperation.()
    BulkOperation`1.UpdateFromQuery(IQueryable originalQuery, IQueryable query, Expression`1 updateExpression)
    DbContextExtensions.[](IQueryable`1 , Expression`1 , Action`1 , Boolean )
    DbContextExtensions.UpdateFromQuery[TEntity](IQueryable`1 query, Expression`1 updateExpression, Action`1 bulkOperationFactory)
    DbContextExtensions.UpdateFromQuery[TEntity](IQueryable`1 query, Expression`1 updateExpression)
    ReceitasDAO.AtualizarReceitaElectronicaTaCoUidEntidadesCentralizadas(ReceitaEletronica receitaEletronica, List`1 entidades) line 114
    ReceitasDAOTest.TestAtualizarReceitaElectronicaTaCoUidEntidadesCentralizadas() line 299

Further technical details

• EF version: 6.1.3
• EF Extensions version: 5.1.19
• Database Provider: SQL Server 14 Developer 64-bit LocalDB
• .NET Framework 4.0

[JonathanMagnan]

Hello @PMJMendes ,

Could you try with the following code:

UpdateFromQuery(r => new Receita { TaCo_UID = r.NovoTaCo }, options => options.InternalIsEntityFrameworkPlus = true);

When enabling InternalIsEntityFrameworkPlus, a more recent logic (the one from EF Plus) is used which supports some join/ijnclude statement like you are trying to do.

So there is a good chance that your scenario can already work.

If that still fail, please provide us a full runnable scenario so that we could be sure to fix the same error as your.

Best Regards,

Jon

[PMJMendes]

Thank you for your prompt reply. Your suggestion generates pretty much the same exceptions, although the stack trace for option 2 seems to have changed a bit.

Looking at the signature for UpdateFromQuery, I think trying to get option 1 to work might be a handful, as I don't think it likes to work with queryables that aren't of a pure entity.

Option 2, however, looks like it might be easy to fix. Looking at the intercepted SQL, it seems like there's a bit of code that should output the inner select, but is failing to do so. Here it is with the original default internals:

MERGE INTO [dbo].[Consumers] AS DestinationTable
USING
(
SELECT 
    [Extent1].[ConsumerID] AS [ConsumerID]
    FROM [dbo].[Consumers] AS [Extent1]
    WHERE 2 = [Extent1].[ConsumerID]
) AS StagingTable
ON StagingTable.[ConsumerID] = DestinationTable.[ConsumerID]
WHEN MATCHED THEN
    UPDATE
    SET     [LookupFK] =  
    CASE WHEN ([Project6].[C1] IS NULL) THEN 0 ELSE [Project6].[C2] END

And here it is with the EFPlus internals:

UPDATE A 
SET A.[LookupFK] =  
    CASE WHEN ([Project6].[C1] IS NULL) THEN 0 ELSE [Project6].[C2] END 
FROM [dbo].[Consumers] AS A
INNER JOIN ( SELECT 
    [Extent1].[ConsumerID] AS [ConsumerID], 
    [Extent1].[LookupFK] AS [LookupFK], 
    [Extent1].[Integrity] AS [Integrity]
    FROM [dbo].[Consumers] AS [Extent1]
    WHERE 2 = [Extent1].[ConsumerID]
           ) AS B ON A.[ConsumerID] = B.[ConsumerID]

In both cases, you can see that the definition for Project6 is missing.

[PMJMendes]

I couldn't get the .net fiddle signup to work, but if you copy-paste the following code, it'll reproduce the issue:

// @nuget: EntityFramework
// @nuget: Z.EntityFramework.Extensions
// @nuget: Z.Expressions.Eval

using System;
using System.Data.Entity;
using System.Linq;
using System.Collections.Generic;
using Z.Expressions;
					
public class Program
{
	static Program()
	{
		EvalManager.DefaultContext.RegisterExtensionMethod(typeof(DbContextExtensions));
		EvalManager.DefaultContext.RegisterType(typeof(Lookup));
		EvalManager.DefaultContext.RegisterType(typeof(Consumer));
	}
	
	public static void Main()
	{
		InitData();
		
		var classes = new List<int> { 100, 200 };

		// Option 1
		try
		{
			using (var context = new EntityContext())
			{
				context.Consumers.
					Where(c => c.ConsumerID == 2).
					Join(context.Lookups.
						 Where(l => classes.Contains(l.LookupClass)).
						 Join(context.Lookups.Where(l => l.IsActive),
							  source => source.LookupClass,
							  dest => dest.LookupClass,
							  (source, dest) => new { Source = source, Dest = dest }),
						 c => c.LookupFK,
						 l => l.Source.LookupID,
						 (c, l) => new { Consumer = c, NewClass = l.Dest.LookupID }).
					UpdateFromQuery(c => new Consumer { LookupFK = c.NewClass }, options => options.InternalIsEntityFrameworkPlus = true);
			}
			
			Console.WriteLine("Option 1 worked.");
		}
		catch (Exception e)
		{
			Console.WriteLine("Option 1 failed:");
			Console.WriteLine(e.Message);
			Console.WriteLine(e.StackTrace);
		}

		// Option 2
		try
		{
			using (var context = new EntityContext())
			{
				context.Consumers.
					Where(c => c.ConsumerID == 2).
					UpdateFromQuery(c => new Consumer { LookupFK = context.Lookups.
						 Where(l => classes.Contains(l.LookupClass) && l.LookupID == c.LookupFK).
						 Join(context.Lookups.Where(l => l.IsActive),
							  source => source.LookupClass,
							  dest => dest.LookupClass,
							  (source, dest) => dest.LookupID).
						DefaultIfEmpty(c.LookupFK).FirstOrDefault()
					}, options => options.InternalIsEntityFrameworkPlus = true);
			}
			
			Console.WriteLine("Option 2 worked.");
		}
		catch (Exception e)
		{
			Console.WriteLine("Option 2 failed:");
			Console.WriteLine(e.Message);
			Console.WriteLine(e.StackTrace);
		}
	}
	
	public static void InitData() 
	{
		var lookups = new List<Lookup>
		{
			new Lookup { LookupID = 1, LookupClass = 100, IsActive = false },
			new Lookup { LookupID = 2, LookupClass = 200, IsActive = true },
			new Lookup { LookupID = 3, LookupClass = 200, IsActive = true },
			new Lookup { LookupID = 4, LookupClass = 100, IsActive = true },
			new Lookup { LookupID = 5, LookupClass = 100, IsActive = true },
		};

		var consumers = new List<Consumer>
		{
			new Consumer { ConsumerID = 1, LookupFK = 3, Integrity = "solid" },
			new Consumer { ConsumerID = 2, LookupFK = 1, Integrity = "solid" },
			new Consumer { ConsumerID = 3, LookupFK = 5, Integrity = "solid" },
		};

		using (var context = new EntityContext())
		{
			context.BulkInsert(lookups, options => options.BatchSize = 100);
			context.BulkInsert(consumers, options => options.BatchSize = 100);
		}
	}

	public class EntityContext : DbContext
	{
		public EntityContext() : base(FiddleHelper.GetConnectionStringSqlServer()) { }
		
		public DbSet<Lookup> Lookups { get; set; }
		public DbSet<Consumer> Consumers { get; set; }
	}
	
	public class Lookup
	{
		public int LookupID { get; set; }
		public int LookupClass { get; set; }
		public bool IsActive { get; set; }
	}

	public class Consumer
	{
		public int ConsumerID { get; set; }
		public int LookupFK { get; set; }
		public string Integrity { get; set; }
	}
}

[JonathanMagnan]

Awesome thank you for the code.

The issue has been assigned to one of my developers. He will look at it.

[JonathanMagnan]

Hello @PMJMendes ,

Thank again for the code but unfortunately, we didn't succeed to support it.

There is some scenario that we support using the Include method, but we do not support yet your scenario which requires some join.

We might eventually look at it again in a few months but unfortunately for now, this scenario is not supported.

Best Regards,

Jon

[PMJMendes]

Hello,

That is unfortunate. Thank you for your time anyway.

Is there any alternate code to update a table based on data from other tables?

[JonathanMagnan]

Hello @PMJMendes ,

My developer just comes up with some ideas to try to support it, so I will give you an update next week.

Let hope that will be a positive one.

[PMJMendes]

Cool. Fingers crossed. :)

Thank you for the update.

[JonathanMagnan]

Hello @PMJMendes ,

Just to let you know that we currently have fix in review. I hope to be able to merge it early next week

The major problem for this kind of scenario is on our side to know which properties should be selected to generate the SQL through Entity Framework, so we trying to add a Selector options to handle complex scenarios that we cannot automatically solve on our side.

I will let you know more very soon.

[PMJMendes]

Thank you kindly. Looking forward to it! :)

[PMJMendes]

Hello, :)

Just wondering if you guys are making progress on this front.

Thank you for your continuing support. :)

[PMJMendes]

Hello, :)

Just wondering if this issue is stil alive... :)

As usual, thanks in advance for your time and attention.

[JonathanMagnan]

Hello @PMJMendes ,

Unfortunately, the change made by my developer improved the method but didn't succeed to fully support your scenario.

We are still working on it but unfortunately, too many requests open at this moment ;(

[PMJMendes]

Alright, thank you for the update. :) I'll keep looking in from time to time.