How to convert Sql server Query in Lambda expression

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008R2

SQL

SELECT [t0].[Id], 
       [t0].[Challan_No], 
       [t0].[Type], 
       [t0].[Measurement], 
       [t0].[Update_By], 
       [t0].[Date], 
       [t1].[Id] AS [Id2], 
       [t1].[Challan_No] AS [Challan_No2], 
       [t1].[Own_Comany], 
       [t1].[Parti_Name], 
       [t1].[Update_By] AS [Update_By2], 
       [t1].[Date] AS [Date2], 
       [t1].[IsDelete], 
       [t1].[Isgenbill], 
       [t2].[Rate] AS [Rate1]
FROM [dbo].[Challan_Total] AS [t0]
INNER JOIN [dbo].[Challan] AS [t1] ON [t0].[Challan_No] = [t1].[Challan_No]
INNER JOIN [dbo].[Rate] AS [t2] ON [t0].[Type] = [t2].[Meterial_type] 
                                AND [t1].Own_Comany = [t2].Company_Id 
                                AND [t1].Parti_Name=[t2].Parti_Id
WHERE [t0].[Challan_No] = '2014CH 10002'

I tried:

C#

var getch = dm.Challan_Totals
                      .Join(dm.Challans,
                      c => c.Challan_No, d => d.Challan_No,
                      (c, d) => new { c, d }).Select(b => b)
                      .Join(dm.Rates,
                      r => r.c.Type, s => s.Meterial_type &&
                      q => q.c., sq => sq.Meterial_type,
                      (r, s) => new { r, s.Rate1 })
                      .Where(m => m.r.c.Challan_No == "2014CH 10002");

Posted 6-Dec-14 5:03am

mukesh mourya

Updated 6-Dec-14 5:21am

Maciej Los

v2

Add a Solution

Comments

Maciej Los 6-Dec-14 11:05am

Do you mean Linq query?

mukesh mourya 6-Dec-14 11:07am

yas Mr. Maciej Los

Maciej Los 6-Dec-14 11:09am

What have you tried till now?
How does your entity model look?
What language: VB.NET, C#?

mukesh mourya 6-Dec-14 11:11am

var getch = dm.Challan_Totals
.Join(dm.Challans,
c => c.Challan_No, d => d.Challan_No,
(c, d) => new { c, d }).Select(b => b)
.Join(dm.Rates,
r => r.c.Type, s => s.Meterial_type &&
q => q.c., sq => sq.Meterial_type,
(r, s) => new { r, s.Rate1 })
.Where(m => m.r.c.Challan_No == "2014CH 10002");

mukesh mourya 6-Dec-14 11:13am

This code I tried in C#

Maciej Los 6-Dec-14 11:14am

Instead posting code in comment, use "Improve question" widget.

Maciej Los 6-Dec-14 11:16am

Have you tried to add at the end: .ToList();?

mukesh mourya 6-Dec-14 11:21am

i m new for lambda so i don't Know how does use and condition in inner joining like in My code

INNER JOIN [dbo].[Rate] AS [t2] ON [t0].[Type] = [t2].[Meterial_type]
AND [t1].Own_Comany = [t2].Company_Id
AND [t1].Parti_Name=[t2].Parti_Id

mukesh mourya 6-Dec-14 11:17am

--repost--

mukesh mourya 6-Dec-14 11:59am

help me.....

Kornfeld Eliyahu Peter 7-Dec-14 4:51am

If you are learning LINQ this may help you: http://www.linqpad.net/

2 solutions

Solution 2

You may use Linq2Sql's DataContext.GetCommand

C#

DbCommand dc = db.GetCommand(TheQeury);

Check out the msdn article on the method[^]

[added]
OR
See linqpad[^] (as suggested by Eliyahu in the thread above, I am not sure what the "lightweight" free version includes though...)
[\added]

Cheers,
C

Posted 6-Dec-14 23:17pm

Joezer BH

Updated 6-Dec-14 23:21pm

v2

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)

mukesh mourya · Accepted Answer · 2014-12-11T02:58:00

C#

var result = dm.Challan_Totals
                                          .Join(dm.Challans,
                                                ct => ct.Challan_No,
                                                c => c.Challan_No,
                                                (ct, c) => new { ct, c })
                                           .Join(dm.Rates,
                                                ctc => new
                                                {
                                                    Type = ctc.ct.Type,
                                                    Own_Company = ctc.c.Own_Comany,
                                                    Parti_Name = ctc.c.Parti_Name
                                                },
                                                rt => new
                                                {
                                                    Type = rt.Meterial_type,
                                                    Own_Company = rt.Company_Id,
                                                    Parti_Name = rt.Parti_Id
                                                },
                                                (ctc, rt) => new { ctc, rt })
                           .Where(x => x.ctc.ct.Challan_No == "2014CH 10002")
                                           .Select(res => new
                                           {
                                               Challan_no = res.ctc.ct.Challan_No,
                                               Type = res.ctc.ct.Type,
                                               Measurment = res.ctc.ct.Measurement,
                                               Rate1 = res.rt.Rate1,
                                               Amount = (res.ctc.ct.Measurement) * ((double)res.rt.Rate1)
                                           })
                                           .ToList();