locked
Linq Distinct(IEqualityComparer)

    Question

  • Hello!

    I have trouble with the Distinct extended method overload that takes a IEqualityComparer.

     

    I'm mean to get the five last searches where the resultCount > 0, but avoid duplicates within the five.

     

    I get the runtime NotSupportedException message stating: Unsupported overload used for query operator 'Distinct'. I have successfully tried .AsEnumerable and .ToList, but those two gets the entire table first and then makes an in memory Disctinct, which is not acceptable.

     

    My code looks like this:

    Code Snippet

    var myDistinctSearches = var mySearches = context.Searches
       .Where(s => s.resultCount > 0)
       .OrderByDescending(s => s.dateCreated)
       .Distinct(new MyComparer())
       .Take(5);

    public class MyComparer : IEqualityComparer<Search>
    {
       public bool Equals(Search x, Search y) {
          return x.searchPhrase == y.searchPhrase;
       }

       public int GetHashCode(Search obj) {
          return obj.searchPhrase.GetHashCode();
       }
    }

     

     

    Has anyone successfully used the Distinct<(Of <(TSource>)>) Generic Method (IQueryable<(Of <(TSource>)>), IEqualityComparer<(Of <(TSource>)>)) ?

     

    ::m 

    Friday, January 25, 2008 2:42 PM

All replies

  • I'm trying, more or less, to do the same thing.  Except that in my case I am trying to extend the DynamicQuery API to allow for selecting distinct records on the database.

    I've created an extension function that allows for a parameterless distinct, but I'm struggling with how to make it work with the iequalitycomparer overload.

    The parameterless overload looks like this:

     <Extension()> _
           Public Function Distinct(ByVal source As IQueryable) As IQueryable
                If (source Is Nothing) Then Throw New ArgumentNullException("source")
                Return source.Provider.CreateQuery( _
                    Expression.Call( _
                        GetType(Queryable), "Distinct", _
                        New Type() {source.ElementType}, source.Expression))
            End Function

    To be honest, however, I'm casting around in the dark.  I happened upon the above by sheer luck. 

    So far, I have:

     <Extension()> _
            Public Function Distinct(ByVal source As IQueryable, ByVal keySelector As String, ByVal elementSelector As String) As IQueryable
                If (source Is Nothing) Then Throw New ArgumentNullException("source")
                If (keySelector Is Nothing) Then Throw New ArgumentNullException("keySelector")
                Dim keyLambda As LambdaExpression = DynamicExpression.ParseLambda(source.ElementType, Nothing, keySelector)
                Dim elementLambda As LambdaExpression = DynamicExpression.ParseLambda(source.ElementType, Nothing, elementSelector)
                Return source.Provider.CreateQuery( _
                    Expression.Call( _
                        GetType(Queryable), "Distinct", _
                        New Type() {source.ElementType, keyLambda.Body.Type}, _
                        source.Expression, Expression.Quote(keyLambda)))
            End Function


    Does anyone who really understands how the dynamicQuery API works have an idea how to make the iequalitycomparer work in an overloaded extension?

    Thanks,
    Jerome
    Thursday, September 18, 2008 9:24 PM
  • fields in MyComparer does not exist in type Object.... ??? it does make sense somehow.. I only started C# a month ago: if someone can explain to me why one should specify the template it would be nice... 

    The following should work with no problem:

    Code Snippet

    IEnumerable<Search> myDistinctSearches = var mySearches = context.Searches
       .Where(s => s.resultCount > 0)
       .OrderByDescending(s => s.dateCreated)
       .Distinct(new MyComparer())
       .Take(5);

    public class MyComparer : IEqualityComparer<Search>
    {
       public bool Equals(Search x, Search y) {
          return x.searchPhrase == y.searchPhrase;
       }

       public int GetHashCode(Search obj) {
          return obj.searchPhrase.GetHashCode();
       }
    }


    Thursday, October 23, 2008 11:00 PM
  •  

    I found out the answer.

     

    You're querying against a SQL data source, and your custom comparer can't be converted to SQL.

     

    Here is the way to make it work:  Add ToList() to pull the data from the database

     

    IEnumerable<Search> myDistinctSearches = var mySearches = context.Searches
       .Where(s => s.resultCount > 0)
       .OrderByDescending(s => s.dateCreated)
       .ToList()

       .Distinct(new MyComparer())

       .Take(5);

    • Proposed as answer by vgarcia330 Monday, May 03, 2010 2:50 PM
    Friday, October 24, 2008 10:38 PM
  • Thanks, it makes sense,  
    but making it type IEnumerable<Object> from var fixes the problem already. I implemented it on a different model without .ToList() and it works. 

    haha, i dont think there is much sense for this thread to continue.
    Friday, October 24, 2008 11:23 PM
  • Hi

    I write a sample of distinct method here:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=4017068&SiteID=1

    when you use IEqualityComparer you have to fetch data in memory and then you can use distinct method.
    the way is using ToList() method to load data immediately.
    Sunday, October 26, 2008 11:10 AM
  • Setting it  Add ToList() to pull the data from the database worked for me.

     

    In my example I wanted to keep it as queryable, so I set it to AsEnumerable() then to Distinct(new CompareID()) and finally to AsQueryable();

    Here is my code example:

    QueryableSource = context.AsEnumerable().Distinct(new MyComparer()).AsQueryable();

    Monday, May 03, 2010 2:56 PM
  • That means you have to pull in all the data in memory first and then run Distinct() on in memory collection. In case of large data I might not want to get all those duplicate data in the first place!
    Wednesday, August 17, 2011 7:23 PM
  • I've create a extension method.

     public static IEnumerable<TSource> DistinctBy<TSource, TKey>(this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)
            {
                var seenKeys = new HashSet<TKey>();
                return source.Where(element => seenKeys.Add(keySelector(element)));
            }
    
    usage:
    var distinct = ObjectContext.ObjectSet.DistinctBy(e=>e.Id);
    grtz Vincent
    Tuesday, September 13, 2011 6:13 PM
  • Vincent, that is awesome!! Works most excellently!

    To others who have the need, here's an example of Vincent's extension method in action:

    Hypothetical situation: You are tracking what kind of animals exist in certain locations, but you only want a single occurence of a monkey in a unique location where a single location could have more than one collection of the same animal:

    var distinctAnimalsByLocation = (from itm in db.Items where itm.AnimalDesc == "Monkey" select item).DistinctBy(e => e.Location);

     

    Monday, September 26, 2011 11:17 PM