search

zzzprojects / EntityFramework-Plus

Entity Framework Plus extends your DbContext with must-haves features: Include Filter, Auditing, Caching, Query Future, Batch Delete, Batch Update, and more
https://entityframework-plus.net/
MIT License
2.25k stars 318 forks source link

Batch Update with Join #447

Open fugaku opened 5 years ago

fugaku commented 5 years ago

Does Entity Framework Plus support Batch Update with Join ?

So for example a SQL Query like 

UPDATE a
SET a.Status = 'A'
FROM [User] a
JOIN [UserGroup] b on a.GroupId = b.Id
WHERE b.Code = 'Staff'
JonathanMagnan commented 5 years ago

Hello @fugaku ,

Some LINQ that require joins are supported.

The best way to find out if your query is supported is to try it.

If you have an error, just provide us a project sample that contains the error and we will look at it.

Best Regards,

Jonathan

Performance Libraries context.BulkInsert(list, options => options.BatchSize = 1000); Entity Framework Extensions • Entity Framework Classic • Bulk Operations • Dapper Plus

Runtime Evaluation Eval.Execute("x + y", new {x = 1, y = 2}); // return 3 C# Eval Function • SQL Eval Function

fugaku commented 5 years ago

I tried using JOIN with sample project here

I got an Invalid column name 'Id' Can you please tell me what I am doing wrong?

JonathanMagnan commented 5 years ago

Thank for the project, we will try it on our side.

JonathanMagnan commented 5 years ago

Hello @fugaku ,

Unfortunately, it looks we don't support this kind of JOIN when the column is modified for an alias

Under the hood, Entity Framework uses some alias ([x].[Id] AS [Id0]) which make the query invalid.

We will continue to investigate this weekend. I believe it could be something to perform a fix for this specific query.

fugaku commented 5 years ago

Thank you for your time to check this out.

I'm using a lot of this kind of query for updates, so it would be great if we can eliminate the need of EF Core Tracking for this.

rgroenewoudt commented 5 years ago

I'm running into a similar issue. Both tables have a column "IsActive" but Entity Framework generates an alias for the column in the joined table, resulting in an invalid UPDATE query.

JonathanMagnan commented 5 years ago

Hello @rgroenewoudt ,

I believe join currently work since a release we made a few months ago.

Here is an example: https://dotnetfiddle.net/G1B2Gs

Could you on your side provide an example of the query that doesn't work for you? So we will be able to investigate it.