3.1.11.0706 Final (SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll)
3.1.12.0806 (SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll
3.1.12.0507 (SD.LLBLGen.Pro.LinqSupportClasses.NET35)
DotNet 4.0 vs2010 project
Adapter template
I have a complex algorithm which ends up counting a number of things. The following code snippet gets the counts where an answer to a question is Yes or No and also counts how many answers are out of date.
This works in most cases but fails when various filters get applied as the Answer counts don't get filtered. I want to be able to do a left join on the answers (code is already in place but not used yet as I don't know how to reference it in the group projection) and then count the answers from there. That way, any top level filtering will automatically filter the answers.
Here is the current code:
public IQueryable<FacilityCounts> RegulationsDashboards(int idClient)
{
var regulations = Regulations<RegulationBaseEntity>(idClient);
var regulationDashboards =
from a in metaData.Facility
join b in metaData.ClientRegion on new { a.IDClient, a.CountryCode, a.RegionCode, a.ServiceCode } equals
new { b.IDClient, b.CountryCode, b.RegionCode, b.ServiceCode }
join c in metaData.RegulationCountryRegion on new { a.CountryCode } equals new { c.CountryCode }
join d in regulations on c.RegID equals d.RegID
join e in metaData.AnswerRegulation on new { a.IDClient, a.FacilityCode, d.RegID }
equals new { e.IDClient, e.FacilityCode, e.RegID } into f
from f1 in f.DefaultIfEmpty()
where
a.IDClient == idClient && a.ServiceCode.Equals("cm", StringComparison.CurrentCultureIgnoreCase) && (c.RegionCode == a.RegionCode || (c.RegionCode == "_countrywide" && b.IncludeFederal))
group a by new { a.IDClient, a.FacilityCode, a.CountryCode, a.RegionCode }
into g
select new FacilityCounts
{
IDClient = g.Key.IDClient,
FacilityCode = g.Key.FacilityCode,
CountryCode = g.Key.CountryCode,
RegionCode = g.Key.RegionCode,
TotalRegulations = g.Count(),
ApplicableRegulations =
metaData.AnswerRegulation.Count(
h =>
regulations.Any(i => i.RegID == h.RegID) && h.Answer == "Yes" &&
h.IDClient == g.Key.IDClient && h.FacilityCode == g.Key.FacilityCode),
NotApplicableRegulations =
metaData.AnswerRegulation.Count(
h =>
regulations.Any(i => i.RegID == h.RegID) && h.Answer == "No" &&
h.IDClient == g.Key.IDClient && h.FacilityCode == g.Key.FacilityCode),
RequiresRevalidationRegulations =
metaData.AnswerRegulation.Count(
h =>
regulations.Any(
i =>
i.RegID == h.RegID &&
h.AnswerLastModifiedDate < i.LastModifiedDateBaseLanguage &&
h.CommentLastModifiedDate < i.LastModifiedDateBaseLanguage) &&
h.IDClient == g.Key.IDClient && h.FacilityCode == g.Key.FacilityCode),
};
return regulationDashboards;
}
Although it is detailed, the troublesome part is the repeated sections:
metaData.AnswerRegulation.Count(
h =>
regulations.Any(i => i.RegID == h.RegID) && h.Answer == "Yes" &&
h.IDClient == g.Key.IDClient && h.FacilityCode == g.Key.FacilityCode),
It doesn't ensure that only the filtered regulation answers are included in the counts as I can't correlate the RegID with the top level RegID.
Not only does it use the raw metaData.AnswerRegulation but it also uses the regulations IQueryable before being filtered. (The regulation IQueryable is a simpler Linq statement to produce a filtered set of rows to use in this counting method)
If I could work out how to use the left joined rows for these counts, I think the results would be accurate when filtering changes the number of rows in the TotalRegulations count.
Any suggestions gratefully received. Maybe I should be doing a nested group on the AnswerRegulations table instead of a Left join? Or maybe it would be easier to separate the job into 2 methods; one for the total and then a separate one to do the answer counts?
