Scottgu's blog -- LINQ to SQL Part 2-defining data model classes -- LINQ to SQL (Part 2-defining our Data Model C

A more concise version will be published in the blog Hall (excluding the English version). I hope this article will help you!

Allowed to reprint, trouble to indicate the source: http://www.cnblogs.com/hanxianlong
Original article:
Http://weblogs.asp.net/scottgu/archive/2007/05/29/linq-to-sql-part-2-defining-our-data-model-classes.aspx

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 Part 1, I explained "What Is LINQ to SQL" and provided some data environments that can be supported by LINQ to SQL.

In my first post I provided code samples that demonstrated how to perform common data scenarios using LINQ to SQL including:
In my first post, I provided a presentation on how to use LINQ to SQL for some common data processing, including the following:

• How to query a database
• How to query a database
• How to update rows in a database
• How to update rows in a database
• How to insert and relate multiple rows in a database
• How to insert and operate associated operation data rows in the database
• How to delete rows in a database
• How to delete records in a database
• How to call a stored procedure
• How to call a stored procedure
• How to retrieve data with server-side Paging
• How to query data by page on the server

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

All these data operations are performed in the following LINQ to SQL class model:

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.

In the second post of this series, I will explain in detail how to generate 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. net 3.5 and Visual Studio "orcas" release.

The LINQ to SQL, LINQ to SQL designer, and all the features I mentioned in this blog series are part of. Net 3.5 and VS "orcas.

You can follow all of the steps below by downloading either Visual Studio "orcas" Beta 1 or visual web developer Express "orcas" beta1. both can be installed and used side-by-side with 2005.

You can download the following files from Visual Studio "orcas" Beta 1 or visual web developer Express "orcas" beta1. Both of them can be installed with vs2005.

Create a new LINQ to SQL data model

Create a new ilnq 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:

You can.. Net, a class library or a Windows client project to add a data model using the following method: click the "Add new item" option and select the "LINQ to SQL" item in the pop-up dialog box:

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 conducting it by which we'll query entities from the database as well as apply changes back to it.

When you select the "LINQ to SQL" item, the "LINQ to SQL" designer will pop up. Then, you can create a model class that represents the relational database. It also generates a strongly-typed "datacontext" class, which represents the attributes of each data table in our database from the database, represents the Stored Procedure Method in our database. As described in part 1 of this series, the datacontext class is a main pipeline for us to query from the database and store updates back to the database.

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:

The following is the screen of An ORM designer with an empty LINQ to SQL, which you will first see after a new LINQ to SQL data model is generated:

 

Entity classes

Entity class

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.

Using LINQ to SQL, you can create a class that maps to or maps to a database. These classes are called "entity classes" and their instances are "entities ". The object class maps data tables in the database. Ing object attributes to table columns. Each entity represents a row of records in the data table.

Entity classes defined with LINQ to SQLDo notHave 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.

Object classes defined by using LINQ to SQL do not have to inherit from a specific base class, which means you can make it inherit from any object. All the classes generated using the LINQ to SQL designer are defined as "Local classes", which means you can choose to add code to the local class and add additional attributes, methods, and events.

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.

Unlike dataset/tableadapter provided in vs2005, you do not need to specify an SQL query statement when generating a data model or access layer when using the LINQ to SQL designer.

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 strongrong typed way.

Instead, you only need to focus on your entity classes and how they map to/from databases and their relationships. If you are using a data entity, you can use the data entity to generate appropriate SQL statement execution logic. You can use a strongly typed method to express how to query data models through the LINQ query syntax.

Creating entity classes from a database

Generate entity classes from the 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.

If you already have a predefined database model, you can use it to quickly generate a LINQ to SQL entity class out 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:

The simplest way to achieve this is to open a database in the Server Explorer window of Vs and select the data table and view for which you want to generate the model, then, you can drag and drop them to the LINQ to SQL designer:

 

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:

When you add two above classes to the panel of the LINQ to designer from the "northwind" database, you will see the following three entity classes based on your data structure:

 

 

Using the data model classes defined above, I can now run all of the code samples (Please CT 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.

With the data model defined above, I can now execute all the case Code mentioned in part 1 of this series (except the stored procedure ). I can directly run the query, insert, update, delete, and server-side paging code without adding any additional code or settings.

Naming and pluralization

Name and plural

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 abve resulted in a "product" class, and the "categories" Table resulted in a "category" class. this class naming helps make your models consistent with. 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 ).

One thing you may have noticed is that when you use the LINQ to SQL designer to generate an entity Class Based on your database structure, it will automatically change the table and column name to "plural ". For example, in the case of "Products", a "product" class is generated, and the "categories" Table generates a "category" class. These class naming methods make your model and.. Net naming is consistent, and I often find it easy for the designer to name me (especially when many tables are added to the 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:

If you do not like the name or attribute name of the class generated by the designer, you can also rewrite it to rename the name you want. You can rename it as follows: or modify it within the name already declared in the designer or through the attribute column:

 

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 particle:

This method of naming entities, properties, and relationships in your database structure into different names is very useful in many cases, especially:

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.

1) when the table/column name in the original database changes. Because your object model uses different backend names, you can use a new table/column name to update only your ing rules without updating your application or query code.

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

