LINQ Quick Overview

by Mirek Kordos

LINQ (Language-Integrated Query) enables you to build queries over data directly from within any .NET-based programming language. LINQ is thought of to be a common interface between any programming language and any type of data. Below is a sample C#/SQL application.

public partial class _Default : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{

        MyLINQDataContext db = new MyLINQDataContext();
        var pr = from p in db.Persons
        where p.FirstName == "Franciszek"
        select p;

GridView1.DataSource = pr;
GridView1.DataBind();

}

Source code

LINQ Providers

by Branimir Giurov www.sofiadev.org

Using LINQ to SQL (A Closer Look)

by Scott Guthrie

Part 1

Over the last few months I wrote a series of blog posts that covered some of the new language features that are coming with the Visual Studio 2008 and .NET Framework 3.5 release. Here are pointers to the posts in my series:

The above language features help make querying data a first class programming concept. We call this overall querying programming model "LINQ" - which stands for .NET Language Integrated Query.

Developers can use LINQ with any data source. They can express efficient query behavior in their programming language of choice, optionally transform/shape data query results into whatever format they want, and then easily manipulate the results. LINQ-enabled languages can provide full type-safety and compile-time checking of query expressions, and development tools can provide full intellisense, debugging, and rich refactoring support when writing LINQ code.

LINQ supports a very rich extensibility model that facilitates the creation of very efficient domain-specific operators for data sources. The the .NET Framework 3.5 ships with built-in libraries that enable LINQ support against Objects, XML, and Databases.

What Is LINQ to SQL?

LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework 3.5 release, and which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it.

LINQ to SQL fully supports transactions, views, and stored procedures. It also provides an easy way to integrate data validation and business logic rules into your data model.

Modeling Databases Using LINQ to SQL:

Visual Studio 2008 ships with a LINQ to SQL designer that provides an easy way to model and visualize a database as a LINQ to SQL object model. My next blog post will cover in more depth how to use this designer (you can also watch this video I made in January to see me build a LINQ to SQL model from scratch using it).

Using the LINQ to SQL designer I can easily create a representation of the sample "Northwind" database like below:

My LINQ to SQL design-surface above defines four entity classes: Product, Category, Order and OrderDetail. The properties of each class map to the columns of a corresponding table in the database. Each instance of a class entity represents a row within the database table.

The arrows between the four entity classes above represent associations/relationships between the different entities. These are typically modeled using primary-key/foreign-key relationships in the database. The direction of the arrows on the design-surface indicate whether the association is a one-to-one or one-to-many relationship. Strongly-typed properties will be added to the entity classes based on this. For example, the Category class above has a one-to-many relationship with the Product class. This means it will have a "Categories" property which is a collection of Product objects within that category. The Product class then has a "Category" property that points to a Category class instance that represents the Category to which the Product belongs.

The right-hand method pane within the LINQ to SQL design surface above contains a list of stored procedures that interact with our database model. In the sample above I added a single "GetProductsByCategory" SPROC. It takes a categoryID as an input argument, and returns a sequence of Product entities as a result. We'll look at how to call this SPROC in a code sample below.

Understanding the DataContext Class

When you press the "save" button within the LINQ to SQL designer surface, Visual Studio will persist out .NET classes that represent the entities and database relationships that we modeled. For each LINQ to SQL designer file added to our solution, a custom DataContext class will also be generated. This DataContext class is the main conduit by which we'll query entities from the database as well as apply changes. The DataContext class created will have properties that represent each Table we modeled within the database, as well as methods for each Stored Procedure we added.

For example, below is the NorthwindDataContext class that is persisted based on the model we designed above:

LINQ to SQL Code Examples

Once we've modeled our database using the LINQ to SQL designer, we can then easily write code to work against it. Below are a few code examples that show off common data tasks:

1) Query Products From the Database

The code below uses LINQ query syntax to retrieve an IEnumerable sequence of Product objects. Note how the code is querying across the Product/Category relationship to only retrieve those products in the "Beverages" category:

2) Update a Product in the Database

The code below demonstrates how to retrieve a single product from the database, update its price, and then save the changes back to the database:

3) Insert a New Category and Two New Products into the Database

The code below demonstrates how to create a new category, and then create two new products and associate them with the category. All three are then saved into the database.

Note below how I don't need to manually manage the primary key/foreign key relationships. Instead, just by adding the Product objects into the category's "Products" collection, and then by adding the Category object into the DataContext's "Categories" collection, LINQ to SQL will know to automatically persist the appropriate PK/FK relationships for me.

