CODEWITHSUNDEEP
c#sql-serverlinqc#-4.0linq-to-entities
DooDoo
DooDoo

Reputation: 13447

Get number of records n a group in LINQ

I wrote a LINQ query that it' result similar to this :

var res = from a  in tbl
          group a by {a.StateCode,a.CityCode} into grp
          //where grp.Count() == 1
          select ...

StateCode       CityCode           ......
------------------------------------------
01               001               ......
01               002               ......
02               001               ......
03               001               ......
03               002               ......
03               003               ......

indeed I want to get just 02 001 record because it has one result row in the final result.If I add commented code It returns count of records correspond to each group not count of own group.I know I can do this with two group by but is there a better way to get row that has (for example) one row in the result?

Upvotes: 0

Views: 1420

Answers (2)

pescolino
pescolino

Reputation: 3123

You are grouping by StateCode and CityCode so each group has only one element. You must group by StateCode only and inlcude where grp.Count() == 1 again.

Each group contains the StateCode as its key and an enumeration of the elements. Since you already filtered for groups with only a a single element you can just return the first of each group:

var res = from a in tbl
          group a by a.StateCode into grp
          where grp.Count() == 1
          select grp.FirstOrDefault();

Upvotes: 1

acfrancis
acfrancis

Reputation: 3671

How about this:

(from b in Tbls
where (from a in Tbls
       where a.StateCode == b.StateCode
       group a by a.StateCode into grp
       where grp.Count () == 1
       select grp).Count() > 0
select b).Dump();

Here's the SQL I used to test it with:

create table tbl (StateCode varchar(5), CityCode varchar(5))

insert tbl values ('01', '001')
insert tbl values ('01', '002')
insert tbl values ('02', '001')
insert tbl values ('03', '001')
insert tbl values ('03', '002')
insert tbl values ('03', '003')

Upvotes: 0

Related Questions