Entity framework result discrepancy for a database views

Question

I have one specific view created in my DB(joins about 5-6 tables with a left join).This view is added to my edmx (entity framework 1.0) . Recently I noticed that one of the column records obtained using the edmx (linq to entities and then ToList()) got duplicated multiple times though in the database view they were different

Column-N (Expected result/ result from DB view)
---------
data1
data2
data3
data4
data5

Column-N(Actual result generated by entity framework)
---------
data1
data1
data1
data1
data1

I fired up my SQL profiler,got the query which was sent by my application to the SQL Server, ran it and it returned me the expected result.

MSDN has a similar post here and here but the moderator has not elaborated on how to solve this problem. My key happens to be a GUID

The root cause you pointed out I think is correct, the problem is on the application side EF mapping, as EF has different object mapping rules with database. when the query results have been returned from database, the EF will do the mapping on application memory according to its own designed logic.

It's important to take these logic into account when you desingn your view query in your database side. I think you should do some adjustment on your view query.

I am not sure whether you have sorted the problem, if not please provide the database structure related to this issue and the view query you have written.

Thanks Binze

Has someone encountered a similar problem before ?

Roarke

Answer 1

The problem is in fact with the key. You have to a) have a unique identifier for each row in the view. and b) map that key accordingly in the edmx. Otherwise as your quote states, the mapping logic will see each subsequent row and figure that it can use the same object instance that it returned before

Abdomen

You are right. I was wrongly assuming that it was picking up my GUID as primary key until I went and saw the StorageModel and Conceptual Model. It was using some other column as the primary key and hence the mess. Fixed it by changing the key manually(without using VS2008 GUI) – Roarke

Answer 2

Same problem for me. An Entity View (VReport) was generated automatically from VS2010 wizard to something like:

class VReport
Line: int (key) 
Desc: string 
Date: DateTime

When I retrieved the records from the database, the SQL query was formed correctly and returned the expected (and distinct) results, but the Entity Framework instead returned a lot of duplicated records.

But instead, also the Date column/field would have to partecipate in the formation the Entity KEY

So, to resolve this issue, I changed the property of the field from Entity Key: false -> true

class VReport
Line: int (key) 
Desc: string 
Date: DateTime (key)

Ingridingrim