Since I starting to work in C# 3.0 I’ve often had occasion to make use of LINQ (Language Integrated Query) queries to work with in-memory collections. My experience with SQL makes it natural for to me to think of this sort of data manipulation in terms of SQL queries, so here are some examples of a query in SQL followed by the LINQ equivalent.
Selecting maximum value
SELECT MAX(StartDate) FROM Staff
(from s in staff select (s.StartDate)).Max();
Grouping
SELECT Department, MIN(StartDate) FROM Staff GROUP BY Department
var groups = from s in staff group s by s.Department into g select new { Department = g.Key, StartDate = g.Min(x => x.StartDate) };
Grouping with an inner join
(This example returns the id of the most recent record for each name recorded for each person.)
SELECT p2.PersonName, MAX(p1.Id) AS Id FROM PersonDetails p1 INNER JOIN PersonDetails p2 ON p1.PersonID = p2.PersonID GROUP BY p1.PersonID, p2.PersonName
var names = from p1 in persons join p2 in persons on p1.PersonID equals p2.PersonID group p1 by new { p1.PersonID, p2.PersonName } into g select new { PersonName = g.Key.PersonName, Id = g.Max(x => x.Id) };
Points to bear in mind
- The
group
clause refers to the range variable (co1
), not the data source (companies
). - Grouping on multiple attributes is done by creating a new object
- You can assign an identifier (here,
g
) to the group using theinto
keyword. The properties that have been grouped can then be referred to with the prefixg.Key
(See Basic LINQ Query Operations for an explanation of the terminology.)
Other Reading
LINQ Syntax Fundamentals
An Introduction to LINQ – has sections on how the new language features in C# 3.0 are relevant.
Curious, I much prefer the functional approach… personal taste, I guess.
staff.Select(s => s.StartDate)
,staff.Max(s => s.StartDate)
,staff.GroupBy(s => s.Department).Something()
, etc.