How to convert this SQL statement to Linq for C#

Question

I am trying to convert my simple SQL statement into Linq format for my C# application but I always seem to struggle making the conversion. I downloaded linqpad and have been playing around with it but I'm still having issues with the proper format.

My SQL statement:

SELECT distinct PictureCity, PictureState
FROM Website_Gallery
GROUP BY PictureCity, PictureState, PictureDate
ORDER BY PictureCity, PictureState

The results are ordered by PictureCity and look like this:

Abington    MA
Acton   MA
Acushnet    MA
Agawam  MA
Andover MA
Arlington   MA
Arlington   TX
Ashby   MA
Ashland MA

What I have so far in my C# application which I can't seem to get to work. (I suck at linq).

var Results = _context.Website_Gallery
.(g => g.PictureCity, g => g.PictureState).AsEnumerable()
.Select g
.GroupBy(g => g)

Why does your group by include a column you don't select? Also if you only Group on the two columns you select then you shouldn't need the distinct, or you can keep the distinct and get rid of the group by. — juharr
– juharr, Commented Feb 13, 2020 at 14:46
Also don't use AsEnumerable like that as it pulls everything from the DB and does the rest of the query in memory (there are times when that is useful, but not here) — juharr
– juharr, Commented Feb 13, 2020 at 14:47
You're missing a function name on line 2 of the final snippet of code: .( — user47589
– user47589, Commented Feb 13, 2020 at 14:48

juharr · Accepted Answer · 2020-02-13 15:16:14Z

1

Seems like all you need is

var results = _context.Website_Gallery
    .OrderBy(x => x.PictureCity)
    .ThenBy(x => x.PictureState)
    .Select(x => new { x.PictureCity, x.PictureState })
    .Distinct();

that would be equivalent to the following SQL

SELECT distinct PictureCity, PictureState
FROM Website_Gallery
ORDER BY PictureCity, PictureState

because what you had did not need the group by

Note you can then either iterate that result in a foreach or tack a ToList to the end to materialize the query into memory.

edited Feb 13, 2020 at 15:16

answered Feb 13, 2020 at 14:50

juharr

32.4k4 gold badges60 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nguyễn Văn Phong · Accepted Answer · 2020-02-13 15:13:36Z

0

SQL

SELECT distinct PictureCity, PictureState
FROM Website_Gallery
ORDER BY PictureCity, PictureState

Linq

var Results = _context.Website_Gallery
                  .Select(g => new { g.PictureCity, g.PictureState })
                  .Orderby(p => p.PictureCity).ThenBy(p => p.PictureState)
                  .Distinct().ToList();

Or you can also do this

var Results = _context.Website_Gallery
            .GroupBy(x => new { PictureCity = x.PictureCity, PictureState = x.PictureState })
            .Select(g => new { g.Key.PictureCity, g.Key.PictureState }).Orderby(p => p.PictureCity).ThenBy(p => p.PictureState)
            .ToList();

edited Feb 13, 2020 at 15:13

answered Feb 13, 2020 at 14:52

Nguyễn Văn Phong

14.2k19 gold badges47 silver badges65 bronze badges

Collectives™ on Stack Overflow

How to convert this SQL statement to Linq for C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related