I love LINQ, and I wish I had more time to become really proficient in it. <sigh/> But there's so much to do and so little time, it's on a need to know basis. When I need to know it, I experiment and learn more, firing up LINQPad and pulling out my trusty copy of LINQ Pocket Reference by Joseph & Ben Albabari. A recent LINQ adventure was this:
from a DataTable containing names and times,
pull out the most recent DataRow for each name, provided it is inside a certain time window, and
present just those DataRows sorted by name
That's not hard for a human to think about, but translating it to LINQ took a while. In the process I rediscovered the lovely let function in LINQ, which aided readability, and made the returned set a simple IEnumerable<DataRow>. That's really helpful, because the typical result of a grouping operation is an IEnumerable of IEnumerables.
Here's the test DataTable as loaded up in LINQPad:
DataTable dt = new DataTable("SharePointUsers");
DataColumn LoginColumn = new DataColumn("Login", typeof(string));
dt.Columns.Add(LoginColumn);
DataColumn PageUrlColumn = new DataColumn("PageUrl", typeof(string));
dt.Columns.Add(PageUrlColumn);
DataColumn AccessTimeColumn = new DataColumn("AccessTime", typeof(DateTime));
dt.Columns.Add(AccessTimeColumn);
dt.BeginLoadData();
dt.LoadDataRow(new object[] { "Office14\\User3", "http://intranet/Default.aspx", DateTime.Now.AddMinutes(-5) }, true);
dt.LoadDataRow(new object[] { "Office14\\User3", "http://intranet/Default.aspx", DateTime.Now.AddSeconds(-119) }, true);
dt.LoadDataRow(new object[] { "Office14\\Admin", "http://intranet/Default.aspx", DateTime.Now.AddMinutes(-2) }, true);
dt.LoadDataRow(new object[] { "Office14\\Admin", "http://intranet/Default.aspx", DateTime.Now }, true);
dt.LoadDataRow(new object[] { "Office14\\User2", "http://intranet/Default.aspx", DateTime.Now.AddMinutes(-1) }, true);
dt.LoadDataRow(new object[] { "Office14\\User1", "http://intranet/Default.aspx", DateTime.Now.AddSeconds(-121) }, true);
dt.LoadDataRow(new object[] { "Office14\\User1", "http://intranet/Default.aspx", DateTime.Now.AddMinutes(-3) }, true);
dt.EndLoadData();
The time window will be 2 minutes (120 seconds). So if the AccessTime column value is within 2 minutes of right now, that row should be included for further evaluation. Those rows that pass the time test need to be grouped by names - which is the Login column. The LINQ group keyword essentially creates a list of lists. So the LINQ expression
from row in dt.AsEnumerable()
group row by row.Field<string>("Login") into userGroup
creates little lists for each name found, like this:
Ok, so far so good, but that query basically results in a list of lists. Each one of those little name-lists needs to be sorted by DateTime, and just the most recent one pulled out. This is where the let keyword shines. The
let keyword in LINQ creates a new variable right there in the middle of the LINQ query, or as Microsoft puts it, the let keyword "introduces a range variable to store sub-expression results in a query expression". And that new variable can be queryable! If the group keyword is followed by a let clause, then those name-lists can be sorted on DateTime, just the top one taken (the most recent one), and all that stored in a new variable, like this, where userGroup refers to those name-lists:
group row by row.Field<string>("Login") into userGroup
let lastAccessRows =
userGroup.OrderByDescending(r => r.Field<DateTime>("AccessTime"))
.Take(1)
Now we're cooking. Adding some initial filtering and final sorting and selecting, the complete query and supporting variables look like this:
Double timeWindow = 120;
DateTime startTime = DateTime.Now.AddSeconds(-timeWindow);
var query = from row in dt.AsEnumerable()
where // AccessTime is later than startTime
DateTime.Compare(row.Field<DateTime>("AccessTime"), startTime) > 0
group row by row.Field<string>("Login") into userGroup
let lastAccessRows =
userGroup.OrderByDescending(r => r.Field<DateTime>("AccessTime"))
.Take(1)
from dr in lastAccessRows
orderby dr.Field<string>("Login")
select dr;
Here it is in LINQPad:
If I were ever stranded on a desert island, with an inexplicable wireless network and a laptop, and I could only have three free tools, I'd grab LINQPad, Reflector (free version), and Fiddler - couldn’t live without them.
Hope this helps!