I have a database with a simple flyweight table referenced by another table. Let's call them, respectively, Category and Product.
The Product table has several columns, one of which is a foreign key column (complete with constraint) to the CategoryID to a row of the Category table. It's simple, and I have many of them in this particular database.
I am using Linq in Visual Studio 2008 to represent this data in my application. Both tables appear as normal, and neither have anything special in their properties that would indicate the behaviour I'll describe.
In the database itself, there are several Product rows which have CategoryID=1. There are two or three that have CategoryID=2. However, when the data is loaded into my application through Linq, iterating through context.Products shows that the first three have CategoryID=1, CategoryID=2 and CategoryID=3, and all the rest of the rows have CategoryID=NULL. This is not how it appears in the database. There are no other anomalies in this database.
Help?
edit: I've posted an answer below, but maybe the DevExpress guys can chime in? I'm happy it's now working correctly but I'm not happy that I still don't understand what went wrong.
The answer here was twofold:
I regenerated the entire database in the dbml file. This fixed the incorrect data showing up when I iterated through the query, but the data was still coming up incorrectly when I tried to put that data in the grid.
I had created a new column before doing step 1, and the data was showing up incorrectly. After doing step 1, the data in this new column (mapped only to CategoryID) was showing up correctly. However, the data in the original column (mapped to the entity Category) was still behaving as originally described. Once I changed the field from Category to CategoryID the behaviour stopped. I'm still not sure exactly why that happened, but at least for now I have a fix.
Thanks for your help, @JonSkeet. :)