CODEWITHSUNDEEP
c#asp.netgridviewlinq-to-sql
user3483341
user3483341

Reputation: 43

Filter Redundant Data in LINQ

I have a View that retrieves 2 or more same data in LNameByFName, displaying the same names. How can I display only 1 one these names regardless of its index?

I can use FirstOrDefault() but I already have .ToList()

public IEnumerable<EmployeeWithEmail> GetAllEmployeesWithEmail(int x)
{
      using (var context = new SQL_TA_SCOREBOARDEntities1())
      {
        return (from ea in context.View_1
                join vh in context.View_2 on (Int16)ea.EmpNo equals vh.EmpNo
                join rl in context.View_3 on ea.RoleID equals rl.id into outer_join
                from subjoin in outer_join where ea.GroupID == x
                select new EmployeeWithEmail
                {

                    EmpNum = ea.EmpNo ?? 0,
                    Role = subjoin.Role,
                    EmailAddress = vh.EmailAddress,
                    LNameByFname = vh.LNameByFName,
                    Active2 = ea.Active ?? false

                }).ToList();

    }
}

Upvotes: 2

Views: 203

Answers (3)

Grant Winney
Grant Winney

Reputation: 66459

If you want to keep the "group" inline, using your existing query syntax, try this:

return (from ea in context.View_1
        join vh in context.View_2 on (Int16)ea.EmpNo equals vh.EmpNo
        join rl in context.View_3 on ea.RoleID equals rl.id into outer_join
            from subjoin in outer_join where ea.GroupID == x
        group new
        {
            ea.EmpNo,
            subjoin.Role,
            vh.EmailAddress,
            vh.LNameByFName,
            ea.Active
        } by vh.LNameByFName into grp
        let item = grp.FirstOrDefault()
        select new EmployeeWithEmail
        {
            EmpNum = item.EmpNo ?? 0,
            Role = item.Role,
            EmailAddress = item.EmailAddress,
            LNameByFname = item.LNameByFName,
            Active2 = item.Active ?? false
        }).ToList();

FYI, when you use FirstOrDefault(), you generally want to test for null too, something like EmpNum = (item != null ? item.EmpNo ?? 0 : -1),. Otherwise you may get a NullReferenceException.

But I think your safe in this case... I can't see how a "group" wouldn't have at least one item in it.

Upvotes: 3

ChaseMedallion
ChaseMedallion

Reputation: 21764

To eliminate duplicates, use GroupBy followed by Take:

   IQueryable<EmployeeWithEmail> query = ...;
   // group by the value you want to be unique
   var queryWithoutDuplicates = query.GroupBy(e => e.LNameByFName)
       // then select the first (arbitrary since there's no order) member of each group
       .SelectMany(g => g.Take(1))
       .ToList();

If you have already pulled the results into memory, you can also use the overload of Distinct() that takes an IEqualityComparer:

 // AsEnumerable() pulls us into memory, where we can use EqualityComparers
 var queryWithoutDuplicates = query.AsEnumerable()
     // do a distinct using a comparer based on the key you want to be unique
     .Distinct(EqualityComparers.Create((EmployeeWithEmail e) => e.LNameByFName))
     .ToList();

EqualityComparers.Create is defined at: http://www.codeducky.org/10-utilities-c-developers-should-know-part-two/.

Upvotes: 1

Jordan S. Jones
Jordan S. Jones

Reputation: 13893

After your select and before your .ToList() call .Distinct() passing an IEqualityComparer.

Upvotes: -1

Related Questions