Linq with multiple table joins, ordering and grouping

Question

4.00/5 (1 vote)

See more:

I'm very new to LINQ, but thought I would give it a try. I am re building a health care program and have hit a block trying to display employees and there dependents money limits.

The query in SQL is something like this:

SELECT   e.employee, l.LIMIT, NVL (d.dependant, 0) dependant, l.limit_name, d.dependant_name, d.title ,   d.first_name , d.middle_name , d.last_name,DECODE (SUM (e.balance), NULL, 'N', 'Y') has_cash, DECODE (SUM (e.CHANGE), NULL, 'N', 'Y') has_cash_change, DECODE (SUM (e.balance_item), NULL, 'N', 'Y') has_item FROM PHSP.employee_limit e, PHSP.lt_limit l, PHSP.dependant d WHERE e.employee = :employee AND l.LIMIT = e.LIMIT AND e.dependant = d.dependant(+) GROUP BY e.employee, l.LIMIT, d.dependant, l.limit_name, d.dependant_name, d.title, d.first_name, d.middle_name, d.last_name
ORDER BY l.limit_name

I just don't know where to start with the joins, along with grouping and ordering by multiple columns in LINQ. Any advise would be much appreciated.

Thank you.

Posted 10-Nov-10 7:01am

Ryanm1324

Updated 10-Nov-10 7:11am

JF2015

v2

Add a Solution

Comments

Ryanm1324 12-Nov-10 10:32am

I somewhat have it working right now except for the decode (SUM(... last three columns. Not too sure about that part.
This is what I have so far:

from e in empLimHeader.EmployeeLimits
where e.Employee == Convert.ToDouble(txtEmployee.Text)
join l in empLimHeader.LtLimits on e.Limit equals l.Limit
join d in empLimHeader.Dependants on e.Dependant equals d.Dependant1
group d by new
{
e.Employee,
l.Limit,
d.Dependant1,
l.LimitName,
d.DependantName,
d.Title,
d.FirstName,
d.MiddleName,
d.LastName//,
// e.Balance,
//e.Change,
// e.BalanceItem
} into limHeader
orderby limHeader.Key.LimitName
select new
{
employee = limHeader.Key.Employee,
limit = limHeader.Key.Limit,
dependantNum = (limHeader.Key.Dependant1 == null ? 0 : limHeader.Key.Dependant1),
limitName = limHeader.Key.LimitName,
dependant = limHeader.Key.DependantName,
title = limHeader.Key.Title,
firstName = limHeader.Key.FirstName,
middleName = limHeader.Key.MiddleName,
lastName = limHeader.Key.LastName//,
// hasCash = (limHeader.Sum(a => limHeader.Key.Balance) == null ? 'N' : 'Y'),
// hasCashChange = (limHeader.Sum(a => limHeader.Key.Change) == null ? 'N' : 'Y'),
//hasItem = (limHeader.Sum(a => Convert.ToDouble(limHeader.Key.BalanceItem.ToString())) == null ? 'N' : 'Y')
};

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

TweakBird · Accepted Answer · 2010-12-15T05:48:00

Solution 3

see these also

1) how-to-join-two-tables-using-linq-to-sql/[^]
2) Linq-Group By[^]
3) join-and-group-by-linq-to-entities/[^]
4) linq-joins-and-groupings[^]

Posted 15-Dec-10 5:48am

TweakBird

Comments

Ryanm1324 17-Dec-10 11:29am

I tried to go through these examples, but keep getting t1.balanceItem, t1.change, and t1.balance invalid identifier.

Here is how it converted into SQL:

SELECT t1."Employee", t1."Limit",
(CASE
WHEN t1."Dependant1" IS NULL THEN 0
ELSE t1."Dependant1"
END) AS C1, t1."LimitName", t1."FirstName", t1."Title", t1."FirstName" AS "FirstName1", t1."MiddleName", t1."LastName",
DECODE(sum(t1."Balance"),null,'N','Y'),DECODE( sum(t1."Change"),null,'N','Y'), DECODE(sum(t1."BalanceItem"),null,'N','Y')
FROM (
SELECT t2.EMPLOYEE AS "Employee", t3.LIMIT AS "Limit", t4.DEPENDANT AS "Dependant1", t3.LIMIT_NAME AS "LimitName",
t4.DEPENDANT_NAME AS "DependantName", t4.TITLE AS "Title", t4.FIRST_NAME AS "FirstName", t4.MIDDLE_NAME AS "MiddleName",
t4.LAST_NAME AS "LastName", t2.BALANCE AS "Balance", t2.CHANGE AS "Change", t2.BALANCE_ITEM AS "BalanceItem"
FROM PHSP.EMPLOYEE_LIMIT t2
INNER JOIN PHSP.LT_LIMIT t3 ON t2.LIMIT = t3.LIMIT
LEFT OUTER JOIN PHSP.DEPENDANT t4 ON t2.DEPENDANT = t4.DEPENDANT
WHERE t2.EMPLOYEE = 1021493
GROUP BY t2.EMPLOYEE, t3.LIMIT, t4.DEPENDANT, t3.LIMIT_NAME, t4.DEPENDANT_NAME, t4.TITLE, t4.FIRST_NAME, t4.MIDDLE_NAME,
t4.LAST_NAME, t2.BALANCE, t2.CHANGE, t2.BALANCE_ITEM
) t1
group by t1."Employee", t1."Limit",t1."Dependant1", t1."LimitName",t1."Title", t1."FirstName" , t1."MiddleName", t1."LastName"
ORDER BY t1."LimitName"

Is there something else needed when trying to get the sum

Thanks

ANSAL P R · Accepted Answer · 2010-12-15T04:45:00

Solution 2

Please go through this.
Samples

Posted 15-Dec-10 4:45am

ANSAL P R