CODEWITHSUNDEEP
c#linqlist
Dody
Dody

Reputation: 19

How to find all items in a list where a property has a specific value or where there is a related item with this value?

How to get(or filter) list where there are record with the same userID and artistID

Here is my rating object

public class Rating
{
    public int userID { get; set; }
    public int artistID { get; set; }
    public int rating { get; set; }
}

and here is my data

Rating rate1 = new Rating { artistID = 1, userID = 101, rating = 2 };
Rating rate2 = new Rating { artistID = 1, userID = 102, rating = 4 };
Rating rate3 = new Rating { artistID = 2, userID = 101, rating = 3 };
Rating rate4 = new Rating { artistID = 2, userID = 102, rating = 5 };
Rating rate5 = new Rating { artistID = 3, userID = 102, rating = 1 };

List<Rating> ratings = new List<Rating>(2);
ratings.Add(rate1);
ratings.Add(rate2);
ratings.Add(rate3);
ratings.Add(rate4);
ratings.Add(rate5);

Output if there ara record : where userID = 101, and where (artistID where userID is 101)

Output example that i want to have: 

artistID    userID      rating
1           101         2
1           102         4
2           101         3
2           102         5

I want also 1, 102, 4 because there is another rating with this artistID which userID is 101. The same applies to 2, 102, 5.

Update If You want to include all records where there is another record with a different user but the same artist (quote from @Tim Schmelter). You can found answer from @Tim Schmelter in Update version.

For example, if u change rate5 to Rating rate5 = new Rating { artistID = 3, userID = 101, rating = 1 }; and also add new object rate6 Rating rate6 = new Rating { artistID = 3, userID = 102, rating = 1 };

It will make result:

artistID    userID      rating
1           101         2
1           102         4
2           101         3
2           102         5
3           101         1
3           102         1

Because artistID that rated by userID-101 also rated by userid-102 you can found answer in @Tim Schmelter answer.

Upvotes: 1

Views: 280

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460108

So you want all ratings where either the userID is 101 or there is one with the same artistID that has userID = 101? You can use Any in Where:

var query = ratings.Where(r =>  r.userID  == 101 || 
              ratings.Any(rr => rr.userID == 101 && r.artistID == rr.artistID));

This is similar to a correlated subquery in sql.

Test:

foreach (Rating r in query)
    Console.WriteLine("artistID = {0}, userID = {1}, rating = {2}"
                   , r.artistID, r.userID, r.rating);

Result:

artistID = 1, userID = 101, rating = 2
artistID = 1, userID = 102, rating = 4
artistID = 2, userID = 101, rating = 3
artistID = 2, userID = 102, rating = 5

Update "i want all records where there is another record with a different user but the same artist"

Now it's clear, you want this:

var query = ratings
    .Where(r => ratings
        .Any(rr => r.artistID == rr.artistID && rr.userID != r.userID));

Upvotes: 4

Related Questions