CODEWITHSUNDEEP
c#sqlentity-frameworklinq-to-entitiesdistinct
Daniel
Daniel

Reputation: 3131

Why is this Entity Framework LINQ query not resulting in a SELECT DISTINCT?

Given the entity:

[Table("Clients")]
public class Client
{
    [Column("txtClientId")]
    public string ClientId { get; set; }

    [Column("OrganizationName")]
    public string ClientName { get; set; }
}

And the LINQ query:

var clients = (from c in database.Clients
                where c.ClientId.Contains(term) || c.ClientName.Contains(term)                
                select c).Distinct();

VS Debugging for 'clients' shows the following query:

SELECT 
    [Extent1].[txtClientId] AS [txtClientId], 
    [Extent1].[OrganizationName] AS [OrganizationName]
FROM [dbo].[Clients] AS [Extent1]
WHERE ([Extent1].[txtClientId] LIKE @p__linq__0 ESCAPE N'~') OR 
    ([Extent1].[OrganizationName] LIKE @p__linq__1 ESCAPE N'~')

Note there is no 'Distinct'. I have searched on this and found a few workarounds like grouping, anonymous types, or doing the distinct in code (ie ToList().Distinct()), but ideally, I would like the SQL:

SELECT DISTINCT
    [Extent1].[txtClientId] AS [txtClientId], 
    [Extent1].[OrganizationName] AS [OrganizationName]
FROM [dbo].[Clients] AS [Extent1]
WHERE ([Extent1].[txtClientId] LIKE @p__linq__0 ESCAPE N'~') OR 
    ([Extent1].[OrganizationName] LIKE @p__linq__1 ESCAPE N'~')

I have tried this with 'ClientId' marked as "[Key]", with no change in behavior.

Why is this not generating the query I expect, and how do I get it to do so? Since I have workarounds, I'm more interested in understanding what I'm missing here and the best way to get the desired SQL.

Upvotes: 2

Views: 695

Answers (2)

Phillip
Phillip

Reputation: 94

I believe it has something to do with using ".Contains()" in the link query.
Maybe you should try using LINQ chaining instead of Query syntax.
i.e. 

database
    .Clients
    .Where (client => client.ClientId.Contains(term) || client.ClientName.Contains(term))
    .Distinct();

Upvotes: 0

Amy B
Amy B

Reputation: 110111

You are calling Queryable.Distinct, which is definately translatable to Sql.

Perhaps LinqToEntities understands your use of Distinct on class Client as "referentially different". Each row in a database is a referentially different instance, even when there is no primary key.

Try projecting the columns you want into an anonymous type (which should use value equality instead of reference equality):

select new {c.ClientId, c.ClientName}

Upvotes: 5

Related Questions