CODEWITHSUNDEEP
c#entity-framework-6
LP13
LP13

Reputation: 34109

How to select and update in one query in EF

I have the following query that selects specific columns from the table and construct DTO. Since i only need certain columns i am not retrieving all the columns from database. In the same method i would also like to update one specific column in retrived Entity and save entity to database before i return the result.

Entities

public class Client
{
   public int ClientID { get; set; }
   public string UploadPath { get; set; }
   public IEnumerable<Batch> Batches { get; set; }
}

public class Batch
{
   public int BatchID { get; set; }
   public int StatusID { get; set; }
}

DTO

public class ClientDTO
{
   public int ClientID { get; set; }
   public string UploadPath { get; set; }
   public IEnumerable<Batch> Batches { get; set;}
}

public class BatchDTO
{
   public int BatchID { get; set; }
   public int StatusID { get; set; }
}

Method

 public async Task<IEnumerable<ClientDTO>> GetData()
 {
        var query = from r in _dbcontext.Clients
                     select new ClientDTO()
                     {
                         ClientID = r.ClientID,
                         UploadPath = r.UploadPath,
                         Batches = from r1 in r.Batches
                                   select new BatchDTO()
                                   {
                                       BatchID = r1.BatchID,
                                       StatusID = r1.StatusID
                                   }
                     };            

        var result = await query.ToListAsync().ConfigureAwait(false);

        // before returning the result i want to update StatusID column in `Batch` entity. 
        // How do i do that since result is not entity?

        return result;
}

Upvotes: 1

Views: 4502

Answers (1)

Igor
Igor

Reputation: 62213

You could create the entity in the state you want, attach it, mark the property as changed, and then save.

var batchToSave = new Batch()
{
    BatchID = whateverYourBatchIdIs,
    StatusID = 234
};
_dbContext.Set<Batch>().Attach(batchToSave);
_dbContext.Entry(batchToSave).Property(x=>x.BatchID).IsModified = true;
_dbContext.SaveChanges();

If your batch id is in what you retrieved then you can do a Select to create one for each record you want to update and attach them all. Something like this (I did not work out your object graph, you will have to do that yourself).

for (var batchToSave = result.Select(x => new Batch() {BatchID = x.batchId, StatusID = x.status}))
{
    _dbContext.Set<Batch>().Attach(batchToSave);
    _dbContext.Entry(batchToSave).Property(x => x.BatchID).IsModified = true;
}
_dbContext.SaveChanges();

On the other hand it could be that if you just mean the Retrieved batches that they are already being tracked by the DbContext. In that case you can access them and change them like this:

foreach (var batch in _dbContext.ChangeTracker.Entries().OfType<Batch>())
{
    batch.StatusID = 123;
}
_dbContext.SaveChanges();

Upvotes: 1

Related Questions