Have a LinqtoSql query that I now want to precompile.
var unorderedc =
from insp in sq.Inspections
where insp.TestTimeStamp > dStartTime && insp.TestTimeStamp < dEndTime
&& insp.Model == "EP" && insp.TestResults != "P"
group insp by new { insp.TestResults, insp.FailStep } into grp
select new
{
FailedCount = (grp.Key.TestResults == "F" ? grp.Count() : 0),
CancelCount = (grp.Key.TestResults == "C" ? grp.Count() : 0),
grp.Key.TestResults,
grp.Key.FailStep,
PercentFailed = Convert.ToDecimal(1.0 * grp.Count() / tcount * 100)
};
I have created this delegate:
public static readonly Funct<SQLDataDataContext, int, string, string, DateTime, DateTime, IQueryable<CalcFailedTestResult>>
GetInspData = CompiledQuery.Compile((SQLDataDataContext sq, int tcount, string strModel, string strTest, DateTime dStartTime,
DateTime dEndTime, IQueryable<CalcFailedTestResult> CalcFailed) =>
from insp in sq.Inspections
where insp.TestTimeStamp > dStartTime && insp.TestTimeStamp < dEndTime
&& insp.Model == strModel && insp.TestResults != strTest
group insp by new { insp.TestResults, insp.FailStep } into grp
select new
{
FailedCount = (grp.Key.TestResults == "F" ? grp.Count() : 0),
CancelCount = (grp.Key.TestResults == "C" ? grp.Count() : 0),
grp.Key.TestResults,
grp.Key.FailStep,
PercentFailed = Convert.ToDecimal(1.0 * grp.Count() / tcount * 100)
});
The syntax error is on the CompileQuery.Compile() statement
It appears to be related to the use of the select new {} syntax. In other pre-compiled queries I have written I have had to just use the select projection by it self. In this case I need to perform the grp.count() and the immediate if logic.
I have searched SO and other references but cannot find the answer.
In short, you can't have a CompliedQuery for an anonymous type, you need to have the query return a named type, so your
select new
{
FailedCount = (grp.Key.TestResults == "F" ? grp.Count() : 0),
CancelCount = (grp.Key.TestResults == "C" ? grp.Count() : 0),
grp.Key.TestResults,
grp.Key.FailStep,
PercentFailed = Convert.ToDecimal(1.0 * grp.Count() / tcount * 100)
}
Would now be:
select new MyType
{
FailedCount = (grp.Key.TestResults == "F" ? grp.Count() : 0),
CancelCount = (grp.Key.TestResults == "C" ? grp.Count() : 0),
TestResults = grp.Key.TestResults,
FailStep = grp.Key.FailStep,
PercentFailed = Convert.ToDecimal(1.0 * grp.Count() / tcount * 100)
}
The last generic param of your Func<>
would be IQueryable<MyType>
as well, since that's what your query would now return.
In this case, MyType
would look something like:
public class MyType {
public int FailedCount { get; set; }
public int CancelCount { get; set; }
public string TestResults { get; set; }
public string FailStep { get; set; } //Unsure of type here
public decimal PercentFailed { get; set; }
}