LINQ - LINQ to SQL

Intro w/Recap

In part 1, part 2, and part 3 we discussed LINQ basics, some more advanced techniques for dealing with objects in LINQ, and LINQ to XML. Here in our final post of the series we'll discuss LINQ to SQL, and briefly discuss Entity Framework in order to give some context to LINQ to SQL. What is LINQ to SQL? As you can guess, it is the usage of LINQ with data stored in a sql-compatible database such as MS SQL Server. For the purposes of our discussion we will be using SQL Express 2008 R2, though these samples will work fine with newer versions as well.

Entity Framework Basics

At this point hopefully you're wondering why I plan on discussing Entity Framework (EF). The reason is that LINQ to SQL works directly with Entity Framework objects. Entity Framework is MS's Object Relational Mapper (ORM) that helps save you time by linking your Plain-Old-CLR-Objects (POCO's) to your database. If you've ever had to link objects with database tables/fields you know it can be very tedious and time-consuming work, and ORM's help automate much of this tedium.

As part of today's LINQ lesson I'll walk you through how to get a database all setup and ready to map with EF, then I'll show you how to have EF automatically create your tables/fields based on object(s) that you've created in code.

Let's start with setting up the database. If you don't already have it, go download sql server express edition. You can find it here. Install that little sucker (get the one called "SQL Server Express With Tools") and then return. If you need help with installation, please post a reply here in the blog so that everyone can benefit from the shared knowledge. After you have completed installation, you're done! Yeah EF is so cool that it creates your database, tables, and columns for you. Can't get much easier than that. But hey we haven't done that yet so keep reading.

Code-First DB Setup w/EF

That's it for the database for now. We're going to use something called code-first setup of our EF, meaning we'll write our storage classes first and we'll use some spiffery to automatically create the tables for us.

I've created a couple classes myself for storage; Animal and Show. Here is the code for both:

using System.ComponentModel.DataAnnotations;
namespace BlogLinq
{
    public class Animal
    {
        [Key]
        public string name { get; set; }
        public string animalType { get; set; }
    }
}

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace BlogLinq
{
    public class Show
    {
        [Key]
        public string name { get; set; }
        public virtual List<Animal> animals { get; set; }
    }
}

As you can see, we have an Animal with 2 properties (name and animalType), and a Show with 2 properties (name and animals, which is a list of type Animal). We use the Key attribute to denote which field is the primary key for our class/table, so that EF can avoid creating a heap table. Trust me (or ask Bobby, my resident DBA, they're bad). The only other oddity here is that I made the animals property of the Show class virtual. This is necessary for EF to load such a list at runtime from a SQL data source.

Next you need to install the EntityFramework package from NuGet. If you don't, your code won't compile as that namespace and the Key attribute I used above are from EF. I'm assuming you know how to work with NuGet package manager, but if that's not the case feel free to ask in the comments below. Now you need to create a "Context" class, which is just a fancy way of saying that you need something that ties LINQ to your database table(s) and classes. Create a class called ZooContext. Here's the code of my ZooContext:

using System.Data.Entity;

namespace BlogLinq
{
    public class ZooContext : DbContext
    {
        public DbSet<Show> Shows { get; set; }
        public DbSet<Animal> Animals { get; set; }
    }
}

Because this blog post isn't about EntityFramework (let me know if you want to see such a beast!) I won't go into any further detail on this ZooContext class, so we'll just say for now that this class as defined above will let us use LINQ to query the database rather than having to roll our own SQL queries.

Inserting Data With EF

What fun is querying data when there's no data? none at all! We're going to write a quick method here that will insert data into our database and tables for us. "But Pete", you say, "We don't have a database, or tables, or columns!". Yeah I know that, and so does EF. Trust me, it will create this crap for you. Write a function like this:

        protected void btnEFInsert_Click(object sender, EventArgs e)
        {
            var zooContext = new ZooContext();
            var show = new Show() { name = "Early", animals = new List<Animal>() };
            show.animals.Add(new Animal() { animalType = "Bird", name = "George" });
            var show2 = new Show() { name = "Late", animals = new List<Animal>() };
            show2.animals.Add(new Animal() { animalType = "Ferret", name = "Fred" });
            show2.animals.Add(new Animal() { animalType = "Bear", name = "Bear" });
            zooContext.Shows.Add(show);
            zooContext.Shows.Add(show2);
            zooContext.SaveChanges();
        }

In the first line of our function we create an instance of our ZooContext class that links EF to our POCO's. We then proceed to create some shows, add animals to them, and add our shows (which contain the animals) to the zooContext. Then we call zooContext.SaveChanges(), and voila! EF created a database for us, a few tables, setup our columns, and even created primary and foreign keys for us. That's so darn cool I could pinch myself. Here's a screenshot full of awesomesauce:

Querying Data With LINQ to SQL

Back to LINQ...now that we have a little bit of data in our database our foray into pure EF is over, so let's query that data using LINQ. This should look pretty familiar by now:

        protected void btnLinqSqlSelect_Click(object sender, EventArgs e)
        {
            var zooContext = new ZooContext();
            var query = from show in zooContext.Shows
                        where show.name.Equals("Late", StringComparison.OrdinalIgnoreCase)
                        select show;
            foreach (var show in query)
            {
                Response.Write("show: " + show.name);
                foreach (var animal in show.animals)
                    Response.Write(String.Format("<br / >    {0} the {1} is in the {2} show!", animal.name, animal.animalType, show.name));
            }
        }

We start by creating a ZooContext object in order to pull junk from the database, then it's all just standard-looking LINQ from there. In the above LINQ query we retrieve all the shows (there's just 1) named "Late", and EF/LINQ goes and pulls all the shows and their child animals from the database for us. Simple and powerful stuff here!

Here's the output in case you don't trust me about the code working:
show: Late
    Bear the Bear is in the Late show!
    Fred the Ferret is in the Late show!

Drawbacks

With great power comes great headaches. The cool time-saving and readability afforded by LINQ comes with a price. Ask your local DBA what they think about ORM's and you'll get a good first-person ear-blasting about the evils of auto-generated queries. Fine-tuning a SQL database really is a profession unto itself, which is part of why the DBA was invented. EF and LINQ generate some decent SQL, but in many cases it's not optimized. This isn't much of a concern with small apps, but if you expect to scale, dig further into LINQ/EF and see what all options you can find for optimization.

What's Next?

• You could spend some time cozying up to Entity Framework. Learn things like changing the name of the database your data gets plopped in, view the sql queries it generates (it's still using sql behind the scenes), how to add new fields to your classes and have them added to the table(s), etc.

Resources

My Code (Created Using VS Express 2013)!
Entity Framework
SQL Express