[索引页]
挖.在上几周我写拉关于
LINQ 的标准操作符号的文章,接着有发一篇关于转化泛型参数类型的文章,转化泛型参数类型能帮助我们使用
LINQ to DataSet中扩展返回集合
的泛型参数类型.如果你还关注
LINQ to JavaScript.现在几来看LINQ to SQL,
分下面几篇文章:
(1)LINQ to SQL的对象模型和基于Attribute的映射;
(2)DataContext的讨论
(3)LINQ to SQL 插入,删除,更新,调试,并发控制
(4)高级LINQ to SQL的使用
(6)LINQ to SQL的性能关注
(7)LINQ三层应用程序.
LINQ to SQL的对象模型和基于Attribute的映射
一:LINQ to SQL的对象模型简单:
LINQ to SQL Object | Relational Object |
|
DataContext | Database |
|
Entity class
| Table |
|
Class member | Column |
|
Association | Foreign-key relationship |
|
二:的如何映射
(1)在LINQ to SQL中有三种映射方式:
- Object Relational Designer (ORD) tool
- SQLMetal command-line tool
- 手写
这里我们只关注手写:
按照上面对象模型的结构.我(在cases我们用的"AdventureWorks",我手写的感兴趣的可以下载,免高手,提供一个northwnd的
下载)
(2)映射数据库
在DatabaseAttribute 的attribute的Name属性指定数据库的名.
[Database(Name="Database#5")]
[Database(Name
=
"
AdventureWorks
"
)]
public
class
AWDB
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](/Images/OutliningIndicators/InBlock.gif)
}
(3)映射table
TableAttribute :表示这个实体类指示的是一个数据库的表.
[Table(Name = "Customers")]
[Table(Name
=
"
HumanResources.Employee
"
)]
public
class
Employee
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
//
}
[Table(Name
=
"
Person.Contact
"
)]
public
class
Contact
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
//
}
要注意最好对Name属性提供完整的引用[dbo].[Customers],还有如果你已经映射table给一个实体,就不能再将其他表映射给这个实体.
(4)Mapping Columns
ColumnAttribute
ColumnAttribute:表示属性或域指示的是数据库的column.
[Column(Name="CustomerID")]
对于ColumnAttribute给一张表:
Property
| Description | Default Value(if any) |
Name
| 表的Column名 |
|
DbType
| 数据Column的类型(并不是实体类型) |
|
Storage | 实体暂存域或变量(转成在SQL命令"field AS storage") |
|
IsPrimaryKey | 指定当前的column是不是相应表的主键
| false |
IsDbGenerated
| 指定当前的column是否自动产生值 | false |
CanBeNull | 指定当前column是否允许有null值 |
|
AutoSync
| 告诉运行环境在INSERT或更新操作执行后得到当前column值. 它有四个操作:Never—不为 INSERT或UPDATE操作返回值. OnUpdate:只返回更新操作的 column值. OnInsert:只返回查入操作的column值(这样可以提供性能) | Never |
Expression | 定义计算数据column的值. |
|
IsVersion
| 指定当前column的版本或timestamp |
|
UpdateCheck
| 在LINQ to SQL中对乐观并发操作控制.有三个值:Always,Nerver,WhenChanged. |
|
IsDiscriminator | 指定用一个值来鉴别column是否LINQ to SQL继承关系.
|
|
|
|
|
|
|
|
1:基本的表的Column的mapping
[Column(DBType
=
"
int
"
, IsPrimaryKey
=
true
, CanBeNull
=
false
)]
public
int
EmployeeID;
[Column(DBType
=
"
nvarchar(256)
"
, CanBeNull
=
false
)]
public
string
LoginID;
[Column(DBType
=
"
nvarchar(15)
"
, CanBeNull
=
false
)]
public
string
NationalIDNUmber;
[Column(DBType
=
"
int
"
,CanBeNull
=
false
)]
public
int
ManagerID;
2Expression的使用
[Column(Storage
=
"
_UnitPrice
"
, DbType
=
"
Money
"
,Expression
=
"
UnitPrice + 1.00
"
)]
public
System.Nullable
<
decimal
>
UnitPrice
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return this._UnitPrice;
}
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if ((this._UnitPrice != value))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.OnUnitPriceChanging(value);
this.SendPropertyChanging();
this._UnitPrice = value;
this.SendPropertyChanged("UnitPrice");
this.OnUnitPriceChanged();
}
}
}
3:CanBeNull的使用
上面是一种使用方式,还可以在实体上指定类型:
我们现在定义在Orders表中的EmployeeID包括Null值:
private Nullable<int> _EmployeeID;
[Column(Storage
=
"
_EmployeeID
"
, DbType
=
"
Int
"
)]
public
System.Nullable
<
int
>
EmployeeID
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
get
{
return this._EmployeeID;
}
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if ((this._EmployeeID != value))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._EmployeeID = value;
}
}
}
(4)Mapping RelationShips
数据库中表的关系我们指的是primary key/foreign key,这里也一样.这里主键的设定是通过IsPrimaryKey来设置的,而他们外键的关系就要使用AssociationAttribute来决定 .
1:AssociationAttribute
表示与数据库中的关系相结合的属性,如果外键.这里也是一张表:
Property
| Description |
|
Name | 结合的关系名 |
|
Storage
| 指定暂时存储的field/变量 |
|
IsUnique | 外健是否有唯一约束 |
|
ThisKey | 在实体类的成员上标识,表示本成员上结合键值. |
|
otherkey | 在多个目标实体上表识,表示与其他成员结合键值. |
|
[Table(Name
=
"
Person.Contact
"
)]
public
class
Contact
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
[Column(DBType = "nvarchar(8) not null")]
public string Title;
![](/Images/OutliningIndicators/InBlock.gif)
[Column(DBType = "nvarchar(50) not null")]
public string FirstName;
[Column(DBType = "nvarchar(50) not null")]
public string MiddleName;
[Column(DBType = "nvarchar(50) not null")]
public string LastName;
[Column(DBType = "nvarchar(50) not null")]
public string EmailAddress;
[Column(DBType = "int")]
public int EmailPromotion;
private EntityRef<Employee> _Employee;
[Association(Name = "FK_Employee_Contact_ContactID",
Storage = "_Employee", ThisKey = "ContactID", IsForeignKey = true)]
public Employee Emp
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
{
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
get
{ return this._Employee.Entity; }
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
set
{ this._Employee.Entity = value; }
}
}
这里还要谈到EntitySet与EntityRef类
2:EntitySet(TEntity)类
提供延迟加载并且为集合保持一对多关系和一对一关系.与EnityRef(TEntity)结合.EntitySet设置外健的实体,EntityRef是引用外键的实体.
在你的实体类中的任何关系你都能使用代码来编写,在Northwind数据库中,因为customers与orders他们之间就有关系.那你也能在你的模型中建立关系.
LINQ to SQL 定义一个AssociationAttribute属性去帮助表示关系.这个属性与EntitySet(TEntity)和EntityRef(TEntity)类型一起使用表示在数据库中外键关系.
通常都是一对多,但也有下面的
- 一对一:表示在两边EntitySet(TEntity)中的这种关系.
- 多对多:这个连结表的主键通常还有他两个表的外键混合而成在这表中.
现在就是Orders表和Order Details,Customer是一对一的关系,OrderID的外健是在Order Details中,而Orders中还建有CustomerID.看下面:
[Table(Name
=
"
Customers
"
)]
public
partial
class
customer
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
private string _CustomerID;
![](/Images/OutliningIndicators/InBlock.gif)
private EntitySet<order> _Orders;
![](/Images/OutliningIndicators/InBlock.gif)
public customer()
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Orders = new EntitySet<order>(new Action<order>(this.attach_Orders),
new Action<order>(this.detach_Orders));
}
![](/Images/OutliningIndicators/InBlock.gif)
[Column(Name = "CustomerID", DbType = "NChar(5) NOT NULl IDENTITY", IsPrimaryKey = true,
IsDbGenerated = true, CanBeNull = false)]
public string CustomerID
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return this._CustomerID;
}
![](/Images/OutliningIndicators/InBlock.gif)
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if ((this._CustomerID != value))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._CustomerID = value;
}
}
}
![](/Images/OutliningIndicators/InBlock.gif)
[Association(Name="FK_Orders_Customers",Storage="_Orders",OtherKey="CustomerID")]
public EntitySet<order> Orders
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return this._Orders;
}
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Orders.Assign(value);
}
}
![](/Images/OutliningIndicators/InBlock.gif)
private void attach_Orders(order entity)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
entity.Customer = this;
}
![](/Images/OutliningIndicators/InBlock.gif)
private void detach_Orders(order entity)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
entity.Customer = null;
}
}
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
[Table(Name
=
"
Orders
"
)]
public
partial
class
order
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
private int _OrderID;
![](/Images/OutliningIndicators/InBlock.gif)
private string _CustomerID;
![](/Images/OutliningIndicators/InBlock.gif)
private EntityRef<customer> _Customer;
![](/Images/OutliningIndicators/InBlock.gif)
private EntitySet<Order_Detail> _Order_Details;
public order()
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Customer = default(EntityRef<customer>);
this._Order_Details = new EntitySet<Order_Detail>(new Action<Order_Detail>
(this.attach_Order_Details),new Action<Order_Detail>(this.detach_Order_Details));
}
[Column(Name = "OrderID",AutoSync = AutoSync.OnInsert, IsPrimaryKey = true,
DbType = "int NOT Null IDENTITY", IsDbGenerated=true)]
public int OrderID
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return this._OrderID;
}
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if ((this._OrderID != value))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._OrderID = value;
}
}
}
![](/Images/OutliningIndicators/InBlock.gif)
[Column(Storage = "_CustomerID", DbType = "NChar(5)")]
public string CustomerID
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return this._CustomerID;
}
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if ((this._CustomerID != value))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (this._CustomerID != value)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._CustomerID = value;
}
}
}
}
![](/Images/OutliningIndicators/InBlock.gif)
[Association(Name = "FK_Orders_Customers", Storage = "_Customer", ThisKey = "CustomerID")]
public customer Customer
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
get
{ return this._Customer.Entity; }
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
set
{ this._Customer.Entity = value; }
}
![](/Images/OutliningIndicators/InBlock.gif)
[Association(Name = "Order_Order_Detail", Storage = "_Order_Details", OtherKey = "OrderID")]
public EntitySet<Order_Detail> Order_Details
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return this._Order_Details;
}
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Order_Details.Assign(value);
}
}
![](/Images/OutliningIndicators/InBlock.gif)
private void attach_Order_Details(Order_Detail entity)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
entity.Order = this;
}
![](/Images/OutliningIndicators/InBlock.gif)
private void detach_Order_Details(Order_Detail entity)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
entity.Order = null;
}
}
![](/Images/OutliningIndicators/None.gif)
[Table(Name
=
"
dbo.[Order Details]
"
)]
public
partial
class
Order_Detail
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](/Images/OutliningIndicators/InBlock.gif)
private int _OrderID;
![](/Images/OutliningIndicators/InBlock.gif)
private EntityRef<order> _Order;
![](/Images/OutliningIndicators/InBlock.gif)
public Order_Detail()
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Order = default(EntityRef<order>);
}
![](/Images/OutliningIndicators/InBlock.gif)
[Column(Storage = "_OrderID", DbType = "Int NOT NULL", IsPrimaryKey = true)]
public int OrderID
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return this._OrderID;
}
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if ((this._OrderID != value))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._OrderID = value;
}
}
}
![](/Images/OutliningIndicators/InBlock.gif)
[Association(Name = "Order_Order_Detail", Storage = "_Order", ThisKey = "OrderID",
IsForeignKey = true)]
public order Order
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return this._Order.Entity;
}
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this._Order.Entity = value;
}
}
}
其实很简单:
3还需要注意:
上面提到的Storage是在DataAttribute
DataAttribute.Storage Property
(5)映射存储过程
1:在LINQ to SQL中有StoredProcedure attribute,它将数据库中的存储过程存储在client对象.它只有一个单独的属性Name.
[StoredProcedure(Name
=
"
OrdersBySalesPersonID
"
)]
public
IEnumerable OrdersBySalesPersonID([Parameter(DBType
=
"
int
"
)] String param1)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
IExecuteResults results = this.ExecuteMethodCall<OrdersBySalesPersonID>
(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())),
param1);
}
使用存储过程返回记录:
[StoredProcedure(Name
=
"
dbo.uspGetRoleDescription
"
)]
public
IEnumerable uspGetRoleDescription([Parameter(Name
=
"
@description
"
)]
string
description)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
IQueryResults result =
this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), description);
return ((IEnumerable)(result));
}
使用存储过程管理记录:
[StoredProcedure(Name
=
"
dbo.uspUpdateRole
"
)]
public
int
uspUpdateRole
([Parameter(Name
=
"
@id
"
)] Nullable id, [Parameter(Name
=
"
@description
"
)]
string
description)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
IExecuteResults result =
this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), id, description);
return ((int)(result.ReturnValue));
}
还可以使用LINQ语句去代替SQL语句去调用存储过程
这样有个好处就是我们又可以使用Vs2008的特点拉和C#的特点.
2:还可以使用Parameter Attribute
有两个属性:
Property
| Descritption | DefaultValue(if any) |
Name
| Parameter名
| 在数据库中这个参数名的字符串 |
DbType | 在数据库中的类型 |
|
|
|
|
|
|
|
[StoredProcedure(Name
=
"
OrdersBySalesPersonID
"
)]
[
return
: Parameter(DbType
=
"
numeric
"
)]
public
IEnumerable MaxOrderBySalesPersonID([Parameter(DbType
=
"
int
"
)]
String param1)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
IExecuteResults results = this.ExecuteMethodCall<OrdersBySalesPersonID>
(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())),
param1);
}
(6)Mapping Functions
LINQ to SQL支持用户定义函数.使用方法就是attribute
[Function(Name="ufnGetcontactInformation")]
Public IQueryable<ufnGetcontactInformation>
ufnGetcontactInformation(System.Nullable<int> ContactID)
{
//
}
Property | Descritption
| DefaultValue(if any) |
IComposable
| 得到或设置是否一个方法被映射到一个函数或一个存储过程. |
|
Name | 函数名 |
|
TypeId
| 当实现一个派生类时,为这一个Attrbute的得到一个唯一标记. |
|
|
|
|
(7)inheritanceMappingAttribute
使用这个Attribute绘制整个继承层次关系.
欢迎评论
works guo
www.cnblogs.com/worksguo