CODEWITHSUNDEEP
entity-framework
reidLinden
reidLinden

Reputation: 4170

EF query contains incorrect elements

I have a query with EF which looks like this:

var x = _db.qMetaDataLookups.ToList();

if I execute, direct on the SQL server SELECT * FROM qMetaDataLookup, 2155 distinct rows are returned. After executing the above, x ALSO contains 2155 elements.

The problem is that the data is wrong. I'm not getting the same data back from the EF as I do from the SQL Query.

In particular, theres a particular element that exists on the SQL output, call it "WXYZ", which makes no appearance at all in the EF version of the query (against the exact same database).

Instead, what I find are numerous repeats. If I call x.Distinct() the list filters down from 2155 elements, to a mere 143.

I'm flummoxed. I have never seen my EF and SQL results differ on a query this simple. There must be a very simple [face-palm] explanation, but I'm missing it.

Thanks.

EDIT qMetaDataLookup (a view) are contains information about our database. In essence, its a listing of all tables and views, and each of their columns, with other information about the datatype, length, precision, scale, etc. The 'key' in this table ought to be the column that matches "tableName.columnName" but instead EF chose for it all the datatype properties. This is why the query fails to perform as desired.

Upvotes: 0

Views: 79

Answers (2)

Andrew Lockhart
Andrew Lockhart

Reputation: 174

The issue might have been that your model was using a key with duplicate values where the Entity Framework was expecting unique values. This would happen if, for example, your data model used a composite primary key composed of foreign keys from other tables. It seems EF doesn't like composite primary keys very much, and so returned results from queries will generate what appear to be duplicated rows.

The fix seems to be to add a surrogate primary key column to your table which is guaranteed to be unique. If you still need to reference the foreign columns that's fine, so long as they aren't being used as a composite primary key for the table.

I can't claim any credit for the solution, but here's the link that helped me solve my issue: http://jepsonsblog.blogspot.ca/2011/11/enitity-framework-duplicate-rows-in.html

Upvotes: 0

Raghavan
Raghavan

Reputation: 637

Make sure the entity key is set correctly for qMetaDataLookup in the Entity Data Model. Sometimes the entity keys are messed up...

Upvotes: 1

Related Questions