Linq To Sql and the art of motorcycle maintenace

Now that my superb title has caught your eye and dragged you into the post…why not stay and read the whole thing?  What a treat!

My last 2 technical posts were about creating a MS Sql database using a Sql Database Project template in Visual Studio…and then about basic table and relational data design.  Today I’m going to build off the last post and we’re going to make some Linq to Sql classes out of the tables we created.

We ended up with 2 tables.  One called “Vehicle” that had a column which was an Id that refered to another tabled called “Engine”.  If you need a refresher, go take a peak at the post…I’ll wait………….okay, good to have you back.

So now we have this awesome database with those 2 tables…and we give them to the junkyard owner.  He gives you this crazy look because…well geez you just plopped a handful of electrons into his hands…yeah he’s going to look at you weird.  The other reason is he has no idea how to enter data into the database…clearly you have some work left to do like create some sort of interface for the guy to work with the database.

So lets aplogize to the junkyard owner and get back to work.  What do we use for an interface…back in the old days we used to SqlDataTable’s and spun through the tables hand crafting these elaborate object models to represent the tables as objects…then along came Linq which slowly has been creeping it’s way into our hearts…and now that whole processes has been automated.  Now it’s as easy as adding a new item into your .NET project by using a template called “Linq to SQL”.  It looks something like this…

LinqToSQLItem

Using this will bring up a design surface where you connect up to a database in your SQL Server Object Explorer and then drag your database objects over into your Linq to SQL design surface and presto…it goes and generates all the elaborate .NET objects to represent your database objects.  That looks something like this…

LinqToSqlDesignSurface

So once you get to this point…you “could” go take a peak at the objects it creates…but they are some crazy mystical things that just work so I wouldn’t bother digging into it.  The important part to remember is if you make any changes to your database, you must also come back to this design surface, delete, and then recreate the all the objects…it’s the safest most reliable way to go with Linq to Sql in my experience.

So I’ll give you the basic layout of the classes it creates…the actual code is much much more complex so what I’m showing is only the basic layout of properties.  I’ll also be using C# for my code layout…I don’t do VB.  🙂

First for Engine…

public class Engine
{
    public int Id { get; set; }

    public string Make { get; set; }

    public int Cylenders { get; set; }

    public string Displacement { get; set; }

    public int Horsepower { get; set; }
}

and then another for Vehicle…

public class Vehicle
{
    public int Id { get; set; }

    public int EngineId { get; set; }

    public Engine Engine { get; set; }

    public string Make { get; set; }

    public string Model { get; set; }

    public int Year { get; set; }

    public int WheelCount { get; set; }

    public int PassengerCapacity { get; set; }
}

So you can see in the Vehicle class, there’s not only the EngineId that points at a record to something in the Engine table…but there’s an actual object for the Engine hanging around as a property…and you better believe that it’s all ready to be filled up with the exact record the EngineId points at…but that’s the trick…it’s ALL READY to be filled up…it doesn’t actually contain the data until you go to use it.  This looks innocent and helpful, but it’s not when you’re doing recursive lookups through lists…it actually becomes a huge perf hit.  Let me explain.

So what’s actualy stored in that Engine property is a Sql query that gets executed once someone tries to use the object.  So imagine that we had a list of 100 engines and for some reason we needed to loop through them all to output to the console.

JunkyardJemsDataContext dataContext = new JunkyardJemsDataContext();

foreach(Engine engine in dataContext.Engines)
{
    Console.WriteLine($"Id          : {engine.Id}");
    Console.WriteLine($"Make        : {engine.Make}");
    Console.WriteLine($"Cylenders   : {engine.Cylenders}");
    Console.WriteLine($"Displacement: {engine.Displacement}");
    Console.WriteLine($"Horsepower  : {engine.Horsepower}");
}

What’s actually happening in the background is for each iteration of the loop it’s doing a Sql query to get all th details for just one record…so in our case that would be 100 Sql queries if we had 100 records.  That’s not so horrible.  But what if we did something like this and we had 100 Vehicles…

JunkyardJemsDataContext dataContext = new JunkyardJemsDataContext();

foreach(Vehicle vehicle in dataContext.Vehicles)
{
    Console.WriteLine($"Id          : {vehicle.Engine.Id}");
    Console.WriteLine($"Make        : {vehicle.Engine.Make}");
    Console.WriteLine($"Cylenders   : {vehicle.Engine.Cylenders}");
    Console.WriteLine($"Displacement: {vehicle.Engine.Displacement}");
    Console.WriteLine($"Horsepower  : {vehicle.Engine.Horsepower}");
}

On the surface it doesn’t look much different…but what’s really happened is you’ve at least doubled the amount of Sql queries going.  Since we said there’s 100 Vehicles…that’s 1 Sql query going on for each Vehicle…but it’s also another being done to get the Engine for the Vehicle…so now that’s 200 queries…and that’s with only one level of normalization.  Let’s say we decided to normalize out “Make” as well.  If we did that it could be used in both the Engine and Vehicle tables…lets’ pretend we did that.

Now there’s a query for the Vehicle, one for the Make, one for the Engine, which also does one for the Make…we’re up to 4 queries for just one thing…and we’ve only normalized out 2 pieces of data now.  Ok…now lets kick that up to 10,000 Vehicles.  Going from 10,000 Sql queries to 40,000 is a pretty big perf jump…it would be noticable.

One of the things I do to combat this is use the .ToList() method a lot.  If you go and do the following…it forces Linq to do the query and populate the results.

JunkyardJemsDataContext dataContext = new JunkyardJemsDataContext();
var vehicles = dataContext.Vehicles.ToList();
var engines = dataContext.Engines.ToList();

foreach(Vehicle vehicle in vehicles)
{
    var engine = engines.FirstOrDefault(e => e.Id == vehicle.EngineId);

    Console.WriteLine($"Id          : {engine.Id}");
    Console.WriteLine($"Make        : {engine.Make}");
    Console.WriteLine($"Cylenders   : {engine.Cylenders}");
    Console.WriteLine($"Displacement: {engine.Displacement}");
    Console.WriteLine($"Horsepower  : {engine.Horsepower}");
}

To me it looks like you’re creating more objects and wasting perf, but nope…this is way waaaaay faster than letting Linq to SQL do it’s behind the scene magic.  Doing the .ToList() on each table forces it to fill the “vehicles” and “engines” variables…then doing the .FirstOrDefault method is happening on the local collection and not the database.  Also notice I’m not making use of the Engine property that’s hanging off the Vehicle object…If you don’t use it…it won’t execute an extra sql query…so instead I use the EngineId in the FirstOrDefault call against the local collection where all the data is already filled out.

So there’s my Sql topics for now.  I’ve got some more advanced stuff to post about in the future, but these 3 should be enough to help any junkyard owner get up and running.  😉  Hope it helps.  Enjoy!

Advertisements
Linq To Sql and the art of motorcycle maintenace

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