EF Core 2, .NET CORE 2 :How do I query the same column for multiple conditions using IQueryable<T>?

Question

I have a database table similar to the one in below image

Table with 2 columns (UserId and value)

I will be passing the UserId along with 2 strings. For Ex: userId: 1, key1: h1, key2: h2 to an APi with a similar signature.

Public List<T> CheckValuesForUser(string userId, string key1, string key2)

I need to check if the particular userId 1 has both h1 and h2 values. If the User doesnt have both the Keys then the query should return null.

I have tried the below queries,

1. context.tableName.where(i=> i.value.Equals("h1") && i.value.Equals("h2")) -- this returns nothing. I assume this predicate is executed for each row.
2. Context.tableName.Where(i=> new string[]{ Key1,Key2 }.Contains(i.Value)) -- This returns a value even if the user does not have a value for Key2. I need to get a NULL result if the user doesnt have both the keys.
3. var list1= Context.tableName.Where(i=> i.Value.Equals(key1)).toList(); var list2= Context.tableName.Where(i=> i.Value.Equals(Key2)).toList(); if(list1.Count > 0 && list2.Count > 0) list1.AddRange(list2);

Can someone please help me find a better solution? I am using Asp.Net Core 2.2

Answer 1

Because the h1 & h2 entries are separate records, you will need to check for both of the values independently.

// Group the records by the expected UserId
var groupedRecords = context
                     .TableName
                     .Where(t => t.UserId == userId)
                     .GroupBy(t => t.UserId);

// Ensure both the required records exist within the grouping
var hasRequiredRecords = groupedRecords.Any(i => i.Value.Equals("h1"))
                                        && Any(i => i.Value.Equals("h2"))

// Now that you know you have the required values, return them
return groupedRecords.Where(i => i.Value.Equals("h1") && i.Value.Equals("h2");

The last line will return only the two records with values h1 & h2 for the provided userId provided as a single List<T>