CODEWITHSUNDEEP
c#sqllinq
Harambe Attack Helicopter
Harambe Attack Helicopter

Reputation: 780

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

How to convert 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.

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;

Upvotes: 0

Views: 61

Answers (1)

bdukes
bdukes

Reputation: 155935

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 pm2.ParamCd == "CH_TYP"
where pm3.ParamCd == "CH_GRP"
orderby c.CreaDt descending
select new {
    c.ChId,
    // …
    ProjectClass = pm.ParamVal,
    ProjectType = pm2.ParamVal,
    ProjectGroup = pm3.ParamVal,
}

Upvotes: 3

Related Questions