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

Did you add the Select() after the Where() or before?

You should add it after, because of the concurrency logic:

 1 Take the entire table    2 Filter it accordingly    3 Select only the ID's    4 Make them distinct.   

If you do a Select first, the Where clause can only contain the ID attribute because all other attributes have already been edited out.

Update: For clarity, this order of operators should work:

db.Items.Where(x=> x.userid == user_ID).Select(x=>x.Id).Distinct(); 

Probably want to add a .toList() at the end but that's optional :)