Foray into LINQ - Matthew Manela

With the release of .NET 3.5 I started playing around with one of the cool new technologies called LINQ. LINQ brings SQL like querying syntax for performing operations on data sources like collections into , files or databases into .NET. The LINQ website describes LINQ as:

The LINQ Project is a codename for a set of extensions to the .NET Framework that encompass language-integrated query, set, and transform operations. It extends C# and Visual Basic with native language syntax for queries and provides class libraries to take advantage of these capabilities.

To truly understand why LINQ is so cool and useful you need to see an example of it in action. When learning the basics of LINQ I concocted an unrealistic program to help exhibit LINQ’s ability to join data from completely different sources together in a very clean and concise way.

I created a program which has three data sources.

SQL Database
XML File
In Memory Collection

Each of these data sources contains different pieces of information related to employees in a company. I have a table in the SQL database called Employees which contains 4 columns (ID, FirstName, LastName, JobCode). The data I have stored in this table is:

ID   FirstName   LastName   JobCode
 1    Matthew     Manela     1
 2    Boris       Dander     1
 3    John        Jacobs     2
 4    Sarah       Franks     2
 5    John        Manders    3
 6    Kreighton   Falla      1
 7    Jeremy      Jones      2
 8    William     Simons     4
 9    Steve       Lott       4
 10   Beth        Downs      2

The XML file’s contents are fairly self explanatory, just note that the Code element maps to the JobCode column in the Employees table.

<?xml version="1.0" encoding="utf-8" ?>
 <Jobs>
   <Job>
     <Code>1</Code>
     <Title>SDE</Title>
     <Description>Builds software for the benefit of the world</Description>
   </Job>
   <Job>
     <Code>2</Code>
     <Title>PM</Title>
     <Description>Makes sure the developers don’t bite off more than they can chew</Description>
   </Job>
   <Job>
     <Code>3</Code>
     <Title>Manager</Title>
     <Description>Makes sure the PMs dont get too frustrated with the developers</Description>
   </Job>
   <Job>
     <Code>4</Code>
     <Title>Head Honcho</Title>
     <Description>Makes sure the Managers dont get too frustrated with the PMs</Description>
   </Job>
 </Jobs>

The third data source is the in memory collection. This part is pretty unrealistic but I did it this way to have a different type of data source. The collection is a dictionary which maps a job code to a string which describes how many years a person with that job code has been at the company. The dictionary is defined as:

var yearsWorking = new Dictionary<int, string>
     {
          { 1, "1 to 5"},
          { 2, "1 to 5"},
          { 3, "6 to 15"},
          { 4, "15 or more"}
     };

(The above dictionary definition also uses two other new features: Implicitly typed local variables and object\collection initializers. )

With these three data sources I want to be able to join the job code from all three and be able to print our a string about each person which would look like:

Matthew Manela is a SDE with 1 to 5 years of experience
Builds software for the benefit of the world

Normally to do this wouldn’t be hard but would take a decent amount of code to read data from each source join them together and then print it out. With LINQ the code to do this is super concise and will be instantly pleasing to anyone who has ever done SQL:

//Joining SQL and XML AND in Memory Collection in LINQ
var query =
        from e in xmlRoot.Elements("Job")
        join u in db.Employees on e.Element("Code").Value equals u.JobCode.ToString()
        join y in yearsWorking on u.JobCode equals y.Key
        select new { Name = u.FirstName + " " + u.LastName,
                     Description = e.Element("Description").Value,
                     Title = e.Element("Title").Value,
                     Year= y.Value
        };

foreach (var line in query)
 {
     Console.WriteLine(line.Name + " is a " + line.Title + " with " + line.Year + " years of experience  " + " \n\t" + line.Description + "\n");
 }

Instead of having to manually go through each piece of data from each data source, LINQ does this all for you. You tell it what you want (just like in SQL) and it performs all the heavy lifting and tedious work for you behind the scenes.

This is just a small sampling of the power of LINQ but it is a good start and it got my feet wet.

I uploaded a Visual Studio 2008 solution containing all of the [download id=”16″ format=”4″] above.