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
– Matt Burland, Commented Jun 22, 2015 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
– Paul Cubitt, Commented Jun 22, 2015 at 15:02
I think your database has the items in there multiple times, can you check? — DavidG
– DavidG, Commented Jun 22, 2015 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 stackoverflow.com/questions/1786770/… — MethodMan
– MethodMan, Commented Jun 22, 2015 at 15:03
Updated question to try and better explain the current outcomes — Paul Cubitt
– Paul Cubitt, Commented Jun 22, 2015 at 15:23

AIDA · Accepted Answer · 2015-06-22 15:39:12Z

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, 2015 at 15:39

AIDA

5292 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Paul Cubitt Over a year ago

Is that tweaking within the View itself, or the EF model of it?

AIDA Over a year ago

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.

Paul Cubitt Over a year ago

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.

Collectives™ on Stack Overflow

C# foreach loop not iterating through item

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related