in the grouping query, I need to group s dynamically, based on a parameter I get passed to my method. There are 4 different options (BAC, District, Zone, Region). I could easily duplicate the code 4 times, but that is...blah. Any ideas? Possibly generate an in-line case?

            var submissions = 
                from s in (from pi in MetaData.ProcessRoundIssueInstance
                    join t in MetaData.Transaction on pi.TransactionID equals t.TransactionID into temp
                    from x in temp.DefaultIfEmpty()
                    where pi.Active && pi.IsComplete
                    select new
                    {
                        Id = pi.ProcessRoundIssueInstanceID,
                        ProcessRoundTypeId = pi.ProcessRound.ProcessRoundTypeID,
                        BAC = pi.Group.GroupID,
                        District = pi.Group.Group.GroupID,
                        Zone = pi.Group.Group.Group.GroupID,
                        Region = pi.Group.Group.Group.Group.GroupID,
                        TransactionId = pi.TransactionID,
                        Amount = (double?)x.Quantity
                    })
                    select s;

            var grouping = from s in submissions
                    group s by s.Region into g
                    select new
                    {
                        Group = g.Key,
                        TotalSubmissions = g.Count(x => x.ProcessRoundTypeId == 1),
                        TotalVotes = g.Count(x => x.ProcessRoundTypeId == 2),
                        EarnedPayment = g.Count(x => x.TransactionId != null),
                        TotalEarned = g.Sum(x => x.Amount)
                    };

You can use q.GroupBy() depending on the parameter, you can use a switch-case, then you can do a q.Select(). Nothing general I'm afraid, as the projection could be complex to just pass to it a simple field.

daelmo wrote:

You can use q.GroupBy() depending on the parameter, you can use a switch-case, then you can do a q.Select(). Nothing general I'm afraid, as the projection could be complex to just pass to it a simple field.

True, but that variable holding the grouping results will not work with anonymous types, so I'll have to create a Submission type. Can you show me a quick example so I can make sure I know what you mean?

True, but that variable holding the grouping results will not work with anonymous types, so I'll have to create a Submission type.

What exactly do you mean 'doesn't work' ?

Anyway, I agree with David, it's not really solveable generically, other than creating the lambda expression manually.

It's a bit of a pain, because group by in linq is not really group by. This means that you can't really append a groupby and return g in a query and append to THAT a select, because the query then is an IGrouping, which is enumerable, but is treated differently by the linq provider, as it has to that because of how Linq's group by works.

So the only way to 'solve' this is by replacing the group group s by s.Region into g, is by writing it as .GroupBy(...) where you pass in an Expression created from the parameter. Though I fear that gives other problems as the C# compiler likes to cram the select projection into the group by projection in your query.

IMHO it's thus easier to write this query in our own api, which can deal with flexible group by collections easily.