How to get distinct values using Linq on the basis of three columns? [duplicate]-CodePudding

var result =  db.SETUP_MASTER_LOGIN
                .Where(x => x.DELETED == false)
                .Select(s => new MasterLoginResp
                             {
                                 resource_name = s.RESOURCE_NAME, 
                                 user_name = x.USER_NAME, 
                                 created_by = x.CREATED_BY
                             })
                .ToList()

How to get distinct values from selected three columns? I have tried all the given solutions but did not get the required results.

CodePudding user response：

I assume that MasterLoginResp is DTO object.

public class MasterLoginResp
{
   public string resource_name {get;set;}
   public string user_name {get;set;}
   public string created_by {get;set;}
   
   public override int GetHashCode()
   {
     return (resource_name   user_name   created_by).GetHashCode();
   }
}

Now just use the way you do.

var result =  db.SETUP_MASTER_LOGIN.Where(x => x.DELETED == false)
.Select(s => new MasterLoginResp{
 resource_name = s.RESOURCE_NAME, 
 user_name = x.USER_NAME, 
 created_by = x.CREATED_BY
}).Distinct().ToList();

Note: This is client-side evaluation so it will bring all data from server.

CodePudding user response：

This is an outstanding question. The Distinct method ought to optionally take a lambda function like First(), Count() and others.

The source code is public. Examining it and looking at how one would do this so as to remain IQueryable for delegation to the server where possible, we arrive at this code.

using System;
using System.Collections.Generic;
using System.Linq;

public static class ExtendLinq
{
  /// <summary>
  /// Performs the Distinct operation on properties 
  /// defined using a lambda expression that returns 
  /// an object.
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <param name="list"></param>
  /// <param name="propertySelector"></param>
  /// <returns></returns>
  public static IEnumerable<T> Distinct<T>(this IEnumerable<T> list, Func<T, object> propertySelector)
  {
    return list.GroupBy(propertySelector).Select(x => x.First());
  }
}

Alas, First returns IEnumerable<T> scuttling hopes of delegation.

We can then apply this.

var result =  db.SETUP_MASTER_LOGIN
                .Where(x => x.DELETED == false)
                .Distinct(s => new 
                             {
                                 s.RESOURCE_NAME, 
                                 x.USER_NAME, 
                                 x.CREATED_BY
                             })
                .Select(s => new MasterLoginResp
                             {
                                 resource_name = s.RESOURCE_NAME, 
                                 user_name = x.USER_NAME, 
                                 created_by = x.CREATED_BY
                             })
                .ToList()

Given that you are already reducing the result to the columns on which you want to apply distinct, you might think you could just use Distinct() without parameters. However, this may directly compare the objects. You'll have to be careful with that. My method will always work due to the implementation.

Notice that I apply Distinct before Select reduces. This allows it to operate on the full gamut of properties, not just the ones you return from Select.