How to get Unique records via LINQ

Question

var EmpRecList = (from ur in db.Users
  join ug in db.UserGroups on ur.UserGroupID equals ug.UserGroupID
  select new
  {
      lastName = ur.LastName, 
      userID = ur.UserID,
      firstName = ur.FirstName,
      userGroupName = ug.UserGroupNameLang1
  }).Where(oh => oh.userGroupName.StartsWith(userCur.UserGroupName))
                                  .OrderBy(x => x.lastName);

I have this code snippet. The problem here is, im getting 2 records with the same user ID. I would like to have a distinct record based on the User ID. Thanks. Tried using distinct method but no success.

If they are not truly distinct, how do you determine which one you want? — Hardrada, Nov 08 '12 at 13:42
What record are you expecting to have, if user linked to several groups? — Sergey Berezovskiy, Nov 08 '12 at 13:44
You can use DistinctBy(), but it works only with objects (LINQ to Objects) like shown here http://stackoverflow.com/questions/2537823/distinct-by-property-of-class-by-linq. So you could use it after calling ToArray() or ToList() — Mikolaj Kieres, Nov 08 '12 at 13:47
thanks for the comments. LINQ to Objects idea is also quite helpful. — JYR, Nov 08 '12 at 23:31

score 1 · Accepted Answer · edited Nov 08 '12 at 23:42

You can use GroupBy and than get First record. It will get the first record which is in EmpRecList according to userid after ordering, but it will not ensure the result which you want to get.

Try this

var EmpRecList = (from ur in db.Users
                  join ug in db.UserGroups on ur.UserGroupID equals ug.UserGroupID
                  select new
                  {
                      lastName = ur.LastName, 
                      userID = ur.UserID,
                      firstName = ur.FirstName,
                      userGroupName = ug.UserGroupNameLang1
                   })
                  .Where(oh => oh.userGroupName.StartsWith(userCur.UserGroupName))
                  .GroupBy(g => g.userID).Select(s => s.First()).ToList().OrderBy(x => x.lastName)

The problem here is that the results will vary depending on the ordering of the data. It will give inconsistent results. — Dave New, Nov 08 '12 at 13:51

score 0 · Answer 2 · answered Nov 08 '12 at 13:49

The problem here is that you wan't a distinct list of Users BUT your LINQ query is grouping some Users with more than one UserGroups. Performing a distinct on this will not give you a unique list because of different UserGroups.

You need to solve this in your where clause. It needs to be more specific. Instead of your predicate being StartsWith, rather use Equals.

var EmpRecList = (from ur in db.Users
  join ug in db.UserGroups on ur.UserGroupID equals ug.UserGroupID
  select new
  {
      lastName = ur.LastName, 
      userID = ur.UserID,
      firstName = ur.FirstName,
      userGroupName = ug.UserGroupNameLang1
  }).Where(oh => oh.userGroupName.Equals(userCur.UserGroupName))
    .OrderBy(x => x.lastName);

It would actually be better to compare the UserGroups by an ID instead of the name.

i need startswith since it's the same function in SQL "LIKE" — JYR, Nov 08 '12 at 23:32

score 0 · Answer 3 · answered Nov 08 '12 at 13:50

You can skip the join, so a user with more than one group won't appear twice. I Assumed there is no navigation property to groups, but if there is you can just use ur.UserGroups and you don't need the let definition.

var EmpRecList = (from ur in db.Users
  let groups = db.UserGroups.Where(ug => ur.UserGroupID == ug.UserGroupID)
  select new
  {
      lastName = ur.LastName, 
      userID = ur.UserID,
      firstName = ur.FirstName,
      userGroupNames = groups.Select(g => g.UserGroupNameLang1)
  }).Where(oh => oh.userGroupNames.Any(n => n.StartsWith(userCur.UserGroupName)))
                                  .OrderBy(x => x.lastName);

How to get Unique records via LINQ

3 Answers3