4) Delete Products from the Database

The code below demonstrates how to delete all Toy products from the database:

5) Call a Stored Procedure

The code below demonstrates how to retrieve Product entities not using LINQ query syntax, but rather by calling the "GetProductsByCategory" stored procedure we added to our data model above. Note that once I retrieve the Product results, I can update/delete them and then call db.SubmitChanges() to persist the modifications back to the database.

6) Retrieve Products with Server Side Paging

The code below demonstrates how to implement efficient server-side database paging as part of a LINQ query. By using the Skip() and Take() operators below, we'll only return 10 rows from the database - starting with row 200.

Part 2 - Defining our Data Model Classes

In Part 1 of my LINQ to SQL blog post series I discussed "What is LINQ to SQL" and provided a basic overview of some of the data scenarios it enables.

In my first post I provided code samples that demonstrated how to perform common data scenarios using LINQ to SQL including:

How to query a database
How to update rows in a database
How to insert and relate multiple rows in a database
How to delete rows in a database
How to call a stored procedure
How to retrieve data with server-side paging

I performed all of these data scenarios using a LINQ to SQL class model that looked like the one below:

In this second blog post in the series I'm going to go into more detail on how to create the above LINQ to SQL data model.

LINQ to SQL, the LINQ to SQL Designer, and all of the features that I'm covering in this blog post series will ship as part of the .NET 3.5 and Visual Studio 2008.

Create a New LINQ to SQL Data Model

You can add a LINQ to SQL data model to an ASP.NET, Class Library or Windows client project by using the "Add New Item" option within Visual Studio and selecting the "LINQ to SQL" item within it:

Selecting the "LINQ to SQL" item will launch the LINQ to SQL designer, and allow you to model classes that represent a relational database. It will also create a strongly-typed "DataContext" class that will have properties that represent each Table we modeled within the database, as well as methods for each Stored Procedure we modeled. As I described in Part 1 of this blog post series, the DataContext class is the main conduit by which we'll query entities from the database as well as apply changes back to it.

Below is a screen-shot of an empty LINQ to SQL ORM designer surface, and is what you'll see immediately after creating a new LINQ to SQL data model:

Entity Classes

LINQ to SQL enables you to model classes that map to/from a database. These classes are typically referred to as "Entity Classes" and instances of them are called "Entities". Entity classes map to tables within a database. The properties of entity classes typically map to the table's columns. Each instance of an entity class then represents a row within the database table.

Entity classes defined with LINQ to SQL do not have to derive from a specific base class, which means that you can have them inherit from any object you want. All classes created using the LINQ to SQL designer are defined as "partial classes" - which means that you can optionally drop into code and add additional properties, methods and events to them.

Unlike the DataSet/TableAdapter feature provided in VS 2005, when using the LINQ to SQL designer you do not have to specify the SQL queries to use when creating your data model and access layer.

Instead, you focus on defining your entity classes, how they map to/from the database, and the relationships between them. The LINQ to SQL OR/M implementation will then take care of generating the appropriate SQL execution logic for you at runtime when you interact and use the data entities. You can use LINQ query syntax to expressively indicate how to query your data model in a strongly typed way.

Creating Entity Classes From a Database

If you already have a database schema defined, you can use it to quickly create LINQ to SQL entity classes modeled off of it.

The easiest way to accomplish this is to open up a database in the Server Explorer within Visual Studio, select the Tables and Views you want to model in it, and drag/drop them onto the LINQ to SQL designer surface:

When you add the above 2 tables (Categories and Products) and 1 view (Invoices) from the "Northwind" database onto the LINQ to SQL designer surface, you'll automatically have the following three entity classes created for you based on the database schema:

Using the data model classes defined above, I can now run all of the code samples (expect the SPROC one) described in Part 1 of this LINQ to SQL series. I don't need to add any additional code or configuration in order to enable these query, insert, update, delete, and server-side paging scenarios.

Naming and Pluralization

One of the things you'll notice when using the LINQ to SQL designer is that it automatically "pluralizes" the various table and column names when it creates entity classes based on your database schema. For example: the "Products" table in our example above resulted in a "Product" class, and the "Categories" table resulted in a "Category" class. This class naming helps make your models consistent with the .NET naming conventions, and I usually find having the designer fix these up for me really convenient (especially when adding lots of tables to your model).

If you don't like the name of a class or property that the designer generates, though, you can always override it and change it to any name you want. You can do this either by editing the entity/property name in-line within the designer or by modifying it via the property grid:

