Introducing LINQ to SQL ASP.NET

LINQ,or Language integrated query,is a set of C# 3.0 language and .NET 3.5 framework features for writing structured queries over local object collections and remote data sources. With LINQ, you can query any collection that implements IEnumerable<>, including tables in a database.

LINQ to SQL is a lot of fun to work with and makes the task of writing a data access layer that generates highly optimized SQL amazingly simple. If you haven’t used LINQ to SQL before, brace yourself.

With LINQ to SQL, you design the database that the application will use and then use the Visual Studio 2008 Object/Relational Designer (sqlmetal.exe in LINQ Preview) to generate a class that represents the database with an appropriate object model.This is a giant step beyond having to handcode the entity and data access classes. Formerly, whenever the database design changed, you had to modify the entity classes and modify the insert, update, delete, and get methods in the data access layer.Of course, you could use third-party object-relational mapping (ORM) tools or some kind of code generator that generates entity classes from database schema and data access layer code. But now, LINQ to SQL does it all for you!

A great thing about LINQ to SQL is that it can generate objects known as projections that contain only the fields you want to receive from a specific query, not the entire row.There’s no ORM tool or object-oriented database library that can do this today because the operation requires a custom compiler to support it. The benefitof projection is pure performance. You select only fields that you need, and you don’t have to build a jumbo-sized object with every field from the tables you query.LINQ to SQL selects only the required fields and creates objects that contain only the selected fields.Let’s take a look at some example queries used in the business layer. Example shows how easy it is to create a new Page object in a database.

Inserting a new Page object in a database using LINQ to SQL

Here DashboardData is the DataContext generated by the Visual Studio 2008 Object Relational Designer.It contains all the database access methods and entities for tables in the database. DataContext takes care of generating queries for objects that are requested from the database, executing the queries, and populating objects from the database.It also keeps track of changes made to the objects and when they are updated, and knows exactly which fields to update in the tables.DataContext completely encapsulates database access and provides a nice, clean, object-oriented way of working with data that is persisted in a database. Moreover, DataContext allows you to run arbitrary queries as well; you can use regular stored procedures to read and write rows in database tables.

Example shows how to get a Page and change its name. You can use lambda expressions similar to those you have seen in Chapter 3 to define the condition for the where clause.

Get an object by primary key and updating

Another option is to select only a scalar value from the database. Reading scalar values directly from a database is faster than reading a row and then converting it to an object repeatedly. Example shows how to do it.

Read scalar values

You can also read specific fields and create an object on the fly that contains only the specific fields. This is called projection and is shown in Example

Create projection

In Example, only two fields from the Aspnet_Users table are selected. LINQ to SQL returns an object that has only these two fields, and not all the fields in the table.

Database paging is very easy in LINQ to SQL.For example, if you want to select 20 rows starting at the 100th row, just use the Skip and Take functions as shown in Example

Paging using Skip and Take

It’s easy to provide transaction support in LINQ to SQL. You just write code inside a using block, and the code inside it falls into a transaction scope (see Example).

Using transaction

When there’s any exception, the using block will call the Dispose function on ts, and the transaction will abort unless it is already completed. But if the code reaches the end of the block, it calls ts.complete( ) and the transaction commits.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

ASP.NET Topics