Incorrect EntitySet when using foreach instead of select

Question

I've got a very basic database schema and have created a dbml from it. Two tables are related in a one-to-many. (A)1-*(B) ... hence each A gets an EntitySet called "BSet".

If I do:

foreach (var a in db.A)
{
  Console.WriteLine(a.Name);
  foreach (var b in a.BSet)
  {
     Console.WriteLine(b.Number);
  }
}

I find that it prints out the same set of B's for each A. If I debug the L2S SQL Log I can see its creating the correct SQL each time it calls BSet. In spite of that it prints the same set of Bs for each A.

If I write a LINQ Select statement then I get the correct result.

Whats broken in the foreach? I am usually pretty good with LINQs subtleties but this one is confusing me!

Answer 1

Your code is fine. Most likely, there's a problem in the way the association property in your typed DataContext has been set up. Check that the association in the designer points to the right columns in each table.

On another point, your code is inefficient in that it will round-trip to the database for each inner loop iteration. You can avoid that round-tripping by writing this as a single LINQ query:

var query =
  from a in db.A
  from b in a.BSet
  select b.Number;

foreach (var item in query) Console.WriteLine (item);

Answer 2

The problem was because the B table did not have a primary key correctly setup.

I had added a primary key for the field which I believed was the primary key, but was actually not.