Returning a Distinct IQueryable with LINQ?

Question

I'm writing a Function that pulls Records from a DataBase using LINQ to get an IQueryable. This LINQ statement will pull all of the records for Active users within a certain time period, and then spit the UserID, First Name, and Last Name out to a Telerik RadGrid.

My problem lies within trying to get a Distinct Value on the UserID when pulling this Data. I have tried re-working this code to get my result. Here is the an example of the code that is pulling all of the Data, with the Distinct NOT working.

public static IQueryable GetActiveEmployees_Grid(string Period)
{
    DataContext Data = new DataContext();
    var Employees = (from c in DataSystem_Records
                     where c.Period == Period
                     orderby c.DataSystem_Employees.LName
                     select c).Distinct();

    return Employees;
}

After applying the DataSource to my Grid, this returns the User 4 times, one instance for each Record for that Period.

Is there a way to apply Distinct to my LINQ Function to make this work the way I intend it to?

Answer 1

Simplest way I have found to do this with object is using the groupby then selecting the first.

public static IQueryable GetActiveEmployees_Grid(string Period)
{
    DataContext Data = new DataContext();
    var Employees = (from c in DataSystem_Records
                     where c.Period == Period
                     orderby c.DataSystem_Employees.LName
                     select c).GroupBy(g=>g.DataSystem_Employees.AccID).Select(x=>x.FirstOrDefault());

    return Employees;
}

This is not tested but the general concept is there.

Edit: I remembered originally finding the answer somewhere on here. Check out this for grouping objects by a certain property. LINQ's Distinct() on a particular property

Answer 2

If you limit the objects you are returning to only the fields that you want to display, it will work properly.

public static IQueryable GetActiveEmployees_Grid(string Period)
{
    DataContext Data = new DataContext();
    var Employees = (from c in DataSystem_Records
                     where c.Period == Period
                     orderby c.DataSystem_Employees.LName
                     select c.DataSystem_Employees.FName, 
                            c.DataSystem_Employees.LName, 
                            c.ID).Distinct();

    return Employees;
}

Answer 3

try and write an IEqualityComparer<T> for the object type being selected and use it in your Distinct method

Answer 4

Making some assumptions around the names of the various fields in the objects:

public static IQueryable GetActiveEmployees_Grid(string Period)
{
    DataContext Data = new DataContext();
    var Employees = (from c in DataSystem_Records
                     where c.Period == Period
                     orderby c.DataSystem_Employees.LName
                     select new { FirstName = c.DataSystem_Employees.FName, LastName = c.DataSystem_Employees.LName, ID = c.DataSystem_Employees.ID }).Distinct();

    return Employees;
}

To follow the MVC pattern you might want to lift this query into your Model and return a specific class that contains those fields.

Answer 5

Use the Distinct() method to do this. Eg:

var query = from Notification in db.Notifications

                        select Notification.client ;
            query=query.Distinct();

The resulting query will only contain distinct values.

Answer 6

The problem is you are pulling back fields that will make each row distinct. Like sgriffinusa said, only pull back the 3 values you are displaying.

Answer 7

You might want to implement a custom comparer for Distinct method. Please refer to a previous SO question here.