Populate list using LINQ

Question

I have an ArrayList of type RawResults where RawResults is a location and a date

public class RawResult
{
    public string location { get; set; }
    public DateTime createDate {get; set; }

    public RawResults(string l, DateTime d)
    {
        this.location = l;
        this.createDate = d;
    }
}

I would like to use LINQ to populate a list containing each distinct location and the number of times it appears in my arraylist. If I able to do it in SQL it would look like this

select 
   bw.location, 
   count(*) as Count
from 
   bandwidth bw, 
   media_log ml
where
   bw.IP_SUBNET = ml.SUBNET
   group by bw.location
   order by location asc

later I will also have to do the same thing, but within a given date range.

UPDATE this is the query that was run to get all of the data in rawData

SELECT        
    MEDIASTREAM.BANDWIDTH.LOCATION, MEDIASTREAM.MEDIA_LOG.CREATE_DATE
FROM            
    MEDIASTREAM.BANDWIDTH INNER JOIN
      MEDIASTREAM.MEDIA_LOG ON MEDIASTREAM.BANDWIDTH.IP_SUBNET =     
      MEDIASTREAM.MEDIA_LOG.SUBNET

Now I need to query the data that was returned in rawData to get different result sets. I have available a List to query.

Answer 1

You can do this:

var results = 
    (from bw in data.bandwith
     join ml in data.media_log on bw.IP_SUBNET equals ml.SUBNET
     group bw by bw.location into g
     orderby g.Key
     select new 
     { 
         location = g.Key, 
         Count = g.Count() 
     })
    .ToList();

Although the ToList is not necessary unless you absolutely need it to be a List<T>. To filter by time, you can just do something like this:

var results = 
    (from bw in data.bandwith
     join ml in data.media_log on bw.IP_SUBNET equals ml.SUBNET
     where bw.createDate >= minDate && bw.createDate <= maxDate
     group bw by bw.location into g
     orderby g.Key
     select new 
     { 
         location = g.Key, 
         Count = g.Count() 
     })
    .ToList();

---

If media_log isn't relevant, you can just omit the join:

var results = 
    from bw in data.bandwith
    group bw by bw.location into g
    orderby g.Key
    select new 
    { 
        location = g.Key, 
        Count = g.Count() 
    }

Or in fluent syntax:

var results = data.bandwith
    .GroupBy(bw => bw.location, (k, g) => new { location = k, Count = g.Count() })
    .OrderBy(r => r.location);

To filter by date, just use this:

var results = 
    from bw in data.bandwith
    where bw.createDate >= minDate && bw.createDate <= maxDate
    group bw by bw.location into g
    orderby g.Key
    select new 
    { 
        location = g.Key, 
        Count = g.Count() 
    };

Or in fluent syntax:

var results = data.bandwith
    .Where(bw => bw.createDate >= minDate && bw.createDate <= maxDate)
    .GroupBy(bw => bw.location, (k, g) => new { location = k, Count = g.Count() })
    .OrderBy(r => r.location);

Note, to use an ArrayList, or any other non-generic collection type in a Linq query, use the Cast<T> or OfType<T> methods, like this:

var results = bandwithArrayList
    .Cast<RawResults>()
    .GroupBy(bw => bw.location, (k, g) => new { location = k, Count = g.Count() })
    .ToList();