I've been working on a proof-of-concept for building dynamic Linq queries in the Entity Framework. The idea is to have any number of criteria that a user can set to limit or expand the scope of (for example) a report.
I still have a long way to go before it would be ready to use for real, but here's what I've been able to do:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| public class ReportPerUserPerDay2 { public List<UserNamesWithDatesWithMinuteTotals> generateData(List<Filter<TrackedTime>> dateFilters) { TimesheetEntities context = new TimesheetEntities(); IQueryable<TrackedTime> times = context.TrackedTime; foreach (Filter<TrackedTime> dateFilter in dateFilters) { times = times.Where(dateFilter.getPredicate()); } var userTimes = (from t in times group t by t.User.UserName into ut from ut2 in (from t in ut group t by t.TargetDate into ut3 select new DatesWithMinuteTotals() { TargetDate = ut3.Key, Minutes = ut3.Sum(t => t.Minutes) }) group ut2 by ut.Key into ut4 select new UserNamesWithDatesWithMinuteTotals() { UserName = ut4.Key, Dates = from t in ut4 select t } ); return userTimes.ToList(); } }
public class DatesWithMinuteTotals { public DateTime TargetDate; public long Minutes; }
public class UserNamesWithDatesWithMinuteTotals { public String UserName; public IEnumerable<DatesWithMinuteTotals> Dates; } |
So I can pass the generateData method filters like this one:
1 2 3 4 5 6 7 8 9 10 11 12 13
| public partial class Controls_ReportFilters_FilterDateRange : System.Web.UI.UserControl, TimesheetReporting.Filters.Filter<TrackedTime> { ...
public Expression<Func<TrackedTime, bool>> getPredicate() { if (StartDate.Ticks == 0 && EndDate.Ticks == 0) return t => true; if (StartDate.Ticks == 0) return t => t.TargetDate <= EndDate; if (EndDate.Ticks == 0) return t => t.TargetDate >= StartDate; return t => t.TargetDate <= EndDate && t.TargetDate >= StartDate; }
}
|
and the Linq statement will auto-generate this crazy SQL code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
| -- Region Parameters DECLARE p__linq__2 DateTime SET p__linq__2 = '2009-02-13 18:13:53.470' -- EndRegion SELECT [Project7].[UserName] AS [UserName], [Project7].[C1] AS [C1], [Project7].[C3] AS [C2], [Project7].[TargetDate] AS [TargetDate], [Project7].[C2] AS [C3] FROM ( SELECT [Distinct2].[UserName] AS [UserName], 1 AS [C1], [Project6].[TargetDate] AS [TargetDate], CASE WHEN ([Project6].[C2] IS NULL) THEN CAST(NULL AS bigint) ELSE CAST( [Project6].[C1] AS bigint) END AS [C2], [Project6].[C2] AS [C3] FROM (SELECT DISTINCT [Project2].[UserName] AS [UserName] FROM (SELECT @p__linq__2 AS [p__linq__2], [Distinct1].[UserName] AS [UserName] FROM ( SELECT DISTINCT [Extent2].[UserName] AS [UserName] FROM [dbo].[TrackedTime] AS [Extent1] LEFT OUTER JOIN [dbo].[aspnet_Users] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[UserId] WHERE [Extent1].[TargetDate] > @p__linq__2 ) AS [Distinct1] ) AS [Project2] CROSS APPLY (SELECT [Extent3].[TargetDate] AS [K1] FROM [dbo].[TrackedTime] AS [Extent3] LEFT OUTER JOIN [dbo].[aspnet_Users] AS [Extent4] ON [Extent3].[UserId] = [Extent4].[UserId] WHERE ([Extent3].[TargetDate] > @p__linq__2) AND (([Extent4].[UserName] = [Project2].[UserName]) OR (([Extent4].[UserName] IS NULL) AND ([Project2].[UserName] IS NULL))) GROUP BY [Extent3].[TargetDate] ) AS [GroupBy1] ) AS [Distinct2] LEFT OUTER JOIN (SELECT [Project5].[UserName] AS [UserName], [GroupBy2].[K1] AS [TargetDate], [GroupBy2].[A1] AS [C1], 1 AS [C2] FROM (SELECT @p__linq__2 AS [p__linq__2], [Distinct3].[UserName] AS [UserName] FROM ( SELECT DISTINCT [Extent6].[UserName] AS [UserName] FROM [dbo].[TrackedTime] AS [Extent5] LEFT OUTER JOIN [dbo].[aspnet_Users] AS [Extent6] ON [Extent5].[UserId] = [Extent6].[UserId] WHERE [Extent5].[TargetDate] > @p__linq__2 ) AS [Distinct3] ) AS [Project5] CROSS APPLY (SELECT [Extent7].[TargetDate] AS [K1], SUM( CAST( [Extent7].[Minutes] AS int)) AS [A1] FROM [dbo].[TrackedTime] AS [Extent7] LEFT OUTER JOIN [dbo].[aspnet_Users] AS [Extent8] ON [Extent7].[UserId] = [Extent8].[UserId] WHERE ([Extent7].[TargetDate] > @p__linq__2) AND (([Extent8].[UserName] = [Project5].[UserName]) OR (([Extent8].[UserName] IS NULL) AND ([Project5].[UserName] IS NULL))) GROUP BY [Extent7].[TargetDate] ) AS [GroupBy2] ) AS [Project6] ON ([Project6].[UserName] = [Distinct2].[UserName]) OR (([Project6].[UserName] IS NULL) AND ([Distinct2].[UserName] IS NULL)) ) AS [Project7] ORDER BY [Project7].[UserName] ASC, [Project7].[C3] ASC
|
which, amazingly enough, rather efficiently produces exactly the information I wanted, and no more. Pretty slick, eh?
No comments:
Post a Comment