CODEWITHSUNDEEP
.netvb.netlinq
Navaneethakrishnan
Navaneethakrishnan

Reputation: 21

How to convert the SQL query into LINQ?

I am new to LINQ. Can anyone please help me convert the following SQL query to LINQ?

SELECT 
   Date,ShiftName,Max(Score) AS Score, 1 AS IsPreferred  
FROM 
   Temp_Nurse_Fill_RequestNumbers 
group by 
   ShiftName,date

Upvotes: 2

Views: 214

Answers (4)

Oybek
Oybek

Reputation: 7243

Just for convenience

tems.
    GroupBy(Function(x) New With { x.ShiftName, x.Date }).
    Select(Function(g) New TempNurseProjection With {
        .Date = g.Key.Date,
        .ShiftName = g.Key.ShiftName,
        .MaxScore = g.Max(Function(q) q.Score),
        .IsPreferred = True
    })

EDIT

Sorry didn't noticed that the final projection is not anonymous but named type.

Upvotes: 1

dknaack
dknaack

Reputation: 60556

You can use a tool called LINQPad.

Tired of querying in antiquated SQL? Well, you don't have to! LINQPad lets you interactively query databases in a modern query language: LINQ. Kiss goodbye to SQL Management Studio! ... LINQPad is also a great way to learn LINQ: it comes loaded with 500 examples from the book, C# 4.0 in a Nutshell. There's no better way to experience the coolness of LINQ and functional programming.

LINQPad Website

hope this helps

Upvotes: 0

Ian Nelson
Ian Nelson

Reputation: 58783

Hmm, how about something like this (this is C#, I presume it is trivial to convert)

temps
    .GroupBy(x => new {x.ShiftName, x.Date})
    .Select(g => new TempNurseProjection
        {
            Date = g.Key.Date,
            ShiftName = g.Key.ShiftName,
            MaxScore = g.Max(q => q.Score),
            IsPreferred = true
        });

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

from t in Temp_Nurse_Fill_RequestNumbers 
group by t.ShiftName, t.date
into g 
select new 
{
   Date = g.date,
   Score = (from tt in g select tt.Score).max
   IsPreferred = 1
};

Upvotes: 1

Related Questions