none
LINQ select distinct c# RRS feed

  • 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

    Thursday, July 26, 2012 4:44 PM

Answers

  • 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".

    Thursday, July 26, 2012 5:54 PM

All replies

  • 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".

    Thursday, July 26, 2012 5:54 PM
  • 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

    Thursday, July 26, 2012 6:07 PM
  • 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".

    Thursday, July 26, 2012 6:25 PM
  • Are you trying to take the distinct IdUser from the Query output ?

    With Best Regards, Boovendan M

    Friday, July 27, 2012 8:54 AM
  • 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())


    • Proposed as answer by Sylvie Smith Thursday, April 17, 2014 7:33 PM
    Saturday, May 18, 2013 12:40 PM
  • I think this is a better solution the previous .ToList() cause enumerating the list is not necessarily what is optimal. It is preferable to let the SQLServer perform the Grouping / Distinct and not the calling program.
    Wednesday, February 5, 2014 9:37 PM
  • The simplest way to go about this, because this may happen in many various ways is to write LINQ about this query

    SELECT tb2.* FROM dbo.SomeTable tb2
    JOIN (SELECT DISTINCT tb.Id FROM dbo.SomeTable tb) tb1 on tb1.Id = tb2.Id

    This is usually not slower than SELECT DISTINCT tb2.* FROM dbo.SomeTable tb2

    Linq may look like:

                var tbsDistinct = (from tb in Model.SomeTable
                                    select tb.Id)
                                    .Distinct();

                return from tbsdst in tbsDistinct
                    join tb in Model.SomeTable on tbsdst equals tb.Id
                    select tb;

    Monday, March 6, 2017 1:09 PM
  • 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".

    THIS IS NOT GOOG AS THE DISTINCT WILL RUN ON THE CLIENT CODE AFTER ALL DUPLICATE ROWS ARE RETURNED FROM THE DATABASE.

    Marios Tsiakiris

    Monday, May 6, 2019 10:12 AM