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

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 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.

Advertisements

About Jennifer Phillips Campbell

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

One 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

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s