linq parametrizable group by after a Join clause

Question

I've followed this post: LINQ group by property as a parameter to make a parametrizable query, and I'm now trying to do the same after a group by clause, but I can't properly define the group/or access the properties after it. Here is what I'm trying to achieve:

 public Dictionary<string, float?> GetFundingByFilter<TKey>(Expression<Func<Object, TKey>> myGroupingProperty, Filter item)
    {

        int cntToBeSureThatTheQueryExecuteAtLeastOneTime = 0;
        Dictionary<string, float?> countriesAndCount = new Dictionary<string, float?>();
        using (var db = new fintechDbContext())
                {


                    countriesAndCount = (from p in db.companyDBSET
                                join f in db.fundingDBSET on p.Company equals f.Company into d 
                                from t in d.DefaultIfEmpty()
                                select  new  {p, t })
                                .GroupBy(myGroupingProperty)
                                .Select(r => new { Value = r.Key, Sum = r.Sum(d => d == null ? 0 : d.t.Amount) })
                                .OrderByDescending(y=>y.Sum)
                                .ToDictionary(v => v.Value.ToString(), v => v.Sum);



                }
            }
        }
        return countriesAndCount;
    }

but d.t.Amount is no longer accessible in this case.

The problem is **Object** inside `Expression>`. Even if you make it somehow compile, how do you think you'll be able to call it? — Ivan Stoev, Apr 04 '16 at 17:06

ocuenca · Accepted Answer · 2016-04-04T18:26:57.027

Well one thing that you can do is create a custom class (a DTO) to save the projection of your query after do the join:

// Instead of save both instances, you can define this class with only the properties you need from both entities
public class CompanyFundingDTO
{
   public Company Company {get;set;}
   public Funding Funding {get;set;}
}

Then in your method change Object by CompanyFundingDTO:

public Dictionary<string, float?> GetFundingByFilter<TKey>(Expression<Func<CompanyFundingDTO, TKey>> myGroupingProperty, Filter item)
{
       //...
       countriesAndCount = (from p in db.companyDBSET
                            join f in db.fundingDBSET on p.Company equals f.Company into d 
                            from t in d.DefaultIfEmpty()
                            select  new  CompanyFundingDTO{Company=p,Funding= t })//Project this way
                            .GroupBy(myGroupingProperty)
                            .Select(r => new { Value = r.Key, Sum = r.Sum(d => d == null ? 0 : d.Funding.Amount) }
                            .OrderByDescending(y=>y.Sum)
                            .ToDictionary(v => v.Value.ToString(), v => v.Sum);
      //...
}

Now, you can call your method passing the first parameter as I show below:

var result= instance.GetFundingByFilter(ct=>ct.Company.SomeCompanyProperty,...);

Thank you to take the time to answer this! Seems silly now :) — Antoine Dussarps, Apr 04 '16 at 19:23

linq parametrizable group by after a Join clause

1 Answers1