Improved Dynamic Query

Yesterday I figured out how we could use C# Expressions to filter query results by any number of criteria. Today I refined my approach somewhat. Instead of the long, complex LINQ query I used there, I now have:

1 2 3 4 5 6 7 8 9

var userTimes = (from t in times group t by new {t.User.UserName, t.TargetDate} into ut select new { UserName = ut.Key.UserName, TargetDate = ut.Key.TargetDate, Minutes = ut.Sum(t => t.Minutes) } );

This produces a faster, more reasonable set of SQL code which returns almost identical data:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

-- Region Parameters DECLARE @p__linq__2 DateTime SET @p__linq__2 = '2009-02-14 16:01:50.069' -- EndRegion SELECT 1 AS [C1], [GroupBy1].[K2] AS [UserName], [GroupBy1].[K1] AS [TargetDate], [GroupBy1].[A1] AS [C2] FROM ( SELECT [Extent1].[TargetDate] AS [K1], [Extent2].[UserName] AS [K2], SUM( CAST( [Extent1].[Minutes] AS int)) AS [A1] FROM [dbo].[TrackedTime] AS [Extent1] LEFT OUTER JOIN [dbo].[aspnet_Users] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[UserId] WHERE ([Extent1].[TargetDate] > @p__linq__2) AND ([Extent1].[TargetDate] < (GetDate())) GROUP BY [Extent1].[TargetDate], [Extent2].[UserName] ) AS [GroupBy1]

Note that I avoided the problem I mentioned here by returning a simple set of native types rather than Entity Framework objects. This approach wouldn't be best under normal circumstances. If I'm querying the database for usernames, for example, I will often want to have more user data as well (like their real names) available to my front-end code. In that case, I could benefit from using the Entity Framework to get all the object data in a single roundtrip. However, if I'm pulling data for reporting purposes, I generally know exactly what data I want to be displaying. In that case it's more important to keep the data set small and fast. I've seen poorly-designed reporting engines pull so much data from the database that the VM runs out of memory, which causes all sorts of problems.

Finally, rather than relying on the LINQ framework to package my objects into custom classes for me, I created a generic TableData class, along with an extender that allows me to do this:

1 2 3 4 5

public TableData<String, DateTime, int> generateData(List<Filter<TrackedTime>> dateFilters) { ... return userTimes.ToTable(d => d.UserName, d => d.TargetDate, d => d.Minutes); }

The next step would be to create a generic web control that creates a front-end table when given the returned report data. That way we can use a single control to display all kinds of report data, rather than creating a new method to display each new set of report data.