Duplicate Rows when Data Binding with LINQ to Entities

Go To StackoverFlow.com


I have problems binding both a telerik RadGrid and a plain vanilla ASP.NET GridView to the results of the following LINQ to entities query. In both cases the grids contain the correct number of rows, but the data from only the first handful of rows is duplicated in all the other rows. I'm directly assigning the return value from this code the the DataSource property on the grids.

public IEnumerable<DirectoryPersonEntry> FindPersons(string searchTerm)
    DirectoryEntities dents = new DirectoryEntities();
    return from dp in dents.DirectoryPersonEntrySet
           where dp.LastName.StartsWith(searchTerm) || dp.Extension.StartsWith(searchTerm)
           orderby dp.LastName, dp.Extension
           select dp;

ADDED: This is the alternate plain ADO.NET code that works:

    DataTable ret = new DataTable();
    using (SqlConnection sqn = new SqlConnection(ConfigurationManager.ConnectionStrings["WaveAdo"].ConnectionString))
        SqlDataAdapter adap = new SqlDataAdapter("select * from DirectoryPersonList where LastName like '" + searchTerm + "%' order by LastName ", sqn);
    return ret;


  1. The query sent to SQL Server by LINQ works.
  2. Iterating the LINQ query results before returning them results in the same duplications.
  3. Iterating the LINQ results in the calling method, before binding, results in the same duplications.

UPDATE: Based on the very logical and fitting advice from Marc Gravel below, I found that the EF designer had made a very uneducated guess at an Entity Key for my entity class, the first field in its list of fields, Department, of which there are only about seven entries shared across all other records.

This is indeed the cause of the duplication. If only I could change or remove the entity key, but this EF designer with all the business logic of an Etch-a-Sketch is admirably committed to repeating it's retarded choice of key while laughing at me locked outside begging to change the key.

2009-06-16 14:27
by ProfK
Are you sure what the table doesn't contain duplicates? The query is correct - Alexander Prokofyev 2009-06-17 06:17
@Alexander, yes, I am sure. A plain DataTable query on the same view results in the grids binding correctly - ProfK 2009-06-17 08:28
Please, show more code. Both working and problematic - Alexander Prokofyev 2009-06-19 05:03
(replied to comment - Marc Gravell 2009-06-22 22:44
Wish I could up-vote this question more. This problem was doing my head in for days! Should've come to SO sooner, I guess - Phil.Wheeler 2010-07-27 20:41


It looks to me like you have a borked primary key. The "identity management" aspect of LINQ-to-SQL and EF means that it is obliged to give you back the same instance whenever it sees the same primary key value(s) for the same object type.

For example, given the data:

id     | name       | ...
1      | Fred       | ...
2      | Barney     | ...
1      | Wilma      | ...
1      | Betty      | ...

Then if it thinks id is a primary key when iterating over the objects from LINQ, it is forced to give you "Fred", "Barney", "Fred", "Fred". Essentially, when it sees id 1 again, it doesn't even look at the other columns - it simply fetches the instance with id 1 from the identity cache - and gives you the same Fred instance it gave you previously. If it doesn't think id is a primary key, it will treat each row as a separate object (and so what if it has the same value in one of the fields as another record - that isn't exactly unusual).

I would advise checking that any fields you have marked as a primary key (in your DBML/EDM model) really are unique per row. In the case above, the id column clearly doesn't represent a unique identifier, so is not suitable as a primary key. Just unmark it as such in the LINQ-to-SQL / EF designer.

update: in particular, look at the "Entity Key" property for the various properties in the designer - especially if you are querying a view. Check that "Entity Key" is only set to true for suitable columns (i.e. those that make the row unique). If it is set incorrectly, set it to false. This is also visible as the yellow key icon - this should only appear on things that genuinely are unique identifiers for a record.

2009-06-21 14:15
by Marc Gravell
+1 for using borked. Also good answer - Wyatt Barnett 2009-06-21 14:41
@Marc, great stuff, you identified the problem most accurately. However, you suggest just removing the entity key flag from the problem column, but I'm not allowed to do this - ProfK 2009-06-22 22:33
I can change the entity key for the entity set, but not for the table, so the mappings get 'borked' - ProfK 2009-06-22 22:38
Either it/they uniquely identifies the row or it doesn't/don't. If they do, Entity Key should work. If they don't - it won't work. Can you define "not allowed to do this" - Marc Gravell 2009-06-22 22:44
Mine was the same, being I had updated the database with 2 columns being the primary key, but forgot to update the DBML file. Updating so it reflected the primary keys stuff worked - Wolf5 2017-05-08 13:19


And if you wrap the link query in a parenthesis and use the .Distinct() extension?

public IEnumerable<DirectoryPersonEntry> FindPersons(string searchTerm)
    DirectoryEntities dents = new DirectoryEntities();
    return (from dp in dents.DirectoryPersonEntrySet
           where dp.LastName.StartsWith(searchTerm) || dp.Extension.StartsWith(searchTerm)
           orderby dp.LastName, dp.Extension
           select dp).Distinct();
2009-06-19 13:41
by StevenMcD
That gives me even stranger results, with similar duplicates, but a different set of entries that fit the same criteria. I'll blog the differences - ProfK 2009-06-19 18:18


One difference between your working and broken queries is the orderby clause. I found a documented bug in the orderby implementation in Linq to Entities... there may be others.

Try removing orderby from the broken query and see if you still get duplicates.

Another difference is the OR in the where clause. Try using only the first part [ where dp.LastName.StartsWith(searchTerm) ] and see if you still get duplicates.

2009-06-22 17:06
by Handcraftsman


I faced the same problem and solved it with a workaround. I am posting it here as it might help others coming here.

instead of select dp , use

select new <ObjectName>
a = v.a
b = v.b

This will not return duplicates.

2011-02-16 17:33
by franklins