How to correctly move data from a linked table to the main one in a LINQ query .NET?

Question

Filtering of the query result using "Where" does not work, in which previously one of the fields was replaced with values from the linked table

I have the Items table with another related table (ItemsAdditionalFiels)

[Table("Items")]
public class Items : Entity
{
    public string Id { get; set; }
    public string City { get; set; }
    public string Area {get; set; }
    public ICollection<ItemsAdditionalFiels> AdditionalFiels { get; set; }
}

[Table("ItemsAdditionalFiels")]
public class ItemsAdditionalFiels: Entity
{
    public string ItemId { get; set; }
    public string AddArea{ get; set; }
}

also I have the following query

IQueryable<Items> query = _context.Set<Items>()
    .Include(i => i.AdditionalFiels);

I need that in the case when the city is equal to "London", the values from the Area are replaced by the replaced by the first found AddArea value from the additional properties.

To do this, I wrote the following code

foreach (var item in query)
{
    if (item.City == "London")
    {
        item.Area = item.AdditionalFiels.FirstOrDefault()?.Location;
    }
}

When debugging the code, it is displayed that the necessary data has been successfully replaced.

However, if to try to filter the data, trying to find some value from Area, which was previously replaced by a value from AddArea, it finds nothing.

var test = query.Where(m => m.Area.Contains("Remote")).ToList(); //nothing was found

Does anyone have any idea what's wrong?

Answer 1

you should first get data to list, then Save Changes.

step1:

foreach (var item in (await query.ToListAsync()))
{
  if (item.City == "London")
  {
    item.Area = item.AdditionalFiels.FirstOrDefault()?.Location;
  }
}

or better:

foreach (var item in (await query.Where(x => x.City == "London").ToListAsync()))
{
  item.Area = item.AdditionalFiels.FirstOrDefault()?.Location;
}

step2:

await _context.SaveChangesAsync();

step3: now you can get data with updated items:

var test = query.Where(m => m.Area.Contains("Remote")).ToList();