LINQ to SQL 101

linqtosql-large

In this series of videos we’ll look at LINQ to SQL, covering the basic workflow, the Data Context and the Object Relational Designer. We’ll demonstrate how to install and use the LINQ to SQL Debug Visualizer, learn how and when LINQ to SQL queries are executed and look at the syntaxes for Insert, Updates and Deletes. Later in the series we’ll look at the anatomy of the Entity Classes in the Data Context as well as the anatomy of the XML mapping file, how to set Properties of Entity Class Members, how to call Stored Procedures, Anatomy of the XML Mapping File and tips for using LINQ to SQL as your Data Access Layer. Finally, we’ll look at the DataLoadOptions object, how to hook Data Context Events using Partial Methods, enabling Inheritance in the Object Relational Designer and finally creating Relationships between disparate Data Sources.

This series is organized into 4 days’ worth of material:

Day 1

LINQtoSQL101_01_01 – Introduction to the LINQ to SQL Workflow

In this video we’ll kick off a new series about one of the popular variants of LINQ and that is LINQ to SQL, which takes the concepts of LINQ and extends them to working with collections of data from a SQL Server database. LINQ to SQL adds additional concepts, objects and methods for not only retrieving sets of data from a database, but also inserting, updating and deleting data. We’ll also look at how to work with stored procedures, performance, working with ASP.NET and other similar considerations. Before you watch this series you should familiarize yourself with LINQ to Object syntax in the C# 301 or VB 301 series on LearnVisualStudio.NET since we’ll assume that you know basic LINQ syntax. Ok, so in this first video, I’ll just assume that you know absolutely nothing about LINQ to SQL specifically, and I’ll show you the basic series of steps for retrieving data from a SQL Server database to be displayed in a console window. The key to this video is that we’re goin

LINQtoSQL101_01_02 – Understanding the Data Context and the Object Relational Designer

In the previous video we demonstrated the entire workflow of using LINQ to SQL as the data access tier in your application. In this video, we’ll take a closer look at two of the main actors in LINQ to SQL, and that is the Object Relational Designer and the Data Context, providing access to the entity model generated by the ORD, as well as performing other crucial data tasks for us. We also look at the code generated by the ORD in the .designer.cs file (or .designer.vb file), renaming entities and properties in the ORD and talk about the internals of the Data Context.

LINQtoSQL101_01_03 – Debugging and Monitoring LINQ to SQL Queries

In the previous video I briefly mentioned that LINQ to SQL queries get translated into commands that SQL Server understands. Those commands are actual Transact SQL! In this video we’re going to sneak and watch the communication between the LINQ runtime and SQL Server by using several tools. First we’ll look at the tools built into VS 2008 SP1 for inspecting the results of a LINQ query, then we’ll examine the LINQ to SQL Debug Visualizer, which is a tool you add-in to Visual Studio, and then we’ll look at the LINQ to SQL Debug Writer, which is just a class that you add to your applications that outputs the generated T-SQL to the Debug window. Finally, we’ll pop open SQL Server Profiler to watch the traffic between LINQ to SQL and SQL Server.

LINQtoSQL101_01_04 – Understanding LINQ to SQL Query Execution

In the previous videos we said that LINQ will go to the database and grab the data it needs to satisfy the LINQ to SQL query syntax you write. We also looked at some ways to watch the execution of the query … like using that Debug Writer class and the SQL Profiler. So, I want to use this video to learn more about when and how often the LINQ to SQL runtime actually sends the request to SQL Server. So, we’ll look at the default behavior of how and when LINQ to SQL retrieves data from the database, then we’ll look at an option we can set in our Entity Model on our ORD to modify the loading behavior, namely the Delay Load property. We’ll also look at how LINQ to SQL retrieves a colection of related entities to a particular instance of an entity that we’re working with.

Day 2

LINQtoSQL101_02_01 – LINQ to SQL Update Syntax

In this video we’ll demonstrate the syntax required to update data in a SQL Server table using LINQ to SQL and the SubmitChanges method. It’s pretty straight forward, but we’ll also demonstrate how updating data across entity relationships is made easy as well.

LINQtoSQL101_02_02 – LINQ to SQL Insert Syntax

In this video I’ll demonstrate how to insert a row into the database using LINQ to SQL, and will show how to insert multiple rows all together at the same time. Specifically, we’ll look at the InsertOnSubmit and InsertAllOnSubmit methods of the Data Context.

