Running count on a column using linq

I have a table GAMES that looks like: Date, Team1, Team2, AtHome

and a sample entry might be

10/10/2010, TEAM A, TEAM B, TEAM B - meaning that TEAM B is playi开发者_如何学Pythonng at home for this game. There are lots of teams playing each other.

I'd like to use linq to get the following:

List all games where a team is at home, and has played the last 3 to 5 games at home.

Any suggestions on how to achieve this?

I whipped up a small example app. I'm not 100% sure if this matches what you are looking for, so post a comment if I missed the point of your question.

EDIT I revised it to take the date into account.

var games = new[]
{
    new Game() {Date = new DateTime(2010, 12, 1), TeamA = "A", TeamB="B", AtHome = "B"},
    new Game() {Date = new DateTime(2010, 11, 1), TeamA = "A", TeamB="B", AtHome = "A"},
    new Game() {Date = new DateTime(2010, 10, 1), TeamA = "A", TeamB="B", AtHome = "B"},
    new Game() {Date = new DateTime(2010, 9, 1), TeamA = "A", TeamB="B", AtHome = "A"},
    new Game() {Date = new DateTime(2010, 8, 1), TeamA = "A", TeamB="B", AtHome = "B"},
    new Game() {Date = new DateTime(2010, 7, 1), TeamA = "A", TeamB="C", AtHome = "A"},
    new Game() {Date = new DateTime(2010, 6, 1), TeamA = "B", TeamB="C", AtHome = "C"},
    new Game() {Date = new DateTime(2010, 5, 1), TeamA = "B", TeamB="C", AtHome = "C"},
    new Game() {Date = new DateTime(2010, 4, 1), TeamA = "D", TeamB="C", AtHome = "C"},
    new Game() {Date = new DateTime(2010, 3, 1), TeamA = "D", TeamB="B", AtHome = "D"},
    new Game() {Date = new DateTime(2010, 2, 1), TeamA = "D", TeamB="B", AtHome = "B"},
    new Game() {Date = new DateTime(2010, 1, 1), TeamA = "A", TeamB="D", AtHome = "D"},
    new Game() {Date = new DateTime(2009, 12, 1), TeamA = "A", TeamB="D", AtHome = "A"},
    new Game() {Date = new DateTime(2009, 11, 1), TeamA = "A", TeamB="B", AtHome = "A"},
    new Game() {Date = new DateTime(2009, 10, 1), TeamA = "A", TeamB="B", AtHome = "B"}
};

var result = from g in games
             orderby g.Date descending
             where games.Where(w => (w.TeamA == g.AtHome || w.TeamB == g.AtHome) 
                                    && w.Date <= g.Date)
                        .Take(5)
                        .Count(c => c.AtHome == g.AtHome) >= 3
             select g;


class Game
{
    public DateTime Date { get; set; }
    public string TeamA { get; set; }
    public string TeamB { get; set; }
    public string AtHome { get; set; }
}

This Returns:

12/1/2010 A B B
11/1/2010 A B A
9/1/2010 A B A
7/1/2010 A C A
6/1/2010 B C C

ok, this isn't going to be perfect LINQ, i'm not even sure of the performance.

var orderedList = lst.OrderByDescending(game => game.Date);
var test = orderedList.Where((game, index) => 
    game.AtHome == "Team" && (orderedList.Skip(index).Take(5).Count(chkGame => chkGame.AtHome == "Team") >= 3));

Basically, we order the list, then perform a where on it using the index, we check to see if the current game has our team as the home team, then we check the ordered list using the index. We skip up until the index, then take the next 5 games and count the number of times they were at home. If greater or equal to 3 then true and we have passed the selection.

Hope this helps!

Here is a solution, though not as efficient as possible. Note the only games where 3-5 previous games at home are found.

var games = new[]
                    {
                        new Game() {Date = new DateTime(2010, 12, 1), TeamA = "A", TeamB="B", AtHome = "B"},
                        new Game() {Date = new DateTime(2010, 11, 1), TeamA = "A", TeamB="B", AtHome = "B"},
                        new Game() {Date = new DateTime(2010, 10, 1), TeamA = "A", TeamB="B", AtHome = "B"},
                        new Game() {Date = new DateTime(2010, 9, 1), TeamA = "A", TeamB="B", AtHome = "B"},
                        new Game() {Date = new DateTime(2010, 8, 1), TeamA = "A", TeamB="B", AtHome = "B"},
                        new Game() {Date = new DateTime(2010, 7, 1), TeamA = "A", TeamB="C", AtHome = "A"},
                        new Game() {Date = new DateTime(2010, 6, 1), TeamA = "B", TeamB="C", AtHome = "C"},
                        new Game() {Date = new DateTime(2010, 5, 1), TeamA = "B", TeamB="C", AtHome = "C"},
                        new Game() {Date = new DateTime(2010, 4, 1), TeamA = "D", TeamB="C", AtHome = "D"},
                        new Game() {Date = new DateTime(2010, 5, 1), TeamA = "B", TeamB="C", AtHome = "C"},
                        new Game() {Date = new DateTime(2010, 4, 1), TeamA = "D", TeamB="C", AtHome = "D"},
                        new Game() {Date = new DateTime(2010, 1, 1), TeamA = "A", TeamB="D", AtHome = "D"},
                        new Game() {Date = new DateTime(2009, 12, 1), TeamA = "A", TeamB="D", AtHome = "D"},
                        new Game() {Date = new DateTime(2009, 11, 1), TeamA = "A", TeamB="B", AtHome = "A"},
                        new Game() {Date = new DateTime(2009, 10, 1), TeamA = "A", TeamB="B", AtHome = "B"}
                    };

            var ordered = games.OrderByDescending(g => g.Date);

            var result = ordered.Where(
                (g, i) =>
                {
                    var count = ordered
                        .Skip(i + 1) // only look at previous games
                        .Where(t => (t.TeamA == g.AtHome || t.TeamB == g.AtHome)) // get all games of the same team
                        .TakeWhile(inner => inner.AtHome == g.AtHome).Count(); // see how many consecutive games have been played at home

                    return (count >= 3) && (count <= 5);
                });

            foreach (var r in result)
            {
                Console.WriteLine(
                    string.Format("Date {0} TeamA: {1} TeamB: {2} AtHome {3}", r.Date, r.TeamA, r.TeamB, r.AtHome)
                    );
            }

Output will be :

Date 01/12/2010 00:00:00 TeamA: A TeamB: B AtHome B
Date 01/11/2010 00:00:00 TeamA: A TeamB: B AtHome B
Date 01/04/2010 00:00:00 TeamA: D TeamB: C AtHome D