none
Dynamic Where Expression within GroupBy RRS feed

  • Question

  • I have a function that needs to summarise data, grouped by a manager and then by the category of work. The manager is from a variable position in the company hierarchy, and is represented in the database by fields ManagerLevel1, ManagerLevel2 etc. The function takes a parameter indicating which field should be used.

    I am able to produce the outer grouping expression, but the where expression to produce a list of numbers for the Values property is eluding me. I can hard-code it, but again, this needs to be variable.

    The below code includes the hard-coded statement drGroup.Where(p => p.ManagerLevel1 == drGroup.Key), which I need to make dynamic, as the groupbyExpression is.

    string paramName = "ManagerLevel1";
    var itemParameter = System.Linq.Expressions.Expression.Parameter(typeof(Models.WorkOrder), "w");
    var drSelectorExpression = System.Linq.Expressions.Expression.Property(itemParameter, paramName);
    var groupbyExpression = System.Linq.Expressions.Expression.Lambda<Func<Models.WorkOrder, string>>
    (
    	drSelectorExpression,
    	new[] { itemParameter }
    );
    
    var nodes = from drGroup in
    		(from w in dataContext.WorkOrders
    		 where w.reportdate >= DateTime.Now.AddDays(-180).Date && w.reportdate <= DateTime.Now
    		 select w).GroupBy(groupbyExpression)
    	orderby drGroup.Key
    	select new CategoryPersonSummaryTable
    	{
    		CategoryPersonSummaryTableID = Convert.ToInt32(drGroup.Key),
    		Person = drGroup.Key,
             Values = (from w in drGroup.Where(p => p.ManagerLevel1 == drGroup.Key)
    			 group w by new { WorkCategory = w.worktype } into wcGroup
    			 select (float)wcGroup.Count()).ToList<float>()
    	};
    
    

    Expected output for the function is a list something like:

    [Person = Person1, Values = [1.5, 1, 4]],

    [Person = Person2, Values = [3, 0, 5]],

    [Person = Person3, Values = [2, 6, 8]]

    As an aside, I know the Convert.ToInt32(drGroup.Key) statement will fail, since drGroup.Key is the name of a category of work - if anybody knows a way to get the sequence number of an object it would be greatly appreciated e.g. for Person1 it would be 0, for Person2 it would be 1 etc. I just need to create a key for RIA services to work with the object.

    Friday, August 6, 2010 3:28 AM