LINQtoSQL101_02_03 – LINQ to SQL Delete Syntax

In this video we’ll demonstrate how to delete one or more rows of data using LINQ to SQL, and we’ll show how to delete an entity that has foreign key relationship. Specifically we’ll look at the DeleteOnSubmit and DeleteAllOnSubmit methods of the Data Context.

LINQtoSQL101_02_04 – LINQ to SQL Stored Procedures

In this video we’ll demonstrate how to work with Stored Procedures in LINQ, demonstrating how to add stored procs to the Entity Model, the .dbml file … and how to reference it in code … and we’ll have two examples … how to retrieve values returned by a stroed proc into an entity define in our .dbml file — so if your stored procedure is returning all columns from the Product table, for example — getting those results mapped into the Product entity and working with a sequence of Products … and then secondly I’ll show what happens when oyur stored proc is returning data that you can’t map to a entity … so, your stored proce is returning columns from multiple tables … what do you do? How does that work? We’ll show you how.

LINQtoSQL101_02_05 – LINQ to SQL Databinding to ASP.NET

In this video we’ll show a practical use of LINQ to SQL in an ASP.NET application. We’ll simply databind a GridView and later a DropDownList to sequences of entities defined in our .dbml file. And the key idea here is that you probably already know everything you need to know. Since you’re working with sequences of entities that implement IEnumerable, you can bind them to just about everything because the DataSource property on all databound controls needs classes that implement IEnumerable in order to work.

LINQtoSQL101_02_06 – Shaping and Paging LINQ to SQL Query Results

In this video we’ll look at a slightly more complicated LINQ to SQL projection. Sometimes this is called Shaping the results, so I used that word in the title, and as you’ll see you can get rather creative with what you extract from a LINQ to SQL query. In the second half of the video, we’ll look at how to page through a LINQ to SQL result set and display ten records at a time in an ASPX page using the Skip and Take extension methods.

Day 3

LINQtoSQL101_03_01 – Declarative ASP.NET Databinding with the LinqDataSource

In this video we’ll look at the LinqDatSource in ASP.NET that allows us to declaratively bind ASP.NET controls on a page to LINQ enabled data model. You might already be familiar with the SqlDataSource or the ObjectDataSource … the LinqDataSource is just like that, but uses the Entity model as it’s data source. One of the benefits of using the control is that it leverages the flexibility that the LINQ entity model provides. You don’t need to define custom query/insert/update/delete methods for the datasource to call – instead you can point the control at your data model, identify what entity table you want it to work against, and then bind any ASP.NET UI control against the and have them work with it. So, we’ll demonstrate how to configure the Linq data source, then we’ll work with the GridView control to display records from the Products table in our AdventureWorks database … and I imagine you’re already fami

LINQtoSQL101_03_02 – Filtering the LinqDataSource

In this video we’ll pick up the action where we left off to demonstrate how to filter the LinqDataSource that points to Product entities, to only display in our GridView those Products that belong to a certain category that our user can select from a drop down list.

Day 4

LINQtoSQL101_04_01 – Custom Validation Support

In this lesson we’ll start looking at a few of the more advanced features of LINQ to SQL. In this video, we’ll look at how to implement custom business rule validation utilizing the Entity model’s partial class and method definitions. First off, LINQ to SQL does it’s own simple validation to make sure that you are using the correct data types. But beyond that, you’ll need to implement your own business rule validation. Recall that when you are creating the Entity model using the design, you generate a .dbml file … and there are two other files associated with that .dbml file … an XML mapping file, as well as a .cs or .vb file that implements a DataContext and the Class definitions that represent the entities. You certainly could make your own custom changes to that file, but if you ever need to re-generate the .dbml file, you’ll lose all your changes. But the correct way to extend the Entity Model classes — you know, the .cs or .vb file that we just talked about –

LINQtoSQL101_04_02 – Hooking Entity Events using Partial Methods

In this video we’re going to pick up where we left off in the previous video by implementing two additional partial methods for our entities. We’ll use the OnCreated method to auto-populate the ID property of a new instances of an entity, which is particularly helpful when using uniqueidentifiers, or rather, GUIDs, as your primary key for your database tables as we do most often with all of our projects. Then we’ll look at the OnLoaded partial method to populate a new calculated property on our SalesOrderDetail entity at the moment when data is loaded from the database into our instances of our entity class.

LINQtoSQL101_04_03 – Using Stored Procedures for CRUD Operations

In this video we’ll further extend LINQ by demonstrating how to use Stored Procedures to perform Insert, Updates and Deletes instead of using the LINQ-generated SQL commands. Why would you want to do this? If your organization employs database administrators who are strict about what hits their database tables, then you might have to resort to using Stored Procs. The good news is that — as long as the stored proc is not overly convoluted — then LINQ can handle all this in stride. It’s a great feature, and it’s surprisingly easy to demonstrate.

LINQtoSQL101_04_04 – Enabling Inheritance in the Object Relational Designer

In this video, I’ll demonstrate how to enable inheritance in LINQ to SQL. Specifically, the type of inheritance LINQ to SQL allows us to implement is Table per Class Hierarchy, which means that you have a single table in the database with columns that apply to all derived classes, as well as columns that are specific to each distinct type of derived class. The LINQ to SQL designer allows us to define a discrimantor column so that it knows whether to create one derived type or a different derived type for a given row of data. Later in 2009 Linq to Entities will be available that will allow you to access data from other database vendors, and more germane to this topic, it provides you with additional type of object relational inheritance. We’ll talk more about that in an upcoming video series, Linq to Entities 101.

LINQtoSQL101_04_05 – Immediate Loading of Associated Table Data with DataLoadOptions

In this video we’ll look at the DataLoadOptions object. So, when LINQ to SQL sees that you want to access a property from a related entity … say for example myCustomer.Orders.OrderID, it will construct a query and go load the Order data at that moment. This is called lazy, or deferred loading. But as you might imagine, that could get out of hand if we are going to loop through all customers and therefore, all orders. We would be going back to the database for each customer to find all his orders. With DataLoadOptions, we can tell LINQ to SQL to load all the related Order records immediately in the same SQL statement. This is extremely important and will reduce the impact on your database dramatically. This is called Immediate or Eager loading.

LINQtoSQL101_04_06 – Editing the DBMLs XML Directly

In this video we’ll look at how to edit the XML of a .dbml directly using the XML Editor in Visual Studio 2008. Why would you want to do this? Some of the frustration people have with working in the Object Relational Designer is that if you change your table structure, you have to delete tables and re-drag them over. Getting in the habit of editing the DBML file’s xml by hand is a worthwhile time saver, and it has the added benefit of helping you understand what you’re doing, not just trusting your tools to do it all for you. You’ll see in a video at the end of this lesson that not only do we hand-craft our .dbml file, but we also hand-craft our database scripts to create tables, data, and so on. Maybe you’ll never want to go that route, and that’s fine. Just knowing that you can might serve helpful someday.

LINQtoSQL101_04_07 – Reviewing the ChangeList for a Transaction

In this video, we’ll look at how the DataContext keeps track of all the items that will be inserted, updated or deleted when you call SubmitChanges, and we’ll do it by calling the DataContext.GetChangeList method. Then we’ll take it one step further and intervene in the SubmitChanges process to determine whether or not a given business rule is not met on the items that are about the be inserted, updated or deleted. Why would we want to do that? There are times when adding validation logic can’t be done purely by looking at individual insert, upate or delete operations – and instead you want to be able to look at th entire change list of operations that are occurring for a transaction. By overriding the SubmitChanges method, we get a chance to make sure that, for example, you can’t save a SalesOrderHeader that doesn’t have any related SalesOrderDetail entities associated with it. That’s a pretty advanced scenario, but it’s entirely possible.

LINQtoSQL101_04_08 – Understanding SubmitChanges and Transactions

In this video we’ll look at what goes on when you call SubmitChanges. I’ve hinted at this in the previous videos, but I want to drive home the idea that every time you queue up changes using InsertOnSubmit, or DeleteOnSubmit, or make a change to an entity’s properties … all of those changes are added to a ChangeList that get translated into T-SQL and executed against the target database. However, all of those commands are wrapped in a transaction, so if one operation fails, they all fail. We’ll use SQL Profiler to watch the transaction behavior as we cause our application to purposefully fail when executing CRUD operations. Honestly, this video probably should have been in lesson 1, so I apologize for waiting until now. Hopefully this will answer any questions you had in your mind about how it works.