LINQ querying nested dictionary with multiple key value selection in C#

Question

I am facing an issue and cannot find any suitable solution for it. I have a nested dictionary that has a layout like this:

var dict = new Dictionary<int, Dictionary<string , dynamic>>()
{
 0 : {Name : "abc" , marks1 : 05 , Marks2 : 10 , marks3 : 39},
 1 : {Name : "efg" , marks1 : 20 , Marks2 : 30 , marks3 : 25},
 2 : {Name : "hig" , marks1 : 30 , Marks2 : 40 , marks3 : 33},
 3 : {Name : "xyz" , marks1 : 40 , Marks2 : 10 , marks3 : 50}
}

I am trying to query using LINQ to get the id of those queries, which are true but I am unable to select two or three criteria for a query. For one key-value the below query works fine:

    var query = from id in dict
                    from info in id.Value
                    where (info.Key == "marks1" && Convert.ToDouble(info.Value) > 10)
                    select id

But when I try 2 or 3 criteria to get the id of row for e.g below it does not work.

    var query = from id in dict
                    from info in id.Value
                    where (info.Key == "marks1" && Convert.ToDouble(info.Value) > 10) 
                    && (info.Key == "marks2" && Convert.ToDouble(info.Value) > 20)
                    select id

Also if I have to compare the values with each other like values of marks1 should be greater the mark2 and mark3. How can I execute such query? Especially when I am not allowed to access value by key like this:

info["marks1"] > ( info["mark2"] && info["marks3"] )

in LINQ as it gives me the following error:

cannot apply indexing with [] to an expression of type 'keyvaluepair < string , dynamic>

Regarding the first part of your question (using 2 or 3 criteria): is it a logical issue? You're requesting info.Key == "marks1" && info.Key == "marks2", but you probably want info.Key == "marks1" || info.Key == "marks2" (mind the Logical OR) — mu88
– mu88, Commented Mar 3, 2020 at 12:34

Krishna Varma · Accepted Answer · 2020-03-03 12:43:07Z

2

I tried this and it worked

var result = dict.Where(x => x.Value.Any(y => y.Key == "marks1" && int.Parse(y.Value) > 10))
                 .Where(x => x.Value.Any(y => y.Key == "marks2" && int.Parse(y.Value) > 31));

answered Mar 3, 2020 at 12:43

Krishna Varma

4,2802 gold badges12 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Talha Kazi Over a year ago

Thankyou works fine..any idea how we can do this in query format of linq

Pavel Anikhouski Over a year ago

@TalhaKazi It doesn't work because info.Key can't be marks1 and Marks2 at the same time. You can do it with query syntax as well, as I've mentioned in my answer

Anu Viswan · Accepted Answer · 2020-03-03 12:47:36Z

2

You could use

var result= dict.Where(x=>Convert.ToDouble(x.Value["marks1"])>10 
                       && Convert.ToDouble(x.Value["Marks2"])>20)
                .Select(x=>x.Key);

Output

1 
2

answered Mar 3, 2020 at 12:47

Anu Viswan

18.2k4 gold badges27 silver badges53 bronze badges

Comments

Harald Coppoolse · Accepted Answer · 2020-03-03 13:02:03Z

Outer from:
dict is a sequence of KeyValuePairs, where the Key is an int, and Value is the inner Dictionary<string, dynamic>. Therefore, id is one element from this sequence of KeyValuePairs: it is one such a KeyValuePair.

Inner from:
id.Value is a Dictionary<string, dynamic>, which is a string of KeyValuepairs where every Key is a string, and every Value a dynamic.

You don't want all KeyValuePairs from the inner dictionary, you only want some of them:

where (info.Key == "marks1" && Convert.ToDouble(info.Value) > 10) 
   && (info.Key == "marks2" && Convert.ToDouble(info.Value) > 20)

If I read this, you only want those KeyValuePairs from the inner dictionary with a string Key that is equal to "marks1" as well as equal to "marks2". Are you sure you want that? It seems to me that the result will be an empty collection.

Alas, you didn't write your requirements, so I'll have to guess.

I think that from every KeyValuePair element from your outer dictionary you want the Key (which is the int from your outer dictionary), as well all as a sequence of all KeyValuePairs from the inner dictionary that have

either (Key == "marks1" AND Convert.ToDouble(info.Value) > 10)
OR (Key == "marks2" AND Convert.ToDouble(info.Value) > 20)

I'm not really familiar with query syntax, so I'll use methodSyntax instead.

Dictionary<int, Dictionary<string, dynamic>> dict = ...;
var result = dict.Select( outerKeyValuePair => new
{
    Key = outerKeyValuePair.Key,
    Values = outerKeyValuePair.Value
        // I don't want all values from the inner dictionary
        // I only want those where (Key == "marks1" and ...) OR (Key == "marks2 and ...)
        .Where(innerKeyValuePair => 
              (innerKeyValuePair.Key == "marks1" && Convert.ToDouble(innerKeyValuePair.Value) > 10)
              ||
              (innerKeyValuePair.Key == "marks2" && Convert.ToDouble(innerKeyValuePair.Value) > 20))

         // from the remaining inner KeyValuePairs select the properties that you want
         .Select(innerKeyValuePair => new
         {
              Name = innerKeyValuePair.Key,
              Value = innerKeyValuePair.Value,
         })
         .ToList(),
    });

In words: your outer dictionary, is a sequence of outer KeyValuePairs. From every keyValuePair in this sequence, make one new object, containing two properties: Key and Values.

The Key property is the Key from your KeyValuepair.

The Values properties is a list. It contains objects made from the innner dictionary that belongs to the value of Key from the outer dictionary.

This inner dictionary is also a sequence of KeyValuePairs. I only keep some of these inner KeyValuePairs, namely only those where:

Key equals "marks1" AND Convert.ToDouble(value) larger than 10
OR: Key equals "marks2 AND Convert.ToDouble(value) larger than 20

From every remaining KeyValuePair from the inner dictionary, I make one new object containing properties Name and Value.

Name is the Key of the inner KeyValuePair (which equals either "marks1" or "marks2") Value is the Value of this inner KeyValuePair.

Of course if you want other properties, you can Select them instead of the ones I selected:

.Select(innerKeyValuePair => new
{
    Marks = innerKeyValuePair.Key,                     // equals "marks1" OR "marks2"
    Value = Convert.ToDouble(innerKeyValuePair.Value),
})
.ToList(),

Wow, thank you very much for that comprehensive explanation. I have actually converted a data table in dictionary form where each id represents a record and the inner dictionary contains the column as keys and data as values so each id is a record on which i was trying to query

Fred · Accepted Answer · 2020-03-03 13:17:42Z

You could flat the dictionary like this:

    dict.Select(item=> new { 
                            Id = item.Key, 
                            Name = item.Value["Name"],
                            marks1 = Convert.ToDouble(item.Value["marks1"]), 
                            marks2 = Convert.ToDouble(item.Value["marks2"]),
                            marks3 = Convert.ToDouble(item.Value["marks3"]) 
                          }
                 )
           .Where(item=> item.marks1 > 10 && 
                         item.marks2 > 20)
           .Select(item=> item.Id)

This query returns the id of records. If you need full records of selected data, you can use this:

    dict.Select(item=> new { 
                            item = item, 
                            Name = item.Value["Name"],
                            marks1 = Convert.ToDouble(item.Value["marks1"]), 
                            marks2 = Convert.ToDouble(item.Value["marks2"]),
                            marks3 = Convert.ToDouble(item.Value["marks3"]) 
                          }
                 )
           .Where(item=> item.marks1 > 10 && 
                         item.marks2 > 20)
           .Select(item=> item)

Collectives™ on Stack Overflow

LINQ querying nested dictionary with multiple key value selection in C#

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Related