LINQ select distinct c#

Question

I'm trying to do a query that does not include repeated IdUser values, ​​but does not work.

this is my linq query:

var sql= (from u in db.USER
          join c in db.CONSULT on u.IdUser equals c.IdUser 
          select new UsuersViewModel 
                 {  
                    IdUser = c.IdUser, 
                    DateCreate=c.DateCreate, 
                    IdTypeConsult = c.IdTypeConsult, 
                    Sex=u.Sex 
                 })
                 .Distinct();

I want this:

SELECT   distinct CONSULT.IdUser , CONSULT.DateCreate, 
         CONSULT.IdTypeConsult , USER.Sex
FROM   CONSULT INNER JOIN
       USER ON CONSULT.IdUser = USER.IdUser 

The query give duplicated records

Why doesn't it work?

Answer 1

I think you want to use the Distinct(IEqualityComparer<T>) overload. You need to create an IEqualityComparer to do what you want:

class UserComparer : IEqualityComparer<UsuersViewModel >
{
    public bool Equals(UsuersViewModel  x, UsuersViewModel y)
    {
        //Check whether the compared objects reference the same data.
        if (Object.ReferenceEquals(x, y)) return true;

        //Check whether any of the compared objects is null.
        if (Object.ReferenceEquals(x, null) || Object.ReferenceEquals(y, null))
            return false;

        return x.IdUser == y.IdUser;
    }

    // If Equals() returns true for a pair of objects 
    // then GetHashCode() must return the same value for these objects.

    public int GetHashCode(UsuersViewModel  user)
    {
        //Check whether the object is null
        if (Object.ReferenceEquals(user, null)) return 0;

        return user.IdUser == null ? 0 : user.IdUser.GetHashCode();
    }
}

Then use it like this:

var comparer = new UserComparer();
var sql= (from u in db.USER
          join c in db.CONSULT on u.IdUser equals c.IdUser 
          select new UsuersViewModel 
                 {  
                    IdUser = c.IdUser, 
                    DateCreate=c.DateCreate, 
                    IdTypeConsult = c.IdTypeConsult, 
                    Sex=u.Sex 
                 })
                 .Distinct(comparer);

I'm not sure if that will generate the SQL you want, but will likely get the results you want.

Answer 2

When comparing class instances (vs. anonymous types) you need to define "equality". For anonymous types the compiler assumes that equality means "all fields are equal" like SQL does. So you have a few choices:

1. Use an anonymous type in your query, use .Distinct(), and convert to a strong type afterwards,
2. Define an IEqualityComparer<Usuers> class and pass that to Distinct,
3. Override Equals (and GetHashCode) in Usuers

2) and 3) will be very similar code. 2) is more flexible (you can define equality in different ways by defining different classes, while 3) will be used whenever you compare Uusers insatnces (not just in this query).

See my answer to a similar problem here.

Answer 3

Note: The following can be run in LinqPad (free at http://www.linqpad.net/) - simply set the Language dropdown to "C# Program" and paste the code into the editor window.

You can use "group" to provide your distinct requirement as follows:

void Main()
{
    var db = new DataBase();
    var sql= (from u in db.USER
          join c in db.CONSULT on u.IdUser equals c.IdUser 
          group c by new { c.IdUser, c.DateCreate, c.IdTypeConsult, u.Sex } into gc
          select new UsuersViewModel 
                 {  
                    IdUser = gc.Key.IdUser, 
                    DateCreate=gc.Key.DateCreate, 
                    IdTypeConsult = gc.Key.IdTypeConsult, 
                    Sex=gc.Key.Sex 
                 })
                 .Distinct();
    sql.Dump("SQL Distinct Demo");
}
public class Consultation {
    public int  IdUser {get;set;}
    public DateTime DateCreate {get;set;}
    public int IdTypeConsult {get;set;}
}
public class UsuersViewModel : Consultation {
    public string Sex {get;set;}
}
public class DataBase {
    public IEnumerable<Consultation> CONSULT {
        get { 
            return new List<Consultation>{
                new Consultation { IdUser = 1, DateCreate=DateTime.Today, IdTypeConsult = 2},
                new Consultation { IdUser = 2, DateCreate=DateTime.Today.AddDays(1), IdTypeConsult = 4}
            };
        }}
    public IEnumerable<UsuersViewModel> USER {
        get {
            return new List<UsuersViewModel>{
                new UsuersViewModel { IdUser = 1, Sex="M"},
                new UsuersViewModel { IdUser = 1, Sex="M"},
                new UsuersViewModel { IdUser = 2, Sex="F"},
                new UsuersViewModel { IdUser = 2, Sex="F"}
            };
        }}
}