Slow LINQ query because of Where+ToDictionary

Question

I have this query that I need to run:

IEnumerable<MerchantWithParameters> merchants =
    from i in (
        from m in d.GetTable<Merchant>()
        join mtp in d.GetTable<MerchantToParameters>() on m.Id equals mtp.MerchantId into mtps
        from mtp in mtps.DefaultIfEmpty()
        join cp in d.GetTable<ContextParameters>() on mtp.ContextParametersId equals cp.Id into cps
        from cp in cps.DefaultIfEmpty()
        select new {Merchant = m, ContextParameter = cp}
    )
    group i by new { i.Merchant.Id } into ig
    select new MerchantWithParameters()
    {
        Id = ig.Key.Id,
        Parameters = ig.Where(g => g.ContextParameter != null).ToDictionary(g => g.ContextParameter.Key, g => g.ContextParameter.Text)
    };

For some reason it takes really long time for this query to be completed.

I believe that it has something to do with

Parameters = ig.Where(g => g.ContextParameter != null).ToDictionary(g => g.ContextParameter.Key, g => g.ContextParameter.Text)

Because when I remove this line, query starts to execute really fast.

Could you please show me what am I doing wrong?

UPDATE: I am using ToList() to extract data from the database.

@Llama: I have merchants and i have parameters in separate tables. Then I need to merge them together: I need to have merchant with property for the parameters as dictionary. — walruz, May 07 '21 at 08:49
`IEnumerable merchants = ....;` doesn't execute the query, it just stores the information needed to execute the query in `merchants`. Perhaps you're using it in such a way that it's going to the database serveral times to perform the same query? — ProgrammingLlama, May 07 '21 at 08:52

score 1 · Accepted Answer · answered May 07 '21 at 11:01

It is known SQL limitation. You cannot get grouped items, only grouping key or aggregation result. Since you need all records, we can do grouping on the client side, but previously maximally limit retrieved data.

var query = 
    from m in d.GetTable<Merchant>()
    from mtp in d.GetTable<MerchantToParameters>().LeftJoin(mtp => m.Id == mtp.MerchantId)
    from cp in d.GetTable<ContextParameters>().LeftJoin(cp => mtp.ContextParametersId == cp.Id)
    select new 
    { 
        MerchantId = m.Id, 
        ContextParameterKey = (int?)cp.Key, 
        ContextParameterText = cp.Text
    };

var result = 
    from q in query.AsEnumerable()
    group q by q.MerchantId into g
    select new MerchantWithParameters
    {
        Id = g.Key,
        Parameters = g.Where(g => g.ContextParameterKey != null)
           .ToDictionary(g => g.ContextParameterKey.Value, g => g.ContextParameterText)
    };

var merchants = result.ToList();

Slow LINQ query because of Where+ToDictionary

1 Answers1