How to Update an array with Dapper Extension?

Question

I get from DB my entities' list, change some properties and try to Update in in DB.

using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var dataPredicate = Predicates.Field<Data>(f => f.Id, Operator.Eq, new [] {1, 2, 3}); // of course it's for an example 
var data = cn.GetList<Data>(dataPredicate);

foreach (var element in data)
{
     element.Status = StatusEnum.Pending;
     element.LastChange = DateTime.Now;
}

foreach (var activeRequest in data)
{
     cn.Update(activeRequest);
}
cn.Close();
}

I tried also:

var updated = data.Select(s => new Data
{
     Id = s.Id,
     CreateDate = s.CreateDate,
     ForeignId = s.ForeignId,
     LastChange = DateTime.Now,
     Status = RequestStatus.Pending
 });

And I get InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

I don't have any problem with another operations.

How can I Update it correctly?

Answer 1

cn.GetList<Data>(dataPredicate) returns IEnumerable and every time you use it with foreach actual sql query gets executed and DataReader is used to provide IEnumerable with data.

So in your case you are executing DataReader twice, and second time you are trying to do Update query on the same connection with DataReader still open.

If this is desirable behaviour (too much data and you do want to load it one by one with DataReader) you should use separate connection for Update queries.

Another option will be to load all the data to list and then iterate trough it, like this:

var data = cn.GetList<Data>(dataPredicate).ToList();