Convert SQL Multiple Left Join to LINQ

January 10, 2013 11:44 am
Andri Kasta Marengga

Convert SQL Multiple Left Join to LINQ

SQL Statement:

SELECT reg.Vsn_name,
SA = sa.Vsn_name,
SP = sp.Vsn_name,
KPT = eng.Vsn_KPT,
Engineer = eng.Vsn_EngineerName,
dev.FDeviceId,
dev.FDeviceName,
dev.FLastLoginTime,
dev.FVersion,
dev.FWarningCode,
dev.FCondition
FROM TT_DEVICE dev
LEFT OUTER JOIN Vsn_EngineerExtensionBase eng
ON dev.FUSerKPT=eng.Vsn_KPT
LEFT OUTER JOIN Vsn_ServicePOintExtensionBase sp
ON eng.Vsn_ServicePointId=sp.Vsn_ServicePointId
LEFT OUTER JOIN Vsn_ServicePOintExtensionBase sa
ON sp.Vsn_PArentId=sa.Vsn_ServicePointId
LEFT OUTER JOIN Vsn_ServicePOintExtensionBase reg
ON sa.Vsn_PArentId=reg.Vsn_ServicePointId

Nowadays i often share my sense with Linq, and having some problem in it. One of them is converting simple SQL script above to Linq format.
After some research finally I found what I need 🙂

from dev in db.TT_DEVICEs
from eng
in db.Vsn_engineerExtensionBases
.Where(e => e.Vsn_KPT == dev.FUserKPT)
.DefaultIfEmpty()
from sp
in db.Vsn_servicepointExtensionBases
.Where(sp => sp.Vsn_servicepointId == eng.Vsn_ServicePointId)
.DefaultIfEmpty()
from sa
in db.Vsn_servicepointExtensionBases
.Where(sa => sa.Vsn_servicepointId == sp.Vsn_ParentId)
.DefaultIfEmpty()
from reg
in db.Vsn_servicepointExtensionBases
.Where(reg => reg.Vsn_servicepointId == sa.Vsn_ParentId)
.DefaultIfEmpty()
select new
{
Regional = reg.Vsn_name,
SA = sa.Vsn_name,
SP = sp.Vsn_name,
KPT = eng.Vsn_KPT,
Engineer = eng.Vsn_EngineerName,
dev.FDeviceId,
dev.FDeviceName,
FLastLoginTime= String.Format("{0:dd-MMM-yyyy HH:mm}",dev.FLastLoginTime),
dev.FVersion,
dev.FWarningCode,
dev.FCondition
};

, ,

No comments yet.

Leave a Reply