convert sql query to linq query

Hi,

Can someone please convert the following sql query to linq please?

it should list unique records and how many records there are with that text
SELECT distinct columnname as var_text, COUNT(columnname) as var_count FROM tbl_data where columnname > '' GROUP BY columnname

Open in new window

OTMAsked:
Who is Participating?
 
Kusala WijayasenaSoftware EngineerCommented:
OK, if your requirement is that, change the query as follows

var results = from tbl in tbl_data
              where tbl.columnname.Trim().Length > 0
              group tbl by tbl.columnname into g
              select new { var_text = g.Key, var_count = g.Count() };

Open in new window


-Kusala
0
 
käµfm³d 👽Commented:
Here's my interpretation:
var results = from item in (from columnname in tbl_data
                            where columnname > ''    // Not sure why greater than empty string, but copied nonetheless
                            select columnname).Distinct()
              group item by item into g
              select new { var_text = g.Key, var_count = g.Count() };

Open in new window

0
 
Kusala WijayasenaSoftware EngineerCommented:
kaufmed, I think your solution should be corrected as follows

var results = from item in (from tbl in tbl_data
                        where tbl.columnname.Trim().Length > 0
                        select tbl.columnname).Distinct()
group item by item into g
select new { var_text = g.Key, var_count = g.Count() };

Open in new window



-Kusala
0
 
OTMAuthor Commented:
thanks kusala, that lists the distinct entries correctly but lists the count for each one as 1. My aim is for the var_count field to be the total no. of records with the columnname text being var_text.

i hope this makes sense, thanks again.
0
 
OTMAuthor Commented:
kusala, thats great. It does exactly what i needed :-D
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.