Recently I have been working on a LINQ to Entities (LE) proof of concept project at my day job. This project is my first experience with LE so the pace has been painfully slow. Fortunately, LE with C# is proving so concise and elegant that I’ve fallen in love. My first project consists of a rather simple Entity Framework model (EF), a web page that allows the eu to select some parameters that are used to build a LE query and display a GridView with the results - basically a simple view of some metrics - Plan versus Actual.
The goal was to write an LE query that shows the metric for the current month, the planned metric and the variance as a percentage.
var getStats = from stats in opsMetrics.StatisticsSet
where stats.FiscalPeriod.FiscalPeriodName == 'MAY_08'
where stats.District.Region.RegionName == 'South Region'
group stats.District.Region.RegionName
into groupedStats
select new
{
PC = groupedStats.Key,
Actual = groupedStats.Sum(p => p.Actual),
Plan = groupedStats.Sum(p => p.Plan)
};This produces the actual and plan metric for my GridView but I ran into some challenges when I wanted to calculate the percentage difference between the two:
Variance = (Actual/Plan) * 100
Linq to Entities has no percentage extension method. I think it would have confused the daylights out of me anyway. So I decided to create a subquery:
var getPerf = from stats in getStats
select new
{
stats.PC,
stats.Actual,
stats.Plan,
From_Plan = (stats.Actual/stats.Plan) * 100
};
Writing a subquery helps me mentally. I have been writing queries for many years. Nesting queries helps me to formulate unit of work models in my brain. SQL engines usually know how to optimize them and perform in an acceptable way. I like to let the relational calculus do all of the work.
However, there’s a problem in the above snippet. What happens when stats.Plan is zero? The SQL Server database engine reports a divide by zero exception. This can occur naturally when there is no plan. Perhaps there is a new district this year and we did not do the budget for it. We still want to view the district because we want a complete listing of the Region. In this case, we wish to calculate a value of zero for the variance. So, we will fix up our calculation of percentage a little:
var getPerf = from stats in getStats
select new
{
stats.PC,
stats.Actual,
stats.Plan,
From_Plan = (stats.Plan==0) ? 0 : (stats.Actual/stats.Plan) * 100
};Now to be honest this is not exactly what I want. I really and to see the From_Plan column show a percent sign “%” on the grid. So what I really want is a formatted string. If we just add the conversion and then concatenate the “%” sign, the From_Plan calculation is getting way more convoluted than it needs to be. So, I’d like to call a function that does all of the havy lifting:
var getPerf = from stats in getStats
select new
{
stats.PC,
stats.Actual,
stats.Plan,
From_Plan = safePercentString(stats.Actual, stats.Plan)
};Then we just implement a simple procedure called safePercentSign.
protected string safePercentString(decimal? inTop, decimal? inBot)
{
decimal result=0;
if (inBot != 0) result = (decimal)(inTop / inBot)*100;
return (result==0) ? “-” : result.ToString(”0,0.0″) + ” %”;
}
protected string safePercentString(int? inTop, int? inBot)
{
return safePercentString((decimal?) inTop,(decimal?) inBot);
}
The above will build and not produce an error but at run time, (in my case during the GridView.DataBind()), LINQ to Entities complains that safePercentString is an unknown thingermajigger (I forget what the actual exception is). Obviously, LE is unhappy with what we may consider an “external function call”. External in that my intent is that LINQ to Entities would just automagically call my procedure.
As it happens, LINQ to Entities fails to issue the query simply because it is unable to produce any SQL that would actually result in what I intended. Honestly, when you are this deep into LINQ you forget all about there even being any SQL involved because it’s all objects.
Objects! That’s the solution. We don’t want LINQ to Entities to calculate and convert the percentage at all. We want C# to do that. So the solution is simple. We convert the query to a LINQ to Objects query:
var getPerf = from stats in getStats.AsEnumerable()
select new
{
stats.PC,
stats.Actual,
stats.Plan,
From_Plan = safePercentString(stats.Actual, stats.Plan)
};The .AsEnumerable() extension method causes LINQ to Entities the enumerate the results into something LINQ to Objects can work with. LINQ to Objects has no problem calling my C# procedure.
Separate the Linq to Entities unit of work of retrieving the records you need to produce the prep to render unit of work using Linq to Objects. I’ve used this practice on a number of other occasions since and it dramatically boosts my productivity.