2) When your database model name is not very "clear. For example, during development, you can name "lastname" and "firstname ", you do not need to name the attributes of an object class as "au_lnname" or "au_fname" (you do not need to rename the column names in the database ).

Relationship associations
Relationship Organization

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 your relationship between the two is inferred (this is denoted by the arrow in designer):

When you drag an object from Server Explorer to the LINQ to SQL designer, Vs will detect the primary/Foreign keys of the object, they also generate "relational organization" in different entity classes it generates ". For example, when I add two tables "product" and "categories" from the northwind database to the LINQ to SQL designer, you will see that a one-to-many relationship will be generated after the two (expressed by arrows in the designer)

The above association will 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.

The above relationship will enable the product entity class to have a "category" attribute. developers can use this attribute to obtain all the product sets of this 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.

If you do not like the designer to generate or name a link for you, you can also rewrite it. You only need to click the link arrow in the designer to rename, delete, or modify it by finding its properties.

Delay/lazy loading
Latency/inertia loading

LINQ to SQL enables developers to specify whether the properties on entities shocould 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.

With the ability to use LINQ to SQL, developers can specify whether to load data at the first call or by delay or laziness. You can select an object attribute or method in the designer to specify whether the object is preloaded or delayed, and then set the "delay loaded" attribute to true or false in the attribute window.

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

For a simple example, when I want to do this, let's take a look at the "category" entity class we just generated. The categories table in northwind has a "picture" field, this field stores an image of each category (very large ), and I only want to retrieve the binary image field from the database when I use it (not when I make a simple query to list all lists)

I cocould 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:

I can select the picture attribute in the LINQ to SQL designer and set its delay loaded value to true.

Note: In addition to inserting 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 ).

Note: In addition to setting the default pre-Fetch/delay loading on the object, you can also control it by code when writing a LINQ query to the object class.

Using Stored Procedures

Use 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:

LINQ to SQL allows you to use stored procedures as a method of your datacontext class. For example, if we have defined a stored procedure as follows, the stored procedure uses categoryid for product information:

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 stronugly-typed method that will invoke the sproc. if I drop the sproc on top of the "product" entity in the designer, the LINQ
SQL designer will declare the sproc to return an ienumerable <product> result:

To add a strongly typed method to execute the stored procedure, I can use Server Explorer in vs to drag/drop the stored procedure to the LINQ to SQL designer. If I place the stored procedure on "product" in the designer, the LINQ to SQL designer declares a method to return the ienumberable <product> result set.

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

Then, I can use the query syntax of LINQ (which generates an adhoc SQL query) or execute the Stored Procedure method added above to obtain the product entity from the database:

Using sprocs to update/delete/insert data

Use stored procedures 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, When you insert, update, or delete an object, LINQ to SQL automatically generates an appropriate SQL statement for you. For example, when you write the following LINQ to SQL code to update some values of the "product" Object object:

By default LINQ to SQL wocould 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 ).

By default, when you submit an update statement, the appropriate "Update" Statement statement declaration will be generated and executed by LINQ to SQL. (I will have several posts to explain "Update ).

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 "... "button on the delete/insert/update values, and pick a special sproc you 've defined instead:
You also choose to define your own insert, update, and delete stored procedures. To set a custom stored procedure, click the object class of the LINQ to SQL designer, and click "..." after delete/insert/update in the Properties window. "button, select the stored procedure you have defined to replace the default one:

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.

The advantage of changing the preceding settings is that the LINQ ing layer of the LINQ to SQL statements is completely completed, this means that the updated code I previously showed will continue to work normally without changing it. This avoids the need for developers to modify code to optimize programs when using the LINQ to SQL model, even if they want to add a custom stored procedure.

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 way to create a data layer for your application. Once you define a data model, you can query, insert, update, and delete it effectively.

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.

Through the built-in LINQ to SQL designer of Vs and Visual Web Developer Express, you can quickly generate data models, especially the LINQ to SQL model. The LINQ to SQL designer also provides many default behaviors that you can customize for flexible scaling to meet your special needs.

In upcoming posts I'll be using the data model we created abve 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 abve to perform additional validation logic.

In the following post, I will use the data model generated above for further drill query, insertion, update, and deletion. In the update, insert, and delete posts, I will discuss again how to add custom business/data verification logic to our designed entities.

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.

Mike taulty also has a lot of good videos on LINQ to SQL, which I have already recommended here. This will provide a very convenient way for beginners.

Hope this helps,

Scott