The ability to have entity/property/association names be different from your database schema ends up being very useful in a number of cases. In particular:

1) When your backend database table/column schema names change. Because your entity models can have different names from the backend schema, you can decide to just update your mapping rules and not update your application or query code to use the new table/column name.

2) When you have database schema names that aren't very "clean". For example, rather than use "au_lname" and "au_fname" for the property names on an entity class, you can just name them to "LastName" and "FirstName" on your entity class and develop against that instead (without having to rename the column names in the database).

Relationship Associations

When you drag objects from the server explorer onto the LINQ to SQL designer, Visual Studio will inspect the primary key/foreign key relationships of the objects, and based on them automatically create default "relationship associations" between the different entity classes it creates. For example, when I added both the Products and Categories tables from Northwind onto my LINQ to SQL designer you can see that a one to many relationship between the two is inferred (this is denoted by the arrow in the designer):

The above association will cause cause the Product entity class to have a "Category" property that developers can use to access the Category entity for a given Product. It will also cause the Category class to have a "Products" collection that enables developers to retrieve all products within that Category.

If you don't like how the designer has modeled or named an association, you can always override it. Just click on the association arrow within the designer and access its properties via the property grid to rename, delete or modify it.

Delay/Lazy Loading

LINQ to SQL enables developers to specify whether the properties on entities should be prefetched or delay/lazy-loaded on first access. You can customize the default pre-fetch/delay-load rules for entity properties by selecting any entity property or association in the designer, and then within the property-grid set the "Delay Loaded" property to true or false.

For a simple example of when I'd want to-do this, consider the "Category" entity class we modeled above. The categories table inside "Northwind" has a "Picture" column which stores a (potentially large) binary image of each category, and I only want to retrieve the binary image from the database when I'm actually using it (and not when doing a simply query just to list the category names in a list).

I could configure the Picture property to be delay loaded by selecting it within the LINQ to SQL designer and by settings its Delay Loaded value in the property grid:

Note: In addition to configuring the default pre-fetch/delay load semantics on entities, you can also override them via code when you perform LINQ queries on the entity class (I'll show how to-do this in the next blog post in this series).

Using Stored Procedures

LINQ to SQL allows you to optionally model stored procedures as methods on your DataContext class. For example, assume we've defined the simple SPROC below to retrieve product information based on a categoryID:

I can use the server explorer within Visual Studio to drag/drop the SPROC onto the LINQ to SQL designer surface in order to add a strongly-typed method that will invoke the SPROC. If I drop the SPROC on top of the "Product" entity in the designer, the LINQ to SQL designer will declare the SPROC to return an IEnumerable<Product> result:

I can then use either LINQ Query Syntax (which will generate an adhoc SQL query) or alternatively invoke the SPROC method added above to retrieve product entities from the database:

Using SPROCs to Update/Delete/Insert Data

By default LINQ to SQL will automatically create the appropriate SQL expressions for you when you insert/update/delete entities. For example, if you wrote the LINQ to SQL code below to update some values on a "Product" entity instance:

By default LINQ to SQL would create and execute the appropriate "UPDATE" statement for you when you submitted the changes (I'll cover this more in a later blog post on updates).

You can also optionally define and use custom INSERT, UPDATE, DELETE sprocs instead. To configure these, just click on an entity class in the LINQ to SQL designer and within its property-grid click the "..." button on the Delete/Insert/Update values, and pick a particular SPROC you've defined instead:

What is nice about changing the above setting is that it is done purely at the mapping layer of LINQ to SQL - which means the update code I showed earlier continues to work with no modifications required. This avoids developers using a LINQ to SQL data model from having to change code even if they later decide to put in a custom SPROC optimization later.

Summary

LINQ to SQL provides a nice, clean way to model the data layer of your application. Once you've defined your data model you can easily and efficiently perform queries, inserts, updates and deletes against it.

Using the built-in LINQ to SQL designer within Visual Studio and Visual Web Developer Express you can create and manage your data models for LINQ to SQL extremely fast. The LINQ to SQL designer also provides a lot of flexibility that enables you to customize the default behavior and override/extend the system to meet your specific needs.

In upcoming posts I'll be using the data model we created above to drill into querying, inserts, updates and deletes further. In the update, insert and delete posts I'll also discuss how to add custom business/data validation logic to the entities we designed above to perform additional validation logic.

Mike Taulty also has a number of great LINQ to SQL videos that I recommend checking out here. These provide a great way to learn by watching someone walkthrough using LINQ to SQL in action.