Inexplicable Entity Framework Entity - One Column Wrong
First, I already have a workaround, plus the database it is happening on is being replaced (completely new db / schema + new app)
Having said that, I would like to understand WHY this happened, so I don't run into this anymore. Thoughts are appreciated.
Anyway: take this simple line of code:
var privs = _olddb.tbl_Privileges.Where(x => x.UserID == userFrom);
Simple enough. Basically, an example of data output should be:
+--------+----------+------------+----------------+------------+
| UserID | RegionID | FacilityID | InitiativeType | AccessType |
+--------+----------+------------+----------------+------------+
| 290 | 27 | 123 | C | F |
| 290 | 27 | 123 | P | F |
| 290 | 27 | 124 | C | F |
| 290 | 27 | 124 | P | F |
+--------+----------+------------+----------------+------------+
Instead ... I get this:
+--------+----------+------------+----------------+------------+
| UserID | RegionID | FacilityID | InitiativeType | AccessType |
+--------+----------+------------+----------------+------------+
| 290 | 27 | 123 | P | F |
| 290 | 27 | 123 | P | F |
| 290 | 27 | 124 | P | F |
| 290 | 27 | 124 | P | F |
+--------+----------+------------+----------------+------------+
Notice the column InitiativeType
....
However, if I run this command:
var privs =_olddb.Database.SqlQuery<tbl_Privileges>("SELECT * FROM tbl_Privileges WHERE UserID = " + userFrom);
Then I get the correct output.
What gives?
-EDIT- Regarding the answer provided by marc_s (Keep in mind that I didn't have a hand in creating the original db and I'm afraid to modify it, it is used by a very old web application.) I honestly feel stupid for not checking the PC before publications, my apologies. I checked the database, this table has no PC at all. So I checked edmx in EF and found that only RegionID and FacilityID had Entity Keys set:
So, I updated the edmx model and set all fields as keys (since I can have 4 fields equally easily between two sets of privileges, since the Access Type has multiple values), ran the script again, and this time it worked fine with the Linq statement ...
source to share
This is most likely a problem with defining your primary key in the original table - I've seen this happen when querying views (which usually don't have a predefined primary key).
Suppose your PC is on this table ( UserID, RegionID, FacilityID
) (just my guess as they are all named ..ID
- might be wrong). When you return 4 rows from SQL Server, the first row contains values ( 290, 27, 123
) as the primary key; EF happily instantiates your object with these values (and other non-character columns) for you.
Now comes the second line - again the PK ( 290, 27, 123
) values , and now EF goes hmmmm, I've seen these values before - it must be the same line, since the primary key must be unique by definition ! So EF will add a second copy of this first instance of the object it created.
So, you need to make sure that your primary key - actually defined in the database in the table, or what EF assumes is the primary key for the view (default: all non-nullable columns) is truly unique for each row - otherwise you see things like this ...
This will not happen with your second approach, where you just simply execute an arbitrary SQL statement - in which case EF will display each row returned individually, without checking its own primary key definition (since you are not actually traversing your DbContext
and its database model )
source to share