How to filter child table by dictionary

Question

I have a question for you all!!!,

Assume that I have 2 tables, one of them is a parent, and the other is a child. I want to filter the parent table by sending data from the child object. The child table contains 4 columns like

{Id, RuleId, RuleKey, RuleValue}

I send a dictionary as a request that includes value for 2 columns ( RuleKey, RuleValue) of the child object. I want to return the parent object that includes RuleKey and RuleValue I sent.

// Key is the column name of the child table and value is the corresponding value
var dictionary = new Dictionary<string,string>()
{
    {"RuleKey", "RuleValue"},
    {"RuleKey1", "RuleValue1"}
}

This is what I tried but it obviously won't work and still give the syntax error. These 2 questions about this issue;

1-) How should I do this (Important one:)))))
2-) Will be there any error If I do this In-Memory, If I remember that correctly, EFCore won't translate the LINQ to SQL script because of the memory issues.

var rules = _ruleContext.Rules.AsQueryable();
rules = rules.Where(u => u.Criteria.Where(k => dictionary.Keys.Contains(k.Key) && dictionary.Values.Contains(k.Value)));

public class Rule : Entity<int>
{
    public string RuleName { get; set; }
    public string Expression { get; set; }
    public ICollection<Criteria> Criteria { get; set; }
}

public class Criteria : Entity<int>
{
    public int RuleId { get; set; }
    public string Name { get; set; }
    public string Key { get; set; }
    public string Operator { get; set; }
    public string Value { get; set; }

    public Rule Rule { get; set; }
}

Can you show `Rule` and `Criteria` model classes? Do you need as result `rule` which meets all `dictionary` criteria? — Svyatoslav Danyliv, Jan 06 '23 at 15:20
I added class you requested, also the answer is yes. I want to rules list as a result that includes all criteria. @SvyatoslavDanyliv — Sabri Korkmaz, Jan 06 '23 at 15:30

Mike Goatly · Answer 1 · 2023-01-09T14:10:45.533

I'm assuming that you want to only match criteria where both Key and Value match a key/value pair in the dictionary. (Your pseudocode would include any rules where either the key or value matched)

What you're trying to do is join between the Criteria table and the key/values in the dictionary. One way to accomplish this would be to create a temporary table containing those pairs, and then use a raw SQL statement to join from Criteria onto that.

Something like this might work:

// To use the temporary table you need to make sure that the connection stays 
// open beyond the table creation statement
context.Database.OpenConnection();
try
{
    context.Database.ExecuteSqlRaw("CREATE TABLE #RequiredCriteria ([Value] nvarchar(100), [Key] nvarchar(100))");
    
    // If you're expecting LOTs of criteria this could become a bottleneck...
    foreach (var criteria in dictionary)
    {
        context.Database.ExecuteSqlInterpolated($"INSERT INTO #RequiredCriteria ([Value], [Key]) VALUES ({criteria.Key}, {criteria.Value})");
    }

    // This creates Rule objects from a raw SQL query that does 
    // the required joining of parameters
    var matched = context.Rules.FromSqlRaw(
        @"SELECT * FROM Rules 
        WHERE Id IN (
            SELECT DISTINCT RuleId 
            FROM Criteria C 
                INNER JOIN #RequiredCriteria RC 
                    ON C.[Key] = RC.[Key] AND C.[Value] = RC.[Value])")
        .ToList();

    context.Database.ExecuteSqlRaw("DROP TABLE #RequiredCriteria");
}
finally
{
    context.Database.CloseConnection();
}

Edit (now without distinct):

I noticed in your comment that a rule must meet all the given criteria - this version of the query will make sure that any matching rule will have the same number of distinct criteria that were requested. That way if the same criteria key and value is duplicated in the request it won't matter:

// To use the temporary table you need to make sure that the connection stays 
// open beyond the table creation statement
context.Database.OpenConnection();
try
{
    context.Database.ExecuteSqlRaw("CREATE TABLE #RequiredCriteria ([Value] nvarchar(100), [Key] nvarchar(100))");
    
    // If you're expecting LOTs of criteria this could become a bottleneck...
    foreach (var item in dictionary)
    {
        context.Database.ExecuteSqlInterpolated($"INSERT INTO #RequiredCriteria ([Value], [Key]) VALUES ({item.Key}, {item.Value})");
    }

    var matched = context.Rules.FromSqlInterpolated(
        @$"SELECT * FROM Rules 
        WHERE Id IN (
            SELECT RuleId 
            FROM Criteria C 
                INNER JOIN #RequiredCriteria RC 
                    ON C.[Key] = RC.[Key] AND C.[Value] = RC.[Value]
            GROUP BY RuleId
            HAVING COUNT(RuleId) = {dictionary.Count}
        )")
        .ToList();

    context.Database.ExecuteSqlRaw("DROP TABLE #RequiredCriteria");
}
finally
{
    context.Database.CloseConnection();
}

Edit 2:

If the Criteria table is going to be quite large, I'd definitely recommend you have an index on the Key and Value columns, otherwise you'll be doing a clustered index scan across the entire table for every query.

Right solution via raw SQL. But you can omit Distinct, in Dictionary it is required unique Key. — Svyatoslav Danyliv, Jan 07 '23 at 11:01

score 1 · Accepted Answer · answered Jan 07 '23 at 10:52

You can use function FilterByItems (don't want to repeat myself). Then query can be written in the following way:

var dictionary = new Dictionary<string,string>()
{
    {"RuleKey", "RuleValue"},
    {"RuleKey1", "RuleValue1"}
}

var matched = _ruleContext.Criterias
    .FilterByItems(dictionary, (c, kv) => c.Key == kv.Key && c.Value == kv.Value, true)
    .GroupBy(c => c.RuleId)
    .Where(g => g.Count() == dictionary.Count)
    .Select(g => new
    {
        RuleId = g.Key,
    });

var rules =
    from r in _ruleContext.Rules
    join m in matched on r.Id equals m.RuleId
    select r;

var result = rules.ToArray();

How to filter child table by dictionary

2 Answers2