C# foreach loop not iterating through item

Question

Am trying to populate a datagrid with contents of a List, but afterwards the list contains the right amount of items, all with the same data row. For example, when press = 8 there are three different batches returned in SQL, but in C# there is one batch returned three times. I'm not sure if the problem is in my query or foreach loop - but I used a messagebox to display the current ScheduleNr during the loop and it was always the same.

            List<BatchList> myBatches = new List<BatchList>();
            BatchList batch;
            var db = new BatchListContext();
            var batchQuery = from b in db.BatchLists
                        where b.Press == press
                        orderby b.ExtrDate, b.BatchNr
                        select b;
            if (batchQuery.Count() == 0)
            {
                MessageBox.Show("Error loading batches", "Error", MessageBoxButton.OK);
                return false;
            }
            foreach (var batchItem in batchQuery)
            {
                batch = new BatchList();
                batch.ScheduleNr = batchItem.ScheduleNr;
                batch.BatchNr = batchItem.BatchNr;
                batch.Press = batchItem.Press;
                batch.ExtrDate = batchItem.ExtrDate;
                batch.NoOrders = batchItem.NoOrders;
                myBatches.Add(batch);
                MessageBox.Show(String.Format("{0}", batchItem.ScheduleNr.ToString())); //Always shows same ScheduleNr
            }

Any advice would be greatly appreciated where I might have made a fundamental mistake.

Many thanks.

//EDIT BatchList is a SQL Server View that if queried in SSMS as follows:

SELECT * FROM BilletPlanner.BatchList WHERE Press = 8

returns:

ScheduleNr  BatchNr Press   ExtrDate    NoOrders
10035620    2       8       2015-06-22      32
10035629    3       8       2015-06-22      22
10035631    4       8       2015-06-23      32

But the DataGrid I am using in C# shows the following (crude copy as cannot paste image currently)

ScheduleNr  BatchNr Press   ExtrDate    NoOrders
10035620    2       8   22/06/2015      32
10035620    2       8   22/06/2015      32
10035620    2       8   22/06/2015      32

If `db.BatchLists` returns `BatchList`, then you don't really need to loop through the query and copy an identical `BatchList`, you could just add a `ToList()` to the end of your query (wrap it in brackets and add it at the end, i.e. `myBatches = (from b in db.batchLists...).ToList()` — Matt Burland, Jun 22 '15 at 14:57
Hi Matt, that has certainly helped clean up my code, but the problem still remains. Which I guess means the actual problem lies in the query - though why it is returning the same row three times I have no idea. — Paul Cubitt, Jun 22 '15 at 15:02
I think your database has the items in there multiple times, can you check? — DavidG, Jun 22 '15 at 15:03
@PaulCubitt what do you mean returning same row 3 times can you explain where that's happening.. if so then you need to do some filtering on your linq query, check out this posting also if you want to filter out the duplicates to get a distinct list http://stackoverflow.com/questions/1786770/linq-get-distinct-values-and-fill-list — MethodMan, Jun 22 '15 at 15:03
Updated question to try and better explain the current outcomes — Paul Cubitt, Jun 22 '15 at 15:23
Move the declaration of `batch` inside your `foreach` loop: e.g. `BatchList batch = new BatchList();` — DavidG, Jun 22 '15 at 15:39

score 1 · Accepted Answer · answered Jun 22 '15 at 15:39

1

Assuming this is Entity Framework accessing a SQL Server View: A common issue with views in EF is primary key confusion. EF tends to attempt to determine what is a truly unique value from a view by the datatypes it can see in metadata. I recommend tweaking your view to have a clear unique primary key (cast as int, distinct so on). Essentially EF is confusing one of the other columns (press, NoOrders) as the primary key and a duplicate value means EF duplicates the entire row. Its a really stupid issue to have to deal with.

answered Jun 22 '15 at 15:39

AIDA

519
2
14

Is that tweaking within the View itself, or the EF model of it? – Paul Cubitt Jun 22 '15 at 15:42
It depends on the EF mode you are using. Code-First would be in the code representation of the view. Database/Model-First unfortunately any changes you make to the model will be lost if the model is regen'd or the view removed/added (to update it). In my previous role, we had to do this on several views in the database since we updated our model frequently. – AIDA Jun 22 '15 at 15:52
Genius - thank you so much. In fact I just moved the DatabaseGenerated Key to before the ScheduleNr (it had been created for the Press column like you suspected) and now it works. – Paul Cubitt Jun 22 '15 at 15:57

C# foreach loop not iterating through item

1 Answers1