Convert SQL with Inner AND Outer Join to L2S
- by Refracted Paladin
I need to convert the below Sproc to a Linq query. At the very bottom is what I have so far. For reference the fields behind the "splat"(not my sproc) are
ImmunizationID int, HAReviewID int, ImmunizationMaintID int, ImmunizationOther varchar(50), ImmunizationDate smalldatetime, ImmunizationReasonID int
The first two are PK and FK, respectively. The other two ints are linke to the Maint Table where there description is stored. That is what I am stuck on, the INNER JOIN AND the LEFT OUTER JOIN
Thanks,
SELECT tblHAReviewImmunizations.*,
tblMaintItem.ItemDescription,
tblMaintItem2.ItemDescription as Reason
FROM
dbo.tblHAReviewImmunizations
INNER JOIN dbo.tblMaintItem
ON dbo.tblHAReviewImmunizations.ImmunizationMaintID =
dbo.tblMaintItem.ItemID
LEFT OUTER JOIN dbo.tblMaintItem as tblMaintItem2
ON dbo.tblHAReviewImmunizations.ImmunizationReasonID =
tblMaintItem2.ItemID
WHERE
HAReviewID = @haReviewID
My attempt so far --
public static DataTable GetImmunizations(int haReviewID)
{
using (var context = McpDataContext.Create())
{
var currentImmunizations =
from haReviewImmunization in context.tblHAReviewImmunizations
where haReviewImmunization.HAReviewID == haReviewID
join maintItem in context.tblMaintItems
on haReviewImmunization.ImmunizationReasonID
equals maintItem.ItemID into g
from maintItem in g.DefaultIfEmpty()
let Immunization = GetImmunizationNameByID(
haReviewImmunization.ImmunizationMaintID)
select new
{
haReviewImmunization.ImmunizationDate,
haReviewImmunization.ImmunizationOther,
Immunization,
Reason = maintItem == null ? " " : maintItem.ItemDescription
};
return currentImmunizations.CopyLinqToDataTable();
}
}
private static string GetImmunizationNameByID(int? immunizationID)
{
using (var context = McpDataContext.Create())
{
var domainName =
from maintItem in context.tblMaintItems
where maintItem.ItemID == immunizationID
select maintItem.ItemDescription;
return domainName.SingleOrDefault();
}
}
