From SQL to LINQ

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

FROM Staff

(from s in staff select (s.StartDate)).Max();


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 the into keyword. The properties that have been grouped can then be referred to with the prefix g.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.

About Jennifer Phillips Campbell

Software Developer and Medieval Historian
This entry was posted in Uncategorized. Bookmark the permalink.

1 Response to From SQL to LINQ

  1. André Neves says:

    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.

Leave a Reply to André Neves Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s