Duplicate Rows when Data Binding with LINQ to Entities

Question

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);
        sqn.Open();
        adap.Fill(ret);
    }
    return ret;

MORE:

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.

Answer 1

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();
}

Answer 2

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.

Answer 3

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.