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 Usuers 
                 {  
                    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 does not work?

---

CADAVID

Answer 1

Hi CADAVID;

Please try this query out as is.

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


// this class is used to compare two objects of type Usuers to remove 
// all objects that are duplicates only by field IdUser
public class UsuersComparer : IEqualityComparer<Usuers>
{
    public bool Equals(Usuers x, Usuers y)
    {
        if (x.IdUser == y.IdUser)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    public int GetHashCode(Usuers obj)
    {
        return obj.IdUser.GetHashCode();
    }
}

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 2

this is the error I get:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[REPORTMVC3.Models. Usuers] Distinct[Usuers](System.Linq.IQueryable`1[REPORTMVC3.Models. Usuers], System.Collections.Generic.IEqualityComparer`1[REPORTMVC3.Models. Usuers])' Method cannot be translated into a store expression.

Obtengo ese error Fernando, gracias por intentar ayudarme. que debo hacer?

---

CADAVID

Answer 3

Hi CADAVID;

That is the very same message I would expect to get if you removed the ".ToList()" method from the query I posted.

// This will cause the above error
}).Distinct(new UsuersComparer);

// This should not cause that error
}).ToList().Distinct(new UsuersComparer);

The reason why the first will cause the error is that Distinct using an EqualityComparer to Linq to SQL or Linq to EF is not supported. In the second case above ToList method causes the query to execute without the Distinct and then with the results applies the Distinct and therefore Entity Framework is not involved. 

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 4

Are you trying to take the distinct IdUser from the Query output ?

---

With Best Regards, Boovendan M

Answer 5

You can use this query

var sql= (from u in db.USER
          join c in db.CONSULT on u.IdUser
 equals c.IdUser
 
          select new Usuers 
                 {  
                    IdUser = c.IdUser, 
                    DateCreate=c.DateCreate, 
                    IdTypeConsult = c.IdTypeConsult, 
                    Sex=u.Sex 
                 })
.GroupBy(g => new { g.IdUser,g.DateCreate,g.IdTypeConsult,g.Sex })
.Select(s => s.FirstOrDefault())