How do I write the current SQL statement in Linq C#

Question

0.00/5 (No votes)

See more:

Hello,

I've just started working with Lightswitch and Linq and I'm having a hard time to write the following simple SQL statement as a Linq query:

SQL

SELECT Departments.Name, SUM(Salary) AS Total FROM EmployeesSet
JOIN Departments ON EmployeesSet.Department_Employees = Departments.id
GROUP BY Departments.Name

There are 2 tables: Departments and Employees. Each employee does have a salary and I want to SUM the total salary costs for each department in a list.

I've read that I can't use joins in Linq queries in Lightswitch. My question is how to realise this in Linq?

Thanks in advance!

Dave

Posted 30-Nov-14 12:05pm

Member 11275599

Updated 30-Nov-14 12:21pm

v2

Add a Solution

Comments

PIEBALDconsult 30-Nov-14 18:41pm

If it ain't broke, why break it?

BillWoodruff 30-Nov-14 18:53pm

Assuming "Departments" consists of "rows" each of which contain a set of references to an "EmployeeID:" ... in other words you can only access the employee's salary by doing a look-up from the data in Departments ... would an example showing you how to do this in Linq using two DataTables ... independent of LightSwitch ... be of any use ?

1 solution

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)

GauravSaxena.Net · Answer 1 · 2014-11-30T18:12:00

Hi
You can use the following query. As there are 2 tables Departments and Employees

C#

using(var context =new dbContext(Your contextName))
{
 var result = from dept in context.tblDepartments 
              group dept by dept.V_DEP_Name into dept1
              join emp in context.tblemployees on dept1.FirstOrDefault().I_DEPT_ID equals emp.I_DEPT_ID
              select new
             {
                    TotalSalary = dept1.sum(p=>p.salary),
                    DepartName = dept1.FirstOrDefault(p=>p.V_DEP_Name)
             }
}

I have given you the example. Here you can make change in the query as column in tables.