?
Solved

convert sql query to linq query

Posted on 2011-03-16
5
Medium Priority
?
613 Views
Last Modified: 2013-11-11
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

0
Comment
Question by:OTM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35147631
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
 
LVL 11

Expert Comment

by:Kusala Wijayasena
ID: 35150758
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
 

Author Comment

by:OTM
ID: 35150971
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
 
LVL 11

Accepted Solution

by:
Kusala Wijayasena earned 2000 total points
ID: 35151064
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
 

Author Closing Comment

by:OTM
ID: 35152237
kusala, thats great. It does exactly what i needed :-D
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question