?
Solved

LINQ To SQL: Distinct()

Posted on 2010-09-24
16
Medium Priority
?
810 Views
Last Modified: 2012-05-10

Hi guys,

I'm trying to retrieve a collection of distinct users from my database.
var DistinctUsers = AllUsers.Distinct(new UserComparer());

Open in new window

AllUsers is an IQueryable<UserEntity> and has multiple entries for the same user. Every user has a unique ID, but there are other fields which are not identical in AllUsers. So, I came up with this simple IEqualityComparer object, which relies on the comparision of user IDs.
public class UserComparer : IEqualityComparer<UserEntity>
    {
        public bool Equals(UserEntity x, UserEntity y)
        {
            return (x.Id == y.Id);
        }
        public int GetHashCode(UserEntity x)
        {
            return (x.Id.GetHashCode());
        }
    }

Open in new window

However, this still does not work. I'm still getting redundant users in my DistinctUsers collection.

Any ideas? Am I missing something?
0
Comment
Question by:softwarea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +1
16 Comments
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 33751661
Put a breakpoint in the 'Equals' function - is it being called ?
0
 

Author Comment

by:softwarea
ID: 33751693
Hi Andy,

how did you figure THIS? Damn, it is NOT called.
I had not even thought about checking this.

Any idea why the comparer is not called? I probably missed someting totally stupid...
0
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 1000 total points
ID: 33751702
>>how did you figure THIS?

Because the function should work IF it was being called, as it didn't work then ....
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 44

Expert Comment

by:AndyAinscow
ID: 33751715
As to why it isn't being called?  You need to look at what should be calling it - is that function itself actually being called?
0
 

Author Comment

by:softwarea
ID: 33751733
Yes!
[code]var DistinctUsers = AllUsers.Distinct(new UserComparer());[/code]
This line is definitely called.

And AllUsers contains all the results which I would expect. However, there are redundant entries.
0
 
LVL 5

Assisted Solution

by:VincentSG
VincentSG earned 1000 total points
ID: 33751754
Hi!

Try modifying your code to
var DistinctUsers = AllUsers.ToList().Distinct(new UserComparer());
0
 

Author Comment

by:softwarea
ID: 33751766
Hi Vincent,

unbelievable. I had just tried that a second ago on my own.
And yes, now it is working!

Perfect!

Are you guys ok, if I spilt the points?
0
 
LVL 14

Expert Comment

by:existenz2
ID: 33751771
Turning the code around would better actually. If you put .ToList() as last it will first execute the distinct and then convert it to a list, which should ensure that the Distinct is definatly called.
var DistinctUsers = AllUsers.Distinct(new UserComparer()).ToList();

Open in new window

0
 
LVL 5

Expert Comment

by:VincentSG
ID: 33751775
Hi!
Great that it helps. Sure go ahead.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 33751798
yes, split is OK
0
 

Accepted Solution

by:
softwarea earned 0 total points
ID: 33751800
I just tried both versions:


1. var DistinctUsers = AllUsers.ToList().Distinct(new UserComparer());
2. var DistinctUsers = AllUsers.Distinct(new UserComparer()).ToList();


No. 1 works.
No. 2 does not!
0
 
LVL 5

Expert Comment

by:VincentSG
ID: 33751822
Hi softwarea!

I am not sure whether this is a problem with Linq (but I think it is).

When you tried to call Distinct to the IQueryable<UserEntity> AllUsers, the data has not actually arrived yet. Thus you UserComparer method is not called.

When you called to ToList() method, you are actually fetching the data and then feeding them to the UserComparer method.

I agreed with existenz2 that it may not be efficient but I have yet to find a better solution.
0
 

Author Comment

by:softwarea
ID: 33751844
I think the same.

LINQ queries rely heavily on lazy evaluation. I just didn't know that you have to execute the query first (by calling ToList()) and then apply the Distinct() operator.
The other way around makes more sense on first view, though. But how knows... :-)

Thanks for all your help guys!  
0
 
LVL 14

Expert Comment

by:existenz2
ID: 33751892
It indeed seems a LINQ problem, because of the following:
1. var DistinctUsers = AllUsers.ToList().Distinct(new UserComparer());
2. var DistinctUsers = AllUsers.Distinct(new UserComparer()).ToList();


#1 first executes the previous lazy LINQ queries and return an IList on which it will execute the distinct (which does directly execute it again).
#2 This should execute the lazy LINQ query before executing the Distinct (or while executing the distinct) and then cast the result to an IList.

Both cases should work perfectly in theory for LINQ, only #2 should be a bit faster. But at least it is solved.
0
 
LVL 5

Expert Comment

by:VincentSG
ID: 33766853
Hi softwarea,
Could you try to signal Close Request again? Somehow this question is still not closed.
0
 

Author Comment

by:softwarea
ID: 33767221
Hi Vincent,

the thread will be closed automatically by tomorrow.

Thanks!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question