Problems run - Entity Framework Extensions - UpdateFromQuery - Update From Joined Table

[JaimeCastellanosM]

Description

I was verifing this Example and I tried to run but have problems.

Entity Framework Extensions - UpdateFromQuery - Update From Joined Table by Michel Zehnder

I would like to know how can to use the UpdateFromQuery from joined Tables.

Exception

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message:
Stack trace:

Fiddle or Project (Optional)

If you are able,

Provide a Fiddle that reproduces the issue: https://dotnetfiddle.net/jQ4moW

Or provide a project/solution that we can run to reproduce the issue.

• Make sure the project compile
• Make sure to provide only the code that is required to reproduce the issue, not the whole project
• You can send private code here: [email protected]

Otherwise, make sure to include as much information as possible to help our team to reproduce the issue.

Note: More information you provide, faster we can implement a solution.

Further technical details

• EF version: [EF Core v7.0.13]
• EF Extensions version: [EFE Core v7.100.0.0]
• Database Server version: [SQL Server 2022]
• Database Provider version (NuGet): [Microsoft.Data.SqlClient v5.1.2]

[JonathanMagnan]

Hello @JaimeCastellanosM ,

See the following online example I created from your example: https://dotnetfiddle.net/oT5OlX

var joined = from o in context.Orders 
			 join c in context.Customers on o.SecondaryID equals c.SecondaryID 
			 select new Order { OrderID = o.OrderID, CustomerID = c.CustomerID };

joined.UpdateFromQuery(x => new Order { CustomerID = x.CustomerID }, options => {
	options.Executing = command => {
		sb.AppendLine(command.CommandText);
	};
});

I had to change some parts of the code as you mixed a lot of EF6 code with EF Core code.

Let me know if that correctly answers your question.

Best Regards,

Jon

[JaimeCastellanosM]

Hello @JonathanMagnan,

Thank you for your answer! I tried changing my code like this example, But now I have the next error.

this is the query generated in QueryUpdateFromQuery:

SELECT [e].[c10092_rowid] AS [Rowid], [e0].[c10091_initial_balance] AS [DebitValue] FROM [e10092_summary_by_book_period] AS [e] INNER JOIN [e10091_summary_by_book] AS [e0] ON [e].[c10092_rowid_summary_by_book] = [e0].[c10091_rowid] WHERE [e].[c10092_debit_value] > 100.0

The unique differences is that in my query EF put the nickname in the columns (As Rowid), but I dont know if this is a problem.

Thank you for your help!

[JonathanMagnan]

Hello @JaimeCastellanosM ,

Indeed, it doesn't work if the column name is different. I tried to find a solution but didn't find one either.

One way to fix it since you already access the command is doing a command.CommandText.Replace("[e].[c10092_rowid] AS [Rowid]", "[e].[c10092_rowid]"), but it is surely not something I would like to have to do in my code.

I asked my developer to look at it and see if there is something we can do.

Best Regards,

Jon

[LeinerViloria]

Hello @JonathanMagnan, I'm Jaime's co-worker.

The partial solution works, we're gonna use it while your team try to fix it, this is our example:

Those are the generated queries:

[LeinerViloria]

Hello @JonathanMagnan, the method MergeFromQuery could be included in ur roadmap?

[JonathanMagnan]

Hello @LeinerViloria ,

Unfortunately, adding MergeFromQuery is not possible in the short term, as we currently have too many tasks to complete first.

However, I'm opening a reminder on our side to look at it in 1 month to evaluate at least the time and effort, and I will give you an update.

Best Regards,

Jon

[LeinerViloria]

Hi @JonathanMagnan, if u need help, u can contact us, hehehe, thank u @JonathanMagnan, we'll be attentive

[LeinerViloria]

Hi @JonathanMagnan, we found a weird query maked by UpdatedFromQuery for Postgres, it's through INNER JOIN clause.

This is the sql server query generated by UpdateFromQuery (edited with .replace):

---

This is the postgres query generated by UpdateFromQuery (without Alias), it doesn't work:

---

This is the edited postgres query, it works:

---

We had to do this code:

[JonathanMagnan]

Hello @LeinerViloria ,

Indeed, you are currently overpassing the limit of our library. The way you are currently using it was not initially expected.

We perform an EXISTS statement to ensure the row will be updated only once. Some INNER JOIN otherwise could make it join multiple times, I believe.

I guess we could eventually add a new option as we control the part of this template.

Best Regards,

Jon