Philip Sy Philip Sy - 2 months ago 11
SQL Question

Convert SQL with multiple joins (with multiple conditions) of the same table to LINQ

Can someone please help me on converting an

SQL
to
LINQ
. Basically, I have a
project_mstr
table that has a
PR_CLASS
,
PR_TYP
and
PR_GRP
columns. Those 3 columns are values in the
params_mstr
under
param_cd
. For example, there's a record that has a
PR_TYP
value in
param_cd
with a
Regular
as its corresponding
param_val
value.

I installed Linquer but I'm not comfortable using it since I still have to create a connection to my database. I can't also find an online
SQL
to
LINQ
converter. So I'm asking the good guys here to please help me with the conversion. Thank you very much guys!

SELECT
c.pr_id, c.pr_class, c.pr_typ, c.pr_grp, cp.pr_price,
c.gl_acct_id, c.pr_DESC "Project",
pm.param_val "Project Class", pm2.param_val "Project Type", pm3.param_val "Project Group"
FROM project_mstr c
JOIN
params_mstr pm ON c.pr_class = pm.param_id AND pm.param_cd = 'PR_CLASS'
JOIN
params_mstr pm2 ON c.pr_typ = pm2.param_id AND pm2.param_cd = 'PR_TYP'
JOIN
params_mstr pm3 ON c.pr_grp = pm3.param_id AND pm3.param_cd = 'PR_GRP'
JOIN
pr_price_mstr cp ON c.pr_id = cp.pr_id
JOIN
gl_acct_mstr gl ON c.gl_acct_id = gl.gl_acct_id
ORDER BY
c.crea_dt DESC;

Answer

LINQ-to-SQL only support equijoins, so if you need to introduce multiple values into the join, you can create an anonymous class to represent all of the values being joined on (note that the anonymous classes need to be the same type, which means that they need to have (1) exactly the same names of fields (2) of exactly the same type (3) in exactly the same order).

from c in ProjectMstr
join pm in ParamsMstr on new { ParamId = c.ChClass, ParamCd = "CH_CLASS" } equals new { pm.ParamId, pm.ParamCd }
join pm2 in ParamsMstr on new { ParamId = c.ChClass, ParamCd = "CH_TYP" } equals new { pm2.ParamId, pm2.ParamCd }
join pm3 in ParamsMstr on new { ParamId = c.ChClass, ParamCd = "CH_GRP" } equals new { pm3.ParamId, pm3.ParamCd }
// …
orderby c.CreaDt descending
select new {
    c.ChId,
    // …
    ProjectClass = pm.ParamVal,
    ProjectType = pm2.ParamVal,
    ProjectGroup = pm3.ParamVal,
}

Alternatively, if it doesn't change the logic of the query, you can pull out the constant value from the join into a where.

from c in ProjectMstr
join pm in ParamsMstr on c.ChClass equals pm.ParamId
join pm2 in ParamsMstr on c.ChClass equals pm2.ParamId
join pm3 in ParamsMstr on c.ChClass equals pm3.ParamId
// …
where pm.ParamCd == "CH_CLASS"
where pm.ParamCd == "CH_TYP"
where pm.ParamCd == "CH_GRP"
orderby c.CreaDt descending
select new {
    c.ChId,
    // …
    ProjectClass = pm.ParamVal,
    ProjectType = pm2.ParamVal,
    ProjectGroup = pm3.ParamVal,
}