CODEWITHSUNDEEP
c#sql-serverasp.net-mvcgroup-bydistinct
user9562969
user9562969

Reputation:

Distinct based on two fields in sql server: .NET

I am trying to fetch the records that are unique in the table based on the two fields. The table structure is;

enter image description here

The problem I get with the query below;

var dept = db.Departments.Where(x => x.LeadBy == SessionManager.CurrentUser.EmployeeId).FirstOrDefault();

var teams = db.Teams.Where(x => x.DeptID == dept.DepartmentId);

List<Employee> refinedEmpList = new List<Employee>();
var empList = db.Employees.ToList();
foreach (var v in teams)
{
    foreach (var c in empList)
    {
        if (v.TeamID == c.TeamId)
        {
            refinedEmpList.Add(c);
        }
    }
}

var CronRep = db.CronReports.ToList();
List<CronReport> EmpList = new List<CronReport>();
foreach (var v in refinedEmpList)
{
    foreach (var c in CronRep)
    {
        if (v.EmployeeId == c.EmpID)
        {
            EmpList.Add(c);
        }
    }
}

return View(EmpList.Where(x => x.Status != "Present" && x.Status != "Absent" && x.Fine != 0).ToList());

It displays the dup entries too like below;

Dup Entries

I tried changing the query to;

return View(EmpList.Where(x => x.Status != "Present" && x.Status != "Absent" && x.Fine != 0).GroupBy(x => x.EmpID).Select(x => x.First()).ToList());

but this as expected only shows the unique Employees like below;

No dups but incorrect

^Which is obviously not correct as I want all the employees with different datetime but no repeated records.

Upvotes: 1

Views: 82

Answers (2)

miechooy
miechooy

Reputation: 3422

You can use this extensions method for future

   public static IEnumerable<TSource> DistinctBy<TSource, TKey>(this IEnumerable<TSource> source,
                Func<TSource, TKey> keySelector)
            {
                var seenKeys = new HashSet<TKey>();
                foreach (var element in source)
                    if (seenKeys.Add(keySelector(element)))
                        yield return element;
            }

with usage 

collection.DistinctBy(x => new {x.Field1, x.Field2}).ToList();

Upvotes: 0

SpyrosLina
SpyrosLina

Reputation: 131

.GroupBy(x => new { x.Field1, x.Field2} ).Select(x => x.First()).ToList());

Upvotes: 3

Related Questions