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.

Answer 1

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();