Linq join, group, count where count greater than value

Question

I have the following linq expression that lets me join two tables, group them by a DSCID, and then get a count of the grouped values:

var qryGeoApppendCount =
              from a in append
              join g in geo
              on a.Field<string>("RNO")
              equals g.Field<string>("RNO")
              group g by g.Field<int>("DSCID") into appendGeo
              select new
              {
                DscId = appendGeo.Key,
                DscIdCount = appendGeo.Count()
              };

I need to take this just one step further by only selecting the counts greater than 1. I tried something like this:

select new
{
    DscId = appendGeo.Key,
    DscIdCount = appendGeo.Count(n => n.Count > 1)
};

but this didn't work. I need to be able to throw an error whenever qryGeoAppendQuery returns records with counts > 1, so ideally the query would be wrapped in an if statement.

score 4 · Accepted Answer · answered Oct 25 '10 at 18:32

var qryGeoApppendCount =
              (from a in append
              join g in geo
              on a.Field<string>("RNO")
              equals g.Field<string>("RNO")
              group g by g.Field<int>("DSCID") into appendGeo
              select new
              {
                DscId = appendGeo.Key,
                DscIdCount = appendGeo.Count()
              })
              .Where(a => a.DscIdCount > 1);

score 1 · Answer 2 · answered Oct 25 '10 at 18:32

Couldn't you just do...

select new
{
  DscId = appendGeo.Key,
  DscIdCount = appendGeo.Where(n => n.Count > 1).Count()
};

or if you just want to know if there exist any...

select new
{
  DscId = appendGeo.Key,
  ThrowException = appendGeo.Any(n => n.Count > 1)
};

score 0 · Answer 3 · edited Mar 12 '13 at 07:37

0

"SELECT  [SubsNumber], sum([Usage_MB])/1024 as GB FROM [VASCDR].[dbo].[nrmSubsDailyMBUsage] where SubsNumber ='" + textBox1.Text.Trim() + "'" group by [SubsNumber] ;

Is how to do it using c#

edited Mar 12 '13 at 07:37

Bernie

1,489
1
16
27

answered Mar 12 '13 at 07:08

chalana

1

score 0 · Answer 4 · answered Oct 25 '10 at 18:39

0

var qryGeoApppendCount =
          from a in append
          join g in geo
          on a.Field<string>("RNO")
          equals g.Field<string>("RNO")
          group g by g.Field<int>("DSCID") into appendGeo
          where appendGeo.Count() > 1
          select new
          {
            DscId = appendGeo.Key,
            DscIdCount = appendGeo.Count()
          };

Can't you add a where clause before the select? It worked in my example but I'm not sure without seeing the data.

answered Oct 25 '10 at 18:39

Dismissile

32,564
38
174
263

I was hoping to count where duplicate DSCID values occur in the joined table, not simply whether the joined table had a record count greater than 1. Thanks a lot though for the reply. – jrubengb Oct 25 '10 at 18:46

Linq join, group, count where count greater than value

4 Answers4