Linq replace column value with another value

Question

Well, im doing a linq query to get a list of results with the same column, and then i need to replace that column value with a new one.

First Code:

var db = GetContext();
var result = from f in GetContext().ProjectStateHistories
             where f.ProjectId.Equals(oldProjectId)
             select f;

foreach (var item in result)
{
     var projectStateHistoryUpdate = db.ProjectStateHistories.Find(item.Id);
     projectStateHistoryUpdate.ProjectId = newProjectId;
     db.Entry(projectStateHistoryUpdate).State = EntityState.Modified;
}
db.SaveChanges();

I searched for some answers, and i found that i can use Select, and make a new object (Linq replace null/empty value with another value)

Second Code:

var result = (from f in GetContext().ProjectStateHistories
              where f.ProjectId.Equals(oldProjectId)
              select f).Select(d=> new { Id = d.Id, EventName = d.EventName, LogUser = d.LogUser, ProjectId = newProjectId, TimeStamp = d.TimeStamp });

And even, Third Code:

var db = GetContext();
var result = (from f in db.ProjectStateHistories
              where f.ProjectId.Equals(oldProjectId)
              select f).Select(d=> new { ProjectId = newProjectId});

But only the First Code works.

I wanted to ask what i am doing wrong, since i think it is better to change the value with a query, instead of using a foreach.

2nd and 3rd, you are reading the data, not updating it back ! — Shyju, May 06 '16 at 14:39
What's wrong with the first code fragment? Why do yo want to do it differently? — Gert Arnold, May 06 '16 at 14:41
1. do you really need to SaveChanges in the foreach loop?.2. Why are you calling GetContext() again in the query instead of using db? — raven, May 06 '16 at 14:41
@GertArnold first code is working great, but i wanted to avoid the foreach. — pierregomes, May 06 '16 at 14:41
@RobertoDeLaParra yah, savechanges must be out of the foreach, your are right thanks. About the GetContext, i m using db, my fault sorry. — pierregomes, May 06 '16 at 14:47
@RobertoDeLaParra I dont get any error. 2nd and 3rd code dont change nothing on DB. 1st is working fine. — pierregomes, May 06 '16 at 14:50
Hi Pierre, Welcome :) I've just had a thought that could help you, I am just free coding here! If you just put the for each as part of the select, and then save your changes will that work? `foreach (var source in db.ProjectStateHistories.Where(x => x.ProjectId== oldProjectId)) { source.ProjectId= newProjectId; db.Entry(source).State = EntityState.Modified; } db.SaveChanges(); ` I think this is a more efficient way of doing it. — Phillip Morton, May 06 '16 at 14:52
in the foreach loop don't you need to update the Id of the "item" to the new project id too? If you don't I guess the second query won't return anything — raven, May 06 '16 at 14:53
@PhillipBetts that code works, but there is a foreach :p I am going to test Riad code. — pierregomes, May 06 '16 at 15:02
@RobertoDeLaParra i can update all together, and i just debugged, and seconde query is returning the right results, but do not update the DB :) I am going to test Riad code — pierregomes, May 06 '16 at 15:02
Thanks @pierregomes, however there is no way to do a straightforward replace using EF, unless you just wrote the SQL Query yourself. Which means you loose out on all the API features of EF, such as concurrency. — Phillip Morton, May 06 '16 at 15:05

Riad Baghbanli · Accepted Answer · 2016-05-06T14:59:07.100

5

See code below:

var db = GetContext();
(from f in db.ProjectStateHistories
     where f.ProjectId.Equals(oldProjectId)
     select f)
       .ToList()
       .ForEach(i => i.ProjectId = newProjectId);
db.SaveChanges();

Alternatively:

var db = GetContext();
db.ProjectStateHistories
       .Where(f => f.ProjectId.Equals(oldProjectId))
       .ToList()
       .ForEach(f => f.ProjectId = newProjectId);
db.SaveChanges();

edited May 06 '16 at 14:59

answered May 06 '16 at 14:56

Riad Baghbanli

3,105
1
12
20

Thanks a lot @Riad ! First Code worked, what do you think is better to use ? The first one, or the second one ? – pierregomes May 06 '16 at 15:05
@pierregomes This too has a for each loop in it ;) Its just refactored ;) – Phillip Morton May 06 '16 at 15:11
1

I prefer the method notation (the second one). I find it easier to read than query notation. – Riad Baghbanli May 06 '16 at 15:12

score 2 · Answer 2 · edited May 23 '17 at 11:53

2

The shortest way I know of to replace your code is this:

var db = getcontext();

db.ProjectStateHistories
    .Where(f => f.ProjectId.Equals(oldProjectId))
    .ToList()
    .ForEach(f => f.ProjectId = newProjectId);

db.SaveChanges();

Other answers can be found here

edited May 23 '17 at 11:53

Community

1
1

answered May 06 '16 at 15:07

mark_h

5,233
4
36
52

Sorry for not answering. This works too :) Thanks a lot for your time! – pierregomes May 09 '16 at 11:33

score 0 · Answer 3 · answered May 06 '16 at 15:04

0

I've just had a thought that could help you, I am just free coding here!

If you just put the for each as part of the select, and then save your changes will that work?

foreach (var source in db.ProjectStateHistories.Where(x => x.ProjectId == oldProjectId)) 
{ 
     source.ProjectId= newProjectId; 
     db.Entry(source).State = EntityState.Modified; 
} 
db.SaveChanges();

I think this is a more efficient way of doing it.

Also the .Select() method is only really useful if you need to Project to a view Model, it won't change the variables in the database, just show them in the newly declared object.

Thanks,

Phill

answered May 06 '16 at 15:04

Phillip Morton

244
2
13

Thanks a lot @Phillip :) Then there is no change between your code and Riad's code ? Because yours it's easier to read. That's a nice solution too. I made this question because i thought i should avoid foreach – pierregomes May 06 '16 at 15:24
I am using your code, because it is easier to read :) Thanks a lot for your time. – pierregomes May 09 '16 at 11:35

Linq replace column value with another value

3 Answers3