Entity Framework query not returning correctly enumerated results

Question

I have this really strange problem where my entity framework query isn't enumerating correctly.

The SQL Server table I'm using has a table with a Sku field, and the column is "distinct". It isn't a key, but it doesn't contain any duplicate values. Using actual SQL with where, distinct and group by cluases I have confirmed this.

However, when I do this:

// Not good
foreach(var product in dc.Products)

or

// Not good
foreach(var product in dc.Products.ToList())

or

// Not good
foreach(var product in dc.Products.OrderBy(p => p.Sku))

the first two objects that are returned ARE THE SAME!!!

The third item was technically the second item in the table, but then the fourth item was the first row from the table again!!!

The only solution I have found is to use the Distinct extension method, which shouldn't really do anything in this situation:

// Good
foreach(var product in dc.Products.ToList().Distinct())

Another weird thing about this is that the count of the resulting queries is the same!!! So whether or not the resulting enumerable has the correct results or duplicates, I always get the number of rows in the actual table! (No I don't have a limit clause anywhere).

What could possibly cause this!?!?!?

This seems to be related to keys... the table doesn't have any keys. Anyone know if EF/LINQ require keys to be there? — John B
– John B ♦, Commented Mar 22, 2010 at 19:44
It looks like adding a key fixed the problem, but I'm still curious why this happens with no warning! — John B
– John B ♦, Commented Mar 22, 2010 at 19:53

John B · Accepted Answer · 2011-11-15 19:28:01Z

1

Make sure you have a Primary Key for the table.

answered Nov 15, 2011 at 19:28

John B♦

20.5k35 gold badges125 silver badges173 bronze badges

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

1 Comment

Nick Patsaris Over a year ago

Indeed, the lack of a primary key skews results

Collectives™ on Stack Overflow

Entity Framework query not returning correctly enumerated results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related