Convert SQL query using 'count' clause, to LinQ

Question

My SQL Server query (below) works perfectly fine, and I'm trying to convert it to LinQ in C#.

SQL Query:

SELECT addressline3, city, COUNT(*) as 'InstitutionNumber'
FROM institutionenquiries
WHERE CITY = 'AHMEDABAD'
GROUP BY addressline3, city
ORDER BY city;

Desired output is:

I'm able to draw the LinQ query in below format, which gives me correct output
(except for COUNT(*) as 'InstitutionNumber').

LINQ Query:

var obj = (from u in dbContext.InstitutionEnquiry
           where u.City == data.Trim().ToLower()
           select new {
               AddressLine3 = u.AddressLine3.Trim().ToLower(),
               City = u.City.Trim().ToLower(),
               InstitutionNumber = (from a in dbContext.InstitutionEnquiry
                                    where a.City == data.Trim().ToLower()
                                    select a).Count()
           }).ToList();

This gives me count for 'InstitutionNumber' = 3 for all.

I'm not able to get the count aspect correctly. I've referred to this post's answer and it gives me only count, but I'm not able to nest it within my LinQ query.
Any helpful inputs will be appreciated.

Why are you converting 'data' to lowercase (u.City == data.Trim().ToLower()) when the 'u' from database is in uppercase? — jdweng
– jdweng, Commented Jan 21, 2017 at 15:52

Sergey Kalinichenko · Accepted Answer · 2017-01-21 15:05:16Z

1

The first observation is that since SQL has a GROUP BY, LINQ should have a GroupBy, too.

The second observation is that since non-aggregate columns in SQL are group by columns, the results you select in LINQ would come from the group's key.

Now we can write the query:

var res = dbContext.InstitutionEnquiry
.Where(u => u.City == data.Trim().ToLower())
.GroupBy(u => new {
    AddressLine3 = u.AddressLine3.Trim().ToLower(),
    City = u.City.Trim().ToLower()
 }).Select(g => new {
     g.Key.AddressLine3,
     g.Key.City,
     Count = g.Count()
}).ToList();

answered Jan 21, 2017 at 15:05

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

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

Comments

shA.t · Accepted Answer · 2017-01-21 15:10:30Z

1

I think you can use this LinQ :

var obj = dbContext.InstitutionEnquiry
        // WHERE CITY = 'AHMEDABAD'
        .Where(w => w.City == "AHMEDABAD")  // => Note           
        // GROUP BY addressline3, city
        .GroupBy(g => new {g.AddressLine3, g.City})
        // SELECT addressline3, city, COUNT(*) as 'InstitutionNumber'
        .Select(c => new {c.Key.AddressLine3, c.Key.City, InstitutionNumber = c.Count()})
        // ORDER BY city
        .OrderBy(o=> o.City)
        .ToList();

Note: For ignoring case sensitivity You can use :

.Where(string.Equals(w.City.Trim(), data.Trim(), StringComparison.CurrentCultureIgnoreCase))

edited Jan 21, 2017 at 15:10

answered Jan 21, 2017 at 15:04

shA.t

17k5 gold badges59 silver badges121 bronze badges

Comments

Manoj Pedvi · Accepted Answer · 2017-01-22 04:49:02Z

1

LINQ :

var obj = (from p in dbContext.InstitutionEnquiry
group p by new
{
    p.addressline3,
    p.city
} into grp
select new
{
    grp.Key.addressline3,
    grp.Key.city,
    InstitutionNumber = grp.Count(),
}).ToList();

Equivalent Lambda expression :

var obj = dbContext.InstitutionEnquiry
.GroupBy(p => new { p.addressline3, p.city})
.Select(p => new { p.Key.addressline3, p.Key.city, InstitutionNumber = p.Count() })
.ToList();

edited Jan 22, 2017 at 4:49

answered Jan 21, 2017 at 15:14

Manoj Pedvi

1698 silver badges15 bronze badges

Collectives™ on Stack Overflow

Convert SQL query using 'count' clause, to LinQ

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related