Updating an item property within IEnumerable but the property doesn't stay set?

Question

I have two tables: Transactions and TransactionAgents. TransactionAgents has a foreign key to Transactions called TransactionID. Pretty standard.

I also have this code:

BrokerManagerDataContext db = new BrokerManagerDataContext();

var transactions = from t in db.Transactions                    where t.SellingPrice != 0                     select t;  var taAgents = from ta in db.TransactionAgents                select ta;  foreach (var transaction in transactions) {     foreach(var agent in taAgents)     {         agent.AgentCommission = ((transaction.CommissionPercent / 100) * (agent.CommissionPercent / 100) * transaction.SellingPrice) - agent.BrokerageSplit;     }  }  dataGridView1.DataSource = taAgents; 

Basically, a TransactionAgent has a property/column named AgentCommission, which is null for all TransactionAgents in my database.

My goal is to perform the math you see in the foreach(var agent in taAgents) to patch up the value for each agent so that it isn't null.

Oddly, when I run this code and break-point on agent.AgentCommission = (formula) it shows the value is being calculated for AgentCommissision and the object is being updated but after it displays in my datagrid (used only for testing), it does not show the value it calculated.

So, to me, it seems that the Property isn't being permanently set on the object. What's more, If I persist this newly updated object back to the database with an update, I doubt the calculated AgentCommission will be set there.

Without having my table set up the same way, is there anyone that can look at the code and see why I am not retaining the property's value?

Answer 1

IEnumerable<T>s do not guarantee that updated values will persist across enumerations. For instance, a List will return the same set of objects on every iteration, so if you update a property, it will be saved across iterations. However, many other implementations of IEnumerables return a new set of objects each time, so any changes made will not persist.

If you need to store and update the results, pull the IEnumerable<T> down to a List<T> using .ToList() or project it into a new IEnumerable<T> using .Select() with the changes applied.

To specifically apply that to your code, it would look like this:

var transactions = (from t in db.Transactions                     where t.SellingPrice != 0                      select t).ToList();  var taAgents = (from ta in db.TransactionAgents                 select ta).ToList();  foreach (var transaction in transactions) {     foreach(var agent in taAgents)     {         agent.AgentCommission = ((transaction.CommissionPercent / 100) * (agent.CommissionPercent / 100) * transaction.SellingPrice) - agent.BrokerageSplit;     }  }  dataGridView1.DataSource = taAgents;  

Answer 2

Specifically, the problem is that each time you access the IEnumerable, it enumerates over the collection. In this case, the collection is a call to the database. In the first part, you're getting the values from the database and updating them. In the second part, you're getting the values from the database again and setting that as the datasource (or, pedantically, you're setting the enumerator as the datasource, and then that is getting the values from the database).

Use .ToList() or similar to keep the results in memory, and access the same collection every time.