[c#] Using .Select and .Where in a single LINQ statement

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?

This question is related to c# linq unique distinct

The answer is

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 :)

Source: Stackoverflow.com

Examples related to c#

• How can I convert this one line of ActionScript to C#? • Microsoft Advertising SDK doesn't deliverer ads • How to use a global array in C#? • How to correctly write async method? • C# - insert values from file into two arrays • Uploading into folder in FTP? • Are these methods thread safe? • dotnet ef not found in .NET Core 3 • HTTP Error 500.30 - ANCM In-Process Start Failure • Best way to "push" into C# array

Examples related to linq

• Async await in linq select • How to resolve Value cannot be null. Parameter name: source in linq? • What does Include() do in LINQ? • Selecting multiple columns with linq query and lambda expression • System.Collections.Generic.List does not contain a definition for 'Select' • lambda expression join multiple tables with select and where clause • LINQ select one field from list of DTO objects to array • The model backing the 'ApplicationDbContext' context has changed since the database was created • Check if two lists are equal • Why is this error, 'Sequence contains no elements', happening?

Examples related to unique

• Count unique values with pandas per groups • Find the unique values in a column and then sort them • How can I check if the array of objects have duplicate property values? • Firebase: how to generate a unique numeric ID for key? • pandas unique values multiple columns • Select unique values with 'select' function in 'dplyr' library • Generate 'n' unique random numbers within a range • SQL - select distinct only on one column • Can I use VARCHAR as the PRIMARY KEY? • Count unique values in a column in Excel

Examples related to distinct

• Using DISTINCT along with GROUP BY in SQL Server • How to "select distinct" across multiple data frame columns in pandas? • Laravel Eloquent - distinct() and count() not working properly together • SQL - select distinct only on one column • SQL: Group by minimum value in one field while selecting distinct rows • Count distinct value pairs in multiple columns in SQL • sql query distinct with Row_Number • Eliminating duplicate values based on only one column of the table • MongoDB distinct aggregation • Pandas count(distinct) equivalent