Convert SQL with Inner AND Outer Join to L2S
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 int
s 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();
}
}
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.ImmunizationMaintID
equals maintItem.ItemID
join maintItem2 in context.tblMaintItems
on haReviewImmunization.ImmunizationReasonID
equals maintItem2.ItemID into g
from maintItem3 in g.DefaultIfEmpty()
select new
{
haReviewImmunization.ImmunizationDate,
haReviewImmunization.ImmunizationOther,
maintItem.ItemDescription,
Reason = maintItem3 == null ? " " : maintItem3.ItemDescription
};
return currentImmunizations.CopyLinqToDataTable();
}
}
精彩评论