Use the first part of LINQ to SQL -- scottgu's blog posts-using LINQ to SQL (Part 1)

This series is from scottgu's blog-to-SQL series. At the invitation of this blog, this article translates scottgu's LINQ to SQL series part (1-N)
Sharing learning materials is a pleasure!

This article is also published on scottgu's Chinese Blog
Http://blog.joycode.com/scottgu/archive/2007/11/17/111496.aspx

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 and. net Framework "orcas" release. here are pointers to the posts in my series:

In the past few months, I have written a series of posts covering some new features in Vs and. NET Framework "orcas" release. Below are links to these posts:

• Automatic properties, object initializer and collection initializers
• Extension methods
• Lambda expressions
• Query syntax
• Anonymous types

 

The above language features help makeQuerying dataA first class programming concept. We call this overall querying Programming Model "LINQ"-which stands. Net Language Integrated Query.

The above languages help you understand data query as a kind of programming philosophy. We call this general query Programming Model "LINQ" -- it refers to the. NET language set 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 versions can provide full type-safety and compile-time checking of Query expressions, and development tools can provide full intelliisense, debugging, and rich refactoring support when writing LINQ code.

Developers can use LINQ on any data source. They can express efficient query behavior in programming languages, and choose to convert the query results or form any form of result set they want to convert to, and then operate the result set very conveniently. Languages with the LINQ function can fully check the type security and query expression Compilation Time, And the development tools provide completely intelligent awareness, debugging, and refactoring support when writing the LINQ code.

LINQ supports a very rich extensibility model that facilitates the creation of very efficient domain-specific operators for data sources. the "orcas" version of. net Framework ships with built-in libraries that enable LINQ support against objects, XML, and databases.

LINQ supports a very wide range of extension models: This model generates different efficient operating factors for different data sources .. The Net Framework "orcas" version is embedded with dictionaries supported by the LINQ language for objects, XML, and databases.

What is LINQ to SQL?

What is LINQ to SQL?

LINQ to SQL is an O/RM (object relational mapping) implementation that ships in. net Framework "orcas" 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.

In. net Framework "orcas" release, which allows you to use. net class to generate a relational database. Then you can use LINQ to query, update, insert, and delete databases from this object.

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.

LINQ to SQL fully supports transactions, views, and stored procedures. It also provides a convenient way to verify the set data and business logic rules in your data model.

Modeling databases using LINQ to SQL:

Create a database by using the following code:

Visual Studio "orcas" 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 ).

VS "orcas" has a built-in designer that provides a simple way to visually convert a database to a LINQ to SQL relational model. In my next blog, I will go into more details about how to use the designer (you can refer to the watch this video I recorded in February about how to use it)

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

Through the LINQ to SQL designer, I can easily design the "northwind" database model as an example:

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.

Four entity classes are defined: product, category, order and orderdetail. The attributes of each class are mapped to the corresponding tables in the database. An instance of each class represents a row of records in the data table.

The arrows between the four entity classes abve 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-specific relationship. stronugly-typed properties will be added to the entity classes based on this. for example, the category class above has a one-to-Define 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.

In, the arrows in the four object classes represent the relationships between objects. They are mainly generated based on the primary key/foreign key relationship in the database. The arrow points on the designer indicate whether the relationship is one-to-one or one-to-many. Based on this, strong-type attributes will be added to this entity class. For example, there is a one-to-multiple relationship between the category class and the product class above. This means that I can have a "categories" attribute, which represents all product objects in this class. The product class will have a "category" attribute to point to an instance of the category class. The instance of this category class indicates the category of the product.

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.

The right side of the LINQ to SQL designer is the stored procedure that interacts with our database model. In the above example, I added a "getproductsbycategory" stored procedure. It has a categoryid as the input parameter and returns a product sequence. The following sample code shows how to call the stored procedure.

Understanding the datacontext class

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 conducting it 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.

When you click the "save" button on the LINQ to SQL designer, Vs will save classes that represent the relationship between our entities and databases. A custom datacontext class is also generated for every file added to the solution by the LINQ to SQL designer.

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

For example, the northwinddatacontext class is generated based on the model we designed above:

LINQ to SQL code example

Examples of code written using LINQ to SQL

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:

After using the LINQ to SQL designer to generate our database model, we can easily write code to operate on the database. Below are some codes that demonstrate some common database operations:

1) query products from the database

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 processing ss the product/Category relationship to only retrieve those products in the "beverages" category:

The following code uses the LINQ to SQL query syntax to query the product object sequence. Note that the Code uses the product/Category relationship to identify only products with the "beverages" category:
C #:

VB:

2) update a product in the database

2) update a product record 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:
The following code shows how to query a single product record from the database, update its price, and save the change to the database:

C #:

VB:

Note: VB in "orcas" beta1 doesn't support Lambdas yet. It will, though, in beta2-at which point the above query can be rewritten to be more concise.
Note: VB does not support Lambdas in "orcas" beta1. But it will be supported in beta2-then the code will be more concise.

3) Insert a new category and two new products into the database

3) Insert a new category and two new products to the database.

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

The following code generates a new category, generates two products associated with the category, and saves the three records to 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.

Note that I do not need to manually maintain the primary/foreign key relationship. Instead, I only add two product records to the "Products" set of categorys, then, by adding this category object to the "categories" set of datacontext, the relation of PK/FK will be automatically added to me by LINQ to SQL.

C #

VB:

4) Delete products from the database

4) Delete products from the database

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

The following code deletes all toys from the database:

C #:

VB:

5) call a stored procedure
5) Call the 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.

The following code shows how to query the product entity without using the query syntax of LINQ. Instead, it calls the "getproductsbycategory" stored procedure we added to the data model. Note: Once I query the product result set, I can update/delete them, and then call dB. submitchanges () to submit these updates to the database.

C #:

VB:

6) retrieve products with server side Paging

6) query products on the server side by PAGE

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.
The following code demonstrates how to implement efficient server paging as part of the LINQ language. By using the following SKIP () and take () operators, we can query only 10 records starting from row 200 in the database:

C #:

VB:

Summary
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.

LINQ to SQL provides an excellent and clear method to build a data layer for your application. Once you define a data model, you can query, insert, update, and delete it conveniently and effectively.

Hopefully the above introduction and code samples have helped whet your appetite to learn more. Over the next few weeks I'll be continuing this series to explore LINQ to SQL in more detail.

We hope that the above introduction and case code will stimulate your appetite and help you learn more. In the next few weeks, I will explore more specifically in this series of LINQ to SQL.
Hope this helps,

Scott