Link to SQL Simple

LINQ and its extensions

LINQ is a data Query Language ( It can query data from a variety of data sources ). Now the LINQ-based extensions are:

1. Linq to object: Primarily querying data from memory objects
2. LINQ to SQL: mainly manipulate (query \ add \ modify \ Delete) data from MS SQL Server database
3. Linq to XML: querying data primarily from XML fragments or XML documents

about Linq to SQL

linq to sql is a Microsoft in .net framework 3.5 ORM (Object  relation  model) Entity Framework. Span style= "font-family: Microsoft Jacob Black;" > A good Entity Framework, in addition to being able to map data tables to entity classes orm :linq to sql\entity framework\n Hibernate et

In the process of using Linq to SQL , we seldom go to handwritten SQL statements to achieve common data deletion and change operations . but its bottom-up implementation is still So how do you detect the SQL statements generated by LINQ to SQL? The DataContext Log property enables detection.

Linq to Sql designer

In Server Explorer, drag-and-drop the data table toLinq to Sqlafter the designer regionVswill beAutomatically generate these data tablesn+1 (Nis the number of data tables)a class.These classes can be broadly divided into2species:One is the database context,the other is the entity class.The database context class isName to DataContextEndThe class,it inherits fromSytem.Data.Linq.DataContext.

Their roles are: The DataContext class is used to interact with the database (add and revise), each time the database operation , should first create the class object ;

Each entity class corresponds to a data table.

the DataContext attribute is marked as database , which indicates

An attribute tag for an entity class is a table that is associated with a data table

attribute tags for attributes in an entity class are column and are related to data columns

Common Properties and methods of DataContext database context classes

CreateDatabase (): CREATE DATABASE

DeleteDatabase (): Delete Database

Databaseexits (): determine if the database exists

Log: This property is used to detect and write SQL statements generated by LINQ to SQL

Linq to SQL common operations

New data

void InsertOnSubmit (): implement 1 new records per time

void Insertallonsubmit (): implement multiple new records at once

Note : after calling insertonsubmit , Be sure to call Datacontext.submitchanges ();

modifying data

First , query to the element you want to modify

Second , change the corresponding attributes of the element

Finally , call datacontext.submitchanges ()

Delete data

First , query to the element you want to delete

Second , call void DeleteOnSubmit () to delete a single element , if you want to delete more than one can invoke void Delteallonsubmit ()

Finally , call datacontext.submitchanges ();

Data query

When querying data , there are 2 Common ways to use it . one is to use LINQ the standard syntax ; one is to use Lambda Expression extension Methods .

Single-table query :

Multi-table Query :

Typical application Scenarios

Scenario 1: implemented in one page : Add a payroll template , and multiple payroll entries for the Payroll template

Procedure One :

Procedure two :

Both of these practices can be implemented . The first step is to realize the new payroll template , and then add the payroll entry . The second approach is to add multiple payroll entries to the Payroll Template Object , then submit a payroll template to the database .

It is important to note that, in practice one, the Payroll template ID is set for the Payroll entry Object , and practice two does not set a payroll template ID for the payroll entry . But why is the final effect the same? ?

Because when we add a payroll entry to the Payroll Template Object , LINQ to SQL automatically assigns a value to the field of the primary foreign key relationship .

Scenario Two : paging using Linq to SQL

The paging feature needs to be combined using two functions : Skip () and take ()

Skip (int count): skips count Bar Data

Take (int count): Extract Count Records from the start location

Paged Query : DataContext.XXX.Skip (PageIndex * pageSize). Take (pageSize)

PageIndex is the first page record to extract . PageIndex >= 0

PageSize is the number of records displayed per page

Other

• Entityset<t>: The class represents a collection of data entities
• System.data.linq.table<t>: This class represents the data table object
• How to determine the linq to sql make data changes \ delete \

because insertonsubmit method and datacontext.submitchanges () : Throw an exception is definitely a failure

• For identity columns ( Auto-generated columns ) , the call to DataContext.XXX.InsertOnSubmit () After the value of the identity column cannot be obtained ; only when calling datacontext.submitchanges () to get the value of the identity column .

Link to SQL Simple