Linq extract a count() value from a data object

I have divAssignments that has potential multiple rows by rNI, an official id, according to a compound key of Indictment and booking numbers.

rNI    Booking  Indictment
12345  954445   10 12345
12345  954445   10 12346
12345  954445   10 12347

So ID has a count of 3 for a single booking number for this rni.

I get lost attem开发者_如何学Gopting to generate a count and a group by booking Number:

var moreThen = from dA in divAssignments
    select new { dA.rNI, IndictmentCount = dA.indictmentNumber.Count() };

Most of the examples are dealing with static int[] and don't seem to work in my case.

How do I get a group and then a count? If I could put in a having that would be fantastic.

from a t-sql POV I'd use this:

Select rni, bookingNumber, count(*) IndictmentCount 
from divAssignments
group by rni, bookingNumber  
having count(*) > 0 

TIA

How about something like this:

var query = from item in divAssignments
group item by item.rNI into grouping
select new
{
  Id = grouping.Key,
  Count = grouping.Count()
}

If you're interested in grouping by both the rNI and the booking number, I would change it to this:

var query = from item in divAssignements
   group item by new { item.rNI, a.Booking } into grouping
   select new
    {
      Id = grouping.Key,
      Count = grouping.Count
    };

OR

var query = from item in divAssignments
    group item by item into grouping
    select new
    {
      Id = grouping.Key,
      Count = grouping.Count()
    }

and implement IEquatable on the divAssignment object to support equality comparison. The other option if you'd like is to write an IEqualityComparer instance to do the composite key comparison. Your query could then look like:

var query = 
  divAssignments
      .GroupBy(i => i, new MyCustomEqualityComparer())
      .Select(i => new { Key = i.Key, Count = i.Count());

var query =
    from dA in divAssignments
    group dA by new { dA.rNI, dA.bookingNumber };

foreach(var grp in query)
{
    Console.WriteLine("rNI={0}, bookingNumber={1} => Count={2}", grp.Key.rNI, grp.Key.bookingNumber, grp.Count());
}

If you use a Grouping operator in Linq you will get what you need. The code:

  var count = from a in divAssignments
              group a by new { a.rNI, a.Booking } into b
              select b;

will return a collection of IGrouping objects. This will give you the Key (in my example this will be an anonymous type with an rNI and a Booking property) and a collection of the divAssignments that match the key.

Using Method syntax (much easier to read in my opinion):

First group the records, then select a new result for each group that contains the count.

var groups = divAssignments.GroupBy(d => new { d.rNI, d.Booking });
groups.Select(g=> new { g.Key.rNI, g.Key.Booking, IndictmentCount = g.Count() });