Linq count of records in a list

Question

Trying to use expresisons and predicates to group a list by two types and take the count. I have two lists: yearList and supList. They both contain columns called FIPS and YEAR and what I need to do is in one list I loop through all the possible FIPS and Years (yearList). I then need to find the count of how many times that FIPS and Year appears in the second list(supList) and write that int value to yearlLst. Below you will find a sample of what the data looks like so for example when FIPS=1001 and Year=2001 form yearList is passed in I should return a count of 1 from my supList and write that value to y.countEnviofnd in my yearList and when FIPS=1001 and Year=2002 is passed in count=2 should be returned. Ive tried multiple wyas to try and do this but can't seem to get it.

This would be the sample list that I need to compare against and find the counts from: this would be supList in my code sample below.

FIPS    YEAR
------------
1001    2001
1001    2002
1003    2006
1002    2001
1001    2002
1003    2005
1003    2006

This would be all the possible FIPS and Years I could possibly have: starts at 1001 for fips and increases but also each fips has a record for year starting at 2001 and going up the 2004. There are lots more but here is a sample. In my code below this would be the yearList

FIPS    DATE
------------
1001    2001
1001    2002
1001    2003
1001    2004
1002    2001
1002    2002
1002    2003
1002    2004
1003    2001
1003    2002
1003    2003
1003    2004

Ive tried all these LINQ expressions and nothing seems to do it. It either returns 0 or 1 for everything.

foreach(Year y in yearList)
{
    int countY = supList.Where(p => p.FIPS == Convert.ToString(y.FIPS) && p.year==y.year).Count();
    int countY = supList.GroupBy(p => p.FIPS == Convert.ToString(y.FIPS) && p.year==y.year).Count();
    int countY  = supList.FindAll(i => i.FIPS == Convert.ToString(y.FIPS) && i.year == y.year).Count(); 

    y.countenviofnd = countY;
}   

Answer 1

Using Where() and Count() will work, here's a sample I made in LINQPad 4:

void Main()
{
    var supList = new List<Data>() {
        new Data { FIPS = 1001, Year = 2001 },
        new Data { FIPS = 1001, Year = 2002 },
        new Data { FIPS = 1003, Year = 2006 },
        new Data { FIPS = 1002, Year = 2001 },
        new Data { FIPS = 1001, Year = 2002 },
        new Data { FIPS = 1003, Year = 2005 },
        new Data { FIPS = 1003, Year = 2006 }
    };

    var yearList = new List<Data>() {
        new Data { FIPS = 1001, Year = 2001 },
        new Data { FIPS = 1001, Year = 2002 },
        new Data { FIPS = 1001, Year = 2003 },
        new Data { FIPS = 1001, Year = 2004 },
        new Data { FIPS = 1002, Year = 2001 },
        new Data { FIPS = 1002, Year = 2002 },
        new Data { FIPS = 1002, Year = 2003 },
        new Data { FIPS = 1002, Year = 2004 },
        new Data { FIPS = 1003, Year = 2001 },
        new Data { FIPS = 1003, Year = 2002 },
        new Data { FIPS = 1003, Year = 2003 },
        new Data { FIPS = 1003, Year = 2004 },
    };

    foreach (var year in yearList)
    {
        supList
            .Where(p => p.FIPS == year.FIPS && p.Year == year.Year)
            .Count()
            .Dump(string.Format("Using Where().Count() for [FIPS:{0}, Year:{1}]", year.FIPS, year.Year));
    }
}

// Define other methods and classes here
class Data
{
    public int FIPS { get; set; }
    public int Year { get; set; }
}

I got the following output:

Using Where().Count() for [FIPS:1001, Year:2001] 
1 

Using Where().Count() for [FIPS:1001, Year:2002] 
2 

Using Where().Count() for [FIPS:1001, Year:2003] 
0 

Using Where().Count() for [FIPS:1001, Year:2004] 
0 

Using Where().Count() for [FIPS:1002, Year:2001] 
1 

Using Where().Count() for [FIPS:1002, Year:2002] 
0 

Using Where().Count() for [FIPS:1002, Year:2003] 
0 

Using Where().Count() for [FIPS:1002, Year:2004] 
0 

Using Where().Count() for [FIPS:1003, Year:2001] 
0 

Using Where().Count() for [FIPS:1003, Year:2002] 
0 

Using Where().Count() for [FIPS:1003, Year:2003] 
0 

Using Where().Count() for [FIPS:1003, Year:2004] 
0 

Note that I made my data of type int, avoiding any possible issues there. I would suggest you do the conversion once during/just-after data retrieval.

Also note that with the data you provided, only one FIPS/Year combo has more than 1 record (the second output).

Depending on your needs, you could probably do better with some kind of GroupBy expression.

UPDATE: for performance, assuming data types are already converted to compatible types you can add the following query to do counts per valid combo all at once:

var query = from year in yearList
            join sup in supList 
                on new { year.FIPS, year.Year } equals new { sup.FIPS, sup.Year }
            group sup by new { sup.FIPS, sup.Year } into p
            select new { p.Key.FIPS, p.Key.Year, Count = p.Count() };
query.Dump("join/group query");

The Dump() statement is just for LINQPad output, you could use ToList() or something. The result is 3 rows as follows:

FIPS Year Count
==== ==== =====
1001 2001 1
1001 2002 2
1002 2001 1

Note that it is a little more complex since I'm grouping by more than one key (thus the anon object with FIPS+Year).