LINQ select separate C #

Question

I am trying to execute a query that does not include duplicate IdUser values ​​but does not work.

this is my linq request:

 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 it:

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

The query gives duplicate records

Why doesn't it work?

Answer 1

I think you want to use the Distinct(IEqualityComparer<T>) overload Distinct(IEqualityComparer<T>) . 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 as follows:

 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 this will generate the SQL you want, but most likely you will get the desired results.

Answer 2

When comparing class instances (compared to anonymous types), you need to define "equality." For anonymous types, the compiler assumes that equality means "all fields are equal", as SQL does. So, you have several options:

• Use an anonymous type in your request, use .Distinct() and subsequently convert to a strong type,
• Define the IEqualityComparer<Usuers> class and go to Distinct .
• Override Equals (and GetHashCode ) in Usuers

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

See my answer to a similar problem here .

Answer 3

Note. You can run the following in LinqPad: http://www.linqpad.net/ ) - just set the Language drop-down menu to "C # Program" and paste the code into the editor window.

You can use the “group” to provide your special requirements 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"} }; }} }