I want to convert my SQL query to lambda expression.Below is the SQL query.I tried but failing to get expected result
UPDATE
Sales
SET
SI.ACP= SD.ACP
FROM
Sales SI
INNER JOIN
SaleDesc SD
ON
SI.GroupID= SD.GroupID;
-
If you tried something, then show us that, and explain why it didn't work.DavidG– DavidG2021-07-28 15:20:02 +00:00Commented Jul 28, 2021 at 15:20
-
At least try to code it. You want to use join or include with LINQ: learn.microsoft.com/en-us/dotnet/csharp/language-reference/… and for updating you can use an ORM or ADO.Net: stackoverflow.com/questions/46657813/…GH DevOps– GH DevOps2021-07-28 15:23:22 +00:00Commented Jul 28, 2021 at 15:23
-
AFAIK out of the box EF does not support such queries. You can look for some libraries like bulk extensions or entity framework extensions which possibly support something like this.Guru Stron– Guru Stron2021-07-28 15:30:55 +00:00Commented Jul 28, 2021 at 15:30
-
It is impossible in raw EF Core. But some extensions may help. I'd recommend linq2db.EntityFrameworkCore. See docs: Update, JoinAlexander Petrov– Alexander Petrov2021-07-28 16:11:27 +00:00Commented Jul 28, 2021 at 16:11
Add a comment
|
3 Answers
EF Core itself does not support bulk and native updates and everything should go through ChangTracker.
Anyway, you can use available extensions for EF Core, which can do that without raw SQL execution. For example linq2db.EntityFrameworkCore (disclaimer: i’m one of the creators)
Then you can write the following LINQ query:
var queryForUpdate =
from si in ctx.Sales
join sd in ctx.SalesDesc on si.GroupID equals sd.GroupID
select new { si, sd }
queryForUpdate
.Set(u => u.si.ACP, u => u.sd.ACP)
.Update();
answered Jul 28, 2021 at 16:16
Svyatoslav Danyliv
28.2k4 gold badges24 silver badges46 bronze badges
Comments
Sales must have a PK. you have different ways to write LINQ. this is one of the approaches (i try to make it more readable for you.)
Sales.Dump("original");
Sales
.Join(
SaleDescs,
sales => sales.GroupID,
desc => desc.GroupID,
(sales, desc) => new { Sales = sales, SaleDesc = desc }
)
.ToList()
.ForEach(s =>
{
Sales.Where(sl => sl.GroupID == s.Sales.GroupID).FirstOrDefault().ACP = s.SaleDesc.ACP;
});
SubmitChanges();
Sales.Dump("resulted");
since I have no details on table structure - i made up some for this example
Comments
use
using (var db = new MyContext())
{
var p = db.Sales
.Join(db.SaleDesc,
left => left.GroupID,
right => right.GroupID,
(left, right) => new { Sales = left, ACP = right.ACP });
foreach (var item in p)
{
item.Sales.ACP = item.ACP;
db.Sales.Attach(item.Sales);
db.Entry(item.Sales).State = EntityState.Modified;
db.SaveChanges();
}
}
or
using (var db = new MyContext())
{
db.Sales
.Join(db.SaleDesc,
left => left.GroupID,
right => right.GroupID,
(left, right) => new { Sales = left, ACP = right.ACP }).ForEachAsync(a => a.Sales.ACP = a.ACP);
db.SaveChanges();
}
7 Comments
Svyatoslav Danyliv
I hope you understand that if there are thousands records this will become a nightmare?
persian-theme
No, I did not consider it, but I think the Update () method does the same in the lower layers.
Svyatoslav Danyliv
Nope, Update from my answer generates exactly the same SQL as from question and no records are loaded to the client. In you solution you load everything and then EF sends a lot of updates
persian-theme
When you have "var queryForUpdate = ......" that is, you have loaded the records and then performed the update operation.
Svyatoslav Danyliv
Loading records, as you know initiated by ToList, ToArray, etc. Here we have just IQueryable which is not executed yet. Execution is provided via Update, which generates appropriate UPDATE SQL.
|