Entity Framework 4 returns distorted clutter if underlying data does not reflect the Entity master key

Question

We have a stored procedure in which there is a select statement:

select convert(int, c.ID) as ID, convert(nvarchar(255), c.name) as name, convert(varchar(32), a.state) as state from customer c join address a on c.addressid = a.ID where c.name like @custNameSpec 

This creates two entries when executed in a T-SQL window:

 ID Name State 1 Robert PA 2 Rob VA 

When executed in the Entity Framework 4 as an import function, it returns two records, but the first record is duplicated:

 ID Name State 1 Robert PA 1 Robert PA 

We removed the function import and the imported function, recreated it, etc. We also added these SQL convert () statements above to ensure that the Entity Framework understands the data types returned from the server.

What can we do to fix this? What causes duplicates?

Our tests include:

 var myresult3 = myUOW.DC.GetAdDir(todaysdate: null, store_nbr: 14, adtype: null).ToList(); var myresult4 = DB.GetAdDir(todaysdate: null, store_nbr: 14, adtype: null).ToList(); 

Both return the same incorrect result. The SQL profiler shows this call:

 exec [dbo].[GetCust] @todaysdate=NULL,@custNameSpec='Rob',@adtype=NULL 

EDIT:

Apparently, business rules have changed. POCO created from the Entity Framework incorrectly configured the primary key, so it returned the correct number of fields, but "deleted" the duplicates, making all duplicates the same (based on the fields of the POCO primary key.)

Other remote-related questions MergeOption , which might explain why this is happening.

Answer 1

When you create a unique constraint for Entity as a primary key, and you have duplicates coming from the data, the Entity Framework will repeat the first duplicate entry for all fields for each duplicate found.

In other words, the Entity Framework returns a distorted mess if your underlying data does not correctly reflect the primary key.