How to get distinct values using Linq on the basis of three columns?

Question

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.

Yes, almost try all the solutions i have found here i use Distinct() and GroupBy() also. — Daniiyal Baig, Sep 15 '21 at 04:28
I tried following solutions 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() — Daniiyal Baig, Sep 15 '21 at 04:29
db.SETUP_MASTER_LOGIN .Where(x => x.DELETED == false).GroupBy(n => new {resource_name = s.RESOURCE_NAME, user_name = x.USER_NAME, created_by = x.CREATED_BY}) .Select(s => new MasterLoginResp { resource_name = s.key.resource_name, user_name = x.key.user_name, created_by = x.key.created_by }) — Daniiyal Baig, Sep 15 '21 at 04:33
@DaniiyalBaig you are grouping correctly but you aren't pulling a single result from each group. See my answer for a correct implementation packaged to make this easier to code. — Peter Wone, Sep 15 '21 at 04:36

score 0 · Answer 1 · answered Sep 15 '21 at 03:56

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.

How we can do this using GroupBy() ? – Daniiyal Baig Sep 15 '21 at 04:11 — Daniiyal Baig, Sep 15 '21 at 04:11

Peter Wone · Answer 2 · 2021-09-15T04:32:45.200

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.

How to get distinct values using Linq on the basis of three columns?

2 Answers2