Linq distinct - Count

I am looking to perform a query on an example list of objects

Date     Username


01/01/2011 james
01/01/2011 jamie
01/01/2011 alex
01/01/2011 james
02/01/2011 matt
02/01/2011 jamie
02/01/2011 alex
02/01/2011 james
02/01/2011 james
02/01/2011 lucy
02/01/2011 alex
03/01/2011 james
03/01/2011 bob
03/01/2011 bob
03/01/2011 james
03/01/2011 james
04/01/2011 alex
04/01/2011 alex
04/01/2011 alex

I want to use linq to query the list of dates with the number of unique user logins.

For example:

01/01/2011 - 3
02/01/2011 - 5
03/01/2011 - 2
04/01/2011 - 1

I have tried as tested a number of linq statements but none of these are giving me the desired result. The closest I have got is giving me the distinct dates but with a count of all the users.

Any help would be greatly appreciated.

115675 次浏览
小开
最佳答案 
logins
.GroupBy(l => l.Date)
.Select(g => new
{
Date = g.Key,
Count = g.Select(l => l.Login).Distinct().Count()
});
小开

Something like this maybe?

var list = new List<MyClass>(new[] {
new MyClass { Date = DateTime.Parse("01/01/2011"), Username = "james" },
new MyClass { Date = DateTime.Parse("01/01/2011"), Username = "james" },
new MyClass { Date = DateTime.Parse("01/01/2011"), Username = "alex" },
new MyClass { Date = DateTime.Parse("01/01/2011"), Username = "james" },
new MyClass { Date = DateTime.Parse("02/01/2011"), Username = "matt" },
new MyClass { Date = DateTime.Parse("02/01/2011"), Username = "jamie" },
new MyClass { Date = DateTime.Parse("02/01/2011"), Username = "alex" },
new MyClass { Date = DateTime.Parse("02/01/2011"), Username = "james" },
new MyClass { Date = DateTime.Parse("02/01/2011"), Username = "james" },
new MyClass { Date = DateTime.Parse("02/01/2011"), Username = "lucy" },
new MyClass { Date = DateTime.Parse("02/01/2011"), Username = "alex" },
new MyClass { Date = DateTime.Parse("03/01/2011"), Username = "james" },
new MyClass { Date = DateTime.Parse("03/01/2011"), Username = "bob" },
new MyClass { Date = DateTime.Parse("03/01/2011"), Username = "bob" },
new MyClass { Date = DateTime.Parse("03/01/2011"), Username = "james" },
new MyClass { Date = DateTime.Parse("03/01/2011"), Username = "james" },
new MyClass { Date = DateTime.Parse("04/01/2011"), Username = "alex" },
new MyClass { Date = DateTime.Parse("04/01/2011"), Username = "alex" },
new MyClass { Date = DateTime.Parse("04/01/2011"), Username = "alex" }
});


list.GroupBy(l => l.Date, l => l.Username)
.Select(g => new {
Date = g.Key,
Count = g.Distinct().Count()
});
小开

Another way to solve this is to group twice, check the sample

          var dist = listLogins.GroupBy(d => d.date + d.Username)
.Select(x => x.First())
.GroupBy(d => d.date).Select(y => new { date = y.Key, count = y.Count() }).ToList();
小开

Can be done within single GroupBy call,

  var Query = list.GroupBy(
(item => item.DateTime),
(key, elements) => new  {
key = key,
count = elements
.Distinct()
.Count()
}
);
小开

I realize this is an ancient question but I ran across it and saw the comment about wanting method syntax and couldn't help myself to answer it... I may have a coding disorder.

In query syntax it looks like this... note that there is no query syntax for Distinct and Count

from l in logins
group l by l.Date into g
select new
{
Date = g.Key,
Count = (from l in g select l.Login).Distinct().Count()
};

For a side by side comparison to the original method syntax (which personally I like better) here you go...

logins
.GroupBy(l => l.Date)
.Select(g => new
{
Date = g.Key,
Count = g.Select(l => l.Login).Distinct().Count()
});
小开

I don't think there is need of Distinct count in Oryol's answer. The groupup automatically picks distinct keys and it can be accessed as g.key

logins
.GroupBy(l => l.Date)
.Select(g => new
{
Date = g.Key,
Count = g.Select(l => l.Login).Count()
});

提交答案