LINQ to SQL Awesomeness

by breeve 11. March 2010 13:37

It is often said that Microsoft doesn’t innovate but simply copies existing ideas. To argue in opposition would be no small feat considering it doesn’t take long for anyone familiar with the tech industry to rattle off examples from Windows to the browser. However, LINQ is not one of those. LINQ was wrestled forth from the keen mind of Anders Hejlsberg—the author of Turbo Pascal and chief architect of Delphi—who joined Microsoft in 1996 after being lured out of Borland.

What bothered Anders the most when he joined Microsoft was not his reported 3 million dollar signing bonus but how disconnected the existing programming models were for different domains. To communicate with databases, for example, you must know SQL and how to write stored procedures. The more they thought about the problem the more they realized different programming models were most prevalent around data query. The solution was LINQ—which stands for Language INtegrated Query. For an organization better known for its late game heroics rather than innovative ideas, LINQ sheds the stereotype by delivering a solid piece of software engineering which can query everything from arrays of object to SQL databases all from C#.

I have been using LINQ to SQL for some home projects and love it. Before you think it must be a slow library abstraction consider that Joel Spolsky and the gang at StackOverflow, which gets 6 million unique visitors per month, have been running it from the beginning with no performance issues to date.

To get started with LINQ goodness let’s look at a recent project I have been working on that screen scrapes grocery ads from the web and stores them in a database. Below is my simple database schema shown from within SQL Server Management Studio 2005.

From the schema each store can have many ads and each ad can have many items. Now that the database is defined, we can use the LINQ to SQL designer in Visual Studio 2008 to generate our data access classes that LINQ runs against. To do so, right click the C# project in Visual Studio and select Add>>New Item. Select the Data Category and then the LINQ to SQL classes template in the right pane as shown below.

A new .dbml file will be add to the project. Double clicking on it will bring up the a designer surface. The next step is to connect to the database using Server Explorer and then drag the database tables onto the LINQ to SQL design surface. To bring up Server Explorer, click View>>Server Explorer in Visual Studio. When the Server Explorer window appears right click Data Connections and select Add Connection. Follow the wizard to connect to a database. Once this is done the Server Explorer window should look something like the screen shot below.

Now multi select all the tables and drag them onto the .dbml designer surface. You will get a warning about the connection string being stored in clear text. Click yes because the generated classes have connection string overloads that can be used later. Now the designer should look something like:

The generated code lives in a partial class underneath the .dbml file in Solution Explorer ending in designer.cs. On closer inspection of the generated code, notice there is a class for each table. LINQ to SQL doesn’t have the ability to generate model classes that are different from the actual tables. This functionality is reserved for the ADO.NET Entity Framework which Microsoft has decided is their future data access technology. As such, LINQ to SQL’s priority has been changed to maintenance only. Even so, it’s as powerful as ADO.NET Entity framework just less flexible.

Now with the SQL model created I can use LINQ to query and add values to the database. To add values, I implemented the visitor pattern to insert scraped data. The HebDataContext class, below, is the data context that was code generated by Visual Studio and is the key class which LINQ queries are run on. I created an instance of it and then do a LINQ query to see if the store exists in the database. If not, it is added and then the children of the store—the ads—are visited. The code looks like:

void IElementVisitor.VisitStore(ScrapeEngine.Modeling.Store storeModel)
{
    _currentStoreID = storeModel.HebID;
    using (HebDataContext context = new HebDataContext())
    {
        DataAccess.Store currentStore = context.Stores.Where(x => x.ID == storeModel.HebID).SingleOrDefault();
        if (currentStore == null)
        {
            DataAccess.Store store = new DataAccess.Store()
            {
                ID = storeModel.HebID,
                Name = storeModel.Name,
                Address = storeModel.Address,
                City = storeModel.City,
                State = storeModel.State,
                Zipcode = storeModel.Zipcode,
                Phone = storeModel.Phone
            };

            context.Stores.InsertOnSubmit(store);
            context.SubmitChanges();
        }
    }

    foreach (ScrapeEngine.Modeling.Ad ad in storeModel.Children)
        ad.AcceptVisitor(this);
}

Where LINQ to SQL really excels is the queries. Below is an example that gets all the items in a particular store's ads that are missing a price—this happens when the item’s price is dependent on buying another item—all from C#.

using (HebDataContext context = new HebDataContext())
{
    string[] items = context.Stores
        .Where(x => x.ID == 580)
        .SelectMany(x => x.Ads)
        .SelectMany(x => x.Items)
        .Where(x => x.Price == String.Empty)
        .OrderBy(x => x.Name)
        .Select(x => x.Name)
        .ToArray();

}

This query above gives the following when I poked in the debugger.

[0] "any four (4) Lean Cuisine Entrées"
[1] "Beneful Dry Dog Food"
[2] "Bumble Bee Solid White Tuna"
[3] "Cheetos"
[4] "Deviled Eggs Party Trays"
[5] "Eckrich Meat Smoked Sausage Links or Rope"
[6] "Eckrich Premium Beef Franks"
[7] "Fritos Corn Chips"
[8] "H-E-B Cafe Olé® Coffee"
[9] "H-E-B Chef Prepared Rotisserie Chicken"
[10] "H-E-B Chocolate Milk"
[11] "H-E-B Fresher Lasting® Chunky Guacamole Kit"
[12] "H-E-B Fully Cooked® Burgers"
[13] "H-E-B Fully Cooked® Shredded Chicken or Beef"
[14] "H-E-B inControl(TM) No Coding Test Strips"
[15] "H-E-B Ridged Potato Chips"
[16] "Hebrew National Beef Franks or Knockwurst"
[17] "Kiolbassa Value Pack Smoked Sausage"
[18] "Live Louisiana Crawfish"
[19] "Maxwell House Coffee"
[20] "Oscar Mayer Lunchmeat"
[21] "Planters Peanuts"
[22] "Sunshine Krispy Saltine Crackers"

Tags:

Software

Powered by BlogEngine.NET 1.5.0.7
Theme by Mads Kristensen

About Me

I am a Principal Engineer with 13 years experience developing and releasing software products. I started developing in C/C++ then moved into .NET and C# and have tech lead multiple projects. I have developed products in Windows Forms, ASP.NET/MVC, Silverlight, and WPF. I currently reside in Austin, Texas.

Own Projects

Pickaxe - An easy to use web page scraper. If you know a little SQL and how basic CSS selectors work, no web scraping product will be easier to use.

Download Page

Source Code

Created ASP.NET MVC forum originally targeting home owner associations but now in use by an investor group.

http://vtss.brockreeve.com

A language for querying PGATour golf strokes.

http://pga.brockreeve.com/

Real time bidder for car ads demo

http://cargawk.com/

Simple front end tic tac toe

http://tictac.brockreeve.com/

Currently Reading