Using .Select and .Where in a single LINQ statement

Question

I need to gather Distinct Id's from a particular table using LINQ. The catch is I also need a WHERE statement that should filter the results based only from the requirements I've set. Relatively new to having to use LINQ so much, but I'm using the following code more or less:

private void WriteStuff(SqlHelper db, EmployeeHelper emp)
{
    String checkFieldChange;
    AnIList tableClass = new AnIList(db, (int)emp.PersonId);
    var linq = tableClass.Items
        .Where(
           x => x.UserId == emp.UserId 
             && x.Date > DateBeforeChanges 
             && x.Date < DateAfterEffective 
             && (
                     (x.Field == Inserted)
                  || (x.Field == Deleted)))
                )
             ).OrderByDescending(x => x.Id);

    if (linq != null)
    {
        foreach (TableClassChanges item in linq)
        {
            AnotherIList payTxn = new AnotherIList(db, item.Id);
            checkFieldChange = GetChangeType(item.FieldName);

            // Other codes that will retrieve data from each item 
            // and write it into a text file
        }
    }
}

I tried to add .Distinct for var linq but it's still returning duplicate items (meaning having the same Id's). I've read through a lot of sites and have tried adding a .Select into the query but the .Where clause breaks instead. There are other articles where the query is somehow different with the way it retrieves the values and place it in a var. I also tried to use .GroupBy but I get an "At least one object must implement IComparable" when using Id as a key.

The query actually works and I'm able to output the data from the columns with the specifications I require, but I just can't seem to make .Distinct work (which is the only thing really missing). I tried to create two vars with one triggering a distinct call then have a nested foreach to ensure the values are just unique, but will thousands of records to gather the performance impact is just too much.

I'm unsure as well if I'd have to override or use IEnumerable for my requirement, and thought I'd ask the question around just in case there's an easier way, or if it's possible to have both .Select and .Where working in just one statement?

Answer 1

In order for Enumerable.Distinct to work for your type, you can implement IEquatable<T> and provide suitable definitions for Equals and GetHashCode, otherwise it will use the default implementation: comparing for reference equality (assuming that you are using a reference type).

From the manual:

The Distinct(IEnumerable) method returns an unordered sequence that contains no duplicate values. It uses the default equality comparer, Default, to compare values.

The default equality comparer, Default, is used to compare values of the types that implement the IEquatable generic interface. To compare a custom data type, you need to implement this interface and provide your own GetHashCode and Equals methods for the type.

In your case it looks like you might just need to compare the IDs, but you may also want to compare other fields too depending on what it means for you that two objects are "the same".

You can also consider using DistinctBy from morelinq.

Note that this is LINQ to Objects only, but I assume that's what you are using.

Yet another option is to combine GroupBy and First:

 var query = // your query here...
    .GroupBy(x => x.Id)
    .Select(g => g.First());

This would also work in LINQ to SQL, for example.