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