本系列,或多或少,直接或间接依赖入门系列知识。但,依然追求独立成章。因本文作者水平有限,文中错误难免,敬请读者指出并谅解。本系列将会和入门并存。案例 某君被邀为一超市设计数据库,用来存储数据。该君根据该超市中实际出现的对象,设计了Customer, Employee,Order, Product等表,用来保存相应的客户,员工,订单,货品等。太好了,该君很有oo的思想吗。 如果,你被要求用类及对象,来描述该关系型数据,你该如何做呢?在linq推出之前,ADO.NET被用来做数据访问层。而后,程序员需要自己去编写事务逻辑层中所出现的类。比如,Customer, Employee,Order, Product等。然后,程序员组装所需的sql语句,通过ADO.NET,将返回的记录,来初始化Customer等类的对象。在这里,你已经自己动手将Customer表和Customer类关联了起来。从Linq To Sql的设计来看,它主要是为了解决data!=objects 的问题而产生的。现在,有了Table和Class之间的映射,数据和对象之间就可以有一个一一对应的关系了。 在Linq To Sql之前,在java领域有Hibernate,在net领域有NHibernate技术,来实现object/relational 持久和查询服务。无论是Hibernate还是NHibernate,其配置复杂,上手时间长,已经不能适应快速开发的需要。而Linq To Sql的推出,恰恰弥补了它们的缺点,彻底降低了程序开发门槛。Linq Linq是Language Integrated Query的缩写。Linq To Sql是linq中的一部分,其与ADO.NET Orcas的关系入下。 现在linq分三个部分。Linq To Objects,即以前的linq。其主要是针对CLR-Based Objects的查询。即内存操作。Linq Enabled ADO.NET是针对关系型数据的。这又包含三个部分。Linq To Datasets, Linq To Sql, Linq To Entities. 其中Linq To Sql 是大家所熟悉的部分,即以前的Dlinq. 官方的解释是,Linq To Datasets, support for ADO.NET Datasets; Linq to SQL, support for SQL Server; Linq to Entities, Support fro Entity Data Model. 记得,以前曾有人问及dlinq与ADO.NET Orcas的关系,因为它们隶属于不同的队伍开发,其中有重叠的部分。而现在,其功能归结在一起,其重叠部分已经得到融合。(如果有人是ADO.NET team或熟悉这个的,开辟专栏给我们大家讲一下呀。)最后一部分叫Linq To XML,即以前的Xlinq. 针对xml格式数据的操作。(还有针对ASP.NET的Blinq, 大声问一下,有没有人懂这个呀)DBML 所谓dbml,即Database Mark Language。数据库描述语言,是一种xml格式的文档,用来描述数据库。上面我们讲了,不是用类来描述数据吗?为什么又有个dbml?是的,dbml只是个中间的产物,其出现的主要原因是,适应c# 和vb.net语言的不同,做中间缓冲。dbml及数据库和code关系如下。 Database ----> DBML ------------> Code. 使用sqlmetal可以产生dbml。键入如下命令: sqlmetal /server:yourserver /database:northwind /dbml:YourDbml.dbml 最终可以得到dbml文件,如下:
<? xml version="1.0" encoding="utf-16" ?> < Database Name ="northwind" Class ="Northwind" xmlns ="http://schemas.microsoft.com/linqtosql/dbml/2007" > < Table Name ="dbo.Categories" Member ="Categories" > < Type Name ="Categories" > < Column Name ="CategoryID" Type ="System.Int32" DbType ="Int NOT NULL IDENTITY" IsPrimaryKey ="true" IsDbGenerated ="true" CanBeNull ="false" /> < Column Name ="CategoryName" Type ="System.String" DbType ="NVarChar(15) NOT NULL" CanBeNull ="false" /> < Column Name ="Description" Type ="System.String" DbType ="NText" CanBeNull ="true" UpdateCheck ="Never" /> < Column Name ="Picture" Type ="System.Data.Linq.Binary" DbType ="Image" CanBeNull ="true" UpdateCheck ="Never" /> < Association Name ="FK_Products_Categories" Member ="Products" OtherKey ="CategoryID" Type ="Products" DeleteRule ="NO ACTION" /> </ Type > </ Table > < Table Name ="dbo.CustomerCustomerDemo" Member ="CustomerCustomerDemo" > < Type Name ="CustomerCustomerDemo" > < Column Name ="CustomerID" Type ="System.String" DbType ="NChar(5) NOT NULL" IsPrimaryKey ="true" CanBeNull ="false" /> < Column Name ="CustomerTypeID" Type ="System.String" DbType ="NChar(10) NOT NULL" IsPrimaryKey ="true" CanBeNull ="false" /> < Association Name ="FK_CustomerCustomerDemo" Member ="CustomerDemographics" ThisKey ="CustomerTypeID" Type ="CustomerDemographics" IsForeignKey ="true" /> < Association Name ="FK_CustomerCustomerDemo_Customers" Member ="Customers" ThisKey ="CustomerID" Type ="Customers" IsForeignKey ="true" /> </ Type > </ Table > < Table Name ="dbo.CustomerDemographics" Member ="CustomerDemographics" > < Type Name ="CustomerDemographics" > < Column Name ="CustomerTypeID" Type ="System.String" DbType ="NChar(10) NOT NULL" IsPrimaryKey ="true" CanBeNull ="false" /> < Column Name ="CustomerDesc" Type ="System.String" DbType ="NText" CanBeNull ="true" UpdateCheck ="Never" /> < Association Name ="FK_CustomerCustomerDemo" Member ="CustomerCustomerDemo" OtherKey ="CustomerTypeID" Type ="CustomerCustomerDemo" DeleteRule ="NO ACTION" /> </ Type > </ Table > < Table Name ="dbo.Customers" Member ="Customers" > < Type Name ="Customers" > < Column Name ="CustomerID" Type ="System.String" DbType ="NChar(5) NOT NULL" IsPrimaryKey ="true" CanBeNull ="false" /> < Column Name ="CompanyName" Type ="System.String" DbType ="NVarChar(40) NOT NULL" CanBeNull ="false" /> < Column Name ="ContactName" Type ="System.String" DbType ="NVarChar(30)" CanBeNull ="true" /> < Column Name ="ContactTitle" Type ="System.String" DbType ="NVarChar(30)" CanBeNull ="true" /> < Column Name ="Address" Type ="System.String" DbType ="NVarChar(60)" CanBeNull ="true" /> < Column Name ="City" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" /> < Column Name ="Region" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" /> < Column Name ="PostalCode" Type ="System.String" DbType ="NVarChar(10)" CanBeNull ="true" /> < Column Name ="Country" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" /> < Column Name ="Phone" Type ="System.String" DbType ="NVarChar(24)" CanBeNull ="true" /> < Column Name ="Fax" Type ="System.String" DbType ="NVarChar(24)" CanBeNull ="true" /> < Association Name ="FK_CustomerCustomerDemo_Customers" Member ="CustomerCustomerDemo" OtherKey ="CustomerID" Type ="CustomerCustomerDemo" DeleteRule ="NO ACTION" /> < Association Name ="FK_Orders_Customers" Member ="Orders" OtherKey ="CustomerID" Type ="Orders" DeleteRule ="NO ACTION" /> </ Type > </ Table > < Table Name ="dbo.Employees" Member ="Employees" > < Type Name ="Employees" > < Column Name ="EmployeeID" Type ="System.Int32" DbType ="Int NOT NULL IDENTITY" IsPrimaryKey ="true" IsDbGenerated ="true" CanBeNull ="false" /> < Column Name ="LastName" Type ="System.String" DbType ="NVarChar(20) NOT NULL" CanBeNull ="false" /> < Column Name ="FirstName" Type ="System.String" DbType ="NVarChar(10) NOT NULL" CanBeNull ="false" /> < Column Name ="Title" Type ="System.String" DbType ="NVarChar(30)" CanBeNull ="true" /> < Column Name ="TitleOfCourtesy" Type ="System.String" DbType ="NVarChar(25)" CanBeNull ="true" /> < Column Name ="BirthDate" Type ="System.DateTime" DbType ="DateTime" CanBeNull ="true" /> < Column Name ="HireDate" Type ="System.DateTime" DbType ="DateTime" CanBeNull ="true" /> < Column Name ="Address" Type ="System.String" DbType ="NVarChar(60)" CanBeNull ="true" /> < Column Name ="City" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" /> < Column Name ="Region" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" /> < Column Name ="PostalCode" Type ="System.String" DbType ="NVarChar(10)" CanBeNull ="true" /> < Column Name ="Country" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" /> < Column Name ="HomePhone" Type ="System.String" DbType ="NVarChar(24)" CanBeNull ="true" /> < Column Name ="Extension" Type ="System.String" DbType ="NVarChar(4)" CanBeNull ="true" /> < Column Name ="Photo" Type ="System.Data.Linq.Binary" DbType ="Image" CanBeNull ="true" UpdateCheck ="Never" /> < Column Name ="Notes" Type ="System.String" DbType ="NText" CanBeNull ="true" UpdateCheck ="Never" /> < Column Name ="ReportsTo" Type ="System.Int32" DbType ="Int" CanBeNull ="true" /> < Column Name ="PhotoPath" Type ="System.String" DbType ="NVarChar(255)" CanBeNull ="true" /> < Association Name ="FK_Employees_Employees" Member ="ReportsToEmployees" ThisKey ="ReportsTo" Type ="Employees" IsForeignKey ="true" /> < Association Name ="FK_Employees_Employees" Member ="Employee" OtherKey ="ReportsTo" Type ="Employees" DeleteRule ="NO ACTION" /> < Association Name ="FK_EmployeeTerritories_Employees" Member ="EmployeeTerritories" OtherKey ="EmployeeID" Type ="EmployeeTerritories" DeleteRule ="NO ACTION" /> < Association Name ="FK_Orders_Employees" Member ="Orders" OtherKey ="EmployeeID" Type ="Orders" DeleteRule ="NO ACTION" /> </ Type > </ Table > < Table Name ="dbo.EmployeeTerritories" Member ="EmployeeTerritories" > < Type Name ="EmployeeTerritories" > < Column Name ="EmployeeID" Type ="System.Int32" DbType ="Int NOT NULL" IsPrimaryKey ="true" CanBeNull ="false" /> < Column Name ="TerritoryID" Type ="System.String" DbType ="NVarChar(20) NOT NULL" IsPrimaryKey ="true" CanBeNull ="false" /> < Association Name ="FK_EmployeeTerritories_Employees" Member ="Employees" ThisKey ="EmployeeID" Type ="Employees" IsForeignKey ="true" /> < Association Name ="FK_EmployeeTerritories_Territories" Member ="Territories" ThisKey ="TerritoryID" Type ="Territories" IsForeignKey ="true" /> </ Type > </ Table > < Table Name ="dbo.Order Details" Member ="OrderDetails" > < Type Name ="OrderDetails" > < Column Name ="OrderID" Type ="System.Int32" DbType ="Int NOT NULL" IsPrimaryKey ="true" CanBeNull ="false" /> < Column Name ="ProductID" Type ="System.Int32" DbType ="Int NOT NULL" IsPrimaryKey ="true" CanBeNull ="false" /> < Column Name ="UnitPrice" Type ="System.Decimal" DbType ="Money NOT NULL" CanBeNull ="false" /> < Column Name ="Quantity" Type ="System.Int16" DbType ="SmallInt NOT NULL" CanBeNull ="false" /> < Column Name ="Discount" Type ="System.Single" DbType ="Real NOT NULL" CanBeNull ="false" /> < Association Name ="FK_Order_Details_Orders" Member ="Orders" ThisKey ="OrderID" Type ="Orders" IsForeignKey ="true" /> < Association Name ="FK_Order_Details_Products" Member ="Products" ThisKey ="ProductID" Type ="Products" IsForeignKey ="true" /> </ Type > </ Table > < Table Name ="dbo.Orders" Member ="Orders" > < Type Name ="Orders" > < Column Name ="OrderID" Type ="System.Int32" DbType ="Int NOT NULL IDENTITY" IsPrimaryKey ="true" IsDbGenerated ="true" CanBeNull ="false" /> < Column Name ="CustomerID" Type ="System.String" DbType ="NChar(5)" CanBeNull ="true" /> < Column Name ="EmployeeID" Type ="System.Int32" DbType ="Int" CanBeNull ="true" /> < Column Name ="OrderDate" Type ="System.DateTime" DbType ="DateTime" CanBeNull ="true" /> < Column Name ="RequiredDate" Type ="System.DateTime" DbType ="DateTime" CanBeNull ="true" /> < Column Name ="ShippedDate" Type ="System.DateTime" DbType ="DateTime" CanBeNull ="true" /> < Column Name ="ShipVia" Type ="System.Int32" DbType ="Int" CanBeNull ="true" /> < Column Name ="Freight" Type ="System.Decimal" DbType ="Money" CanBeNull ="true" /> < Column Name ="ShipName" Type ="System.String" DbType ="NVarChar(40)" CanBeNull ="true" /> < Column Name ="ShipAddress" Type ="System.String" DbType ="NVarChar(60)" CanBeNull ="true" /> < Column Name ="ShipCity" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" /> < Column Name ="ShipRegion" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" /> < Column Name ="ShipPostalCode" Type ="System.String" DbType ="NVarChar(10)" CanBeNull ="true" /> < Column Name ="ShipCountry" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" /> < Association Name ="FK_Order_Details_Orders" Member ="OrderDetails" OtherKey ="OrderID" Type ="OrderDetails" DeleteRule ="NO ACTION" /> < Association Name ="FK_Orders_Customers" Member ="Customers" ThisKey ="CustomerID" Type ="Customers" IsForeignKey ="true" /> < Association Name ="FK_Orders_Employees" Member ="Employees" ThisKey ="EmployeeID" Type ="Employees" IsForeignKey ="true" /> < Association Name ="FK_Orders_Shippers" Member ="Shippers" ThisKey ="ShipVia" Type ="Shippers" IsForeignKey ="true" /> </ Type > </ Table > < Table Name ="dbo.Products" Member ="Products" > < Type Name ="Products" > < Column Name ="ProductID" Type ="System.Int32" DbType ="Int NOT NULL IDENTITY" IsPrimaryKey ="true" IsDbGenerated ="true" CanBeNull ="false" /> < Column Name ="ProductName" Type ="System.String" DbType ="NVarChar(40) NOT NULL" CanBeNull ="false" /> < Column Name ="SupplierID" Type ="System.Int32" DbType ="Int" CanBeNull ="true" /> < Column Name ="CategoryID" Type ="System.Int32" DbType ="Int" CanBeNull ="true" /> < Column Name ="QuantityPerUnit" Type ="System.String" DbType ="NVarChar(20)" CanBeNull ="true" /> < Column Name ="UnitPrice" Type ="System.Decimal" DbType ="Money" CanBeNull ="true" /> < Column Name ="UnitsInStock" Type ="System.Int16" DbType ="SmallInt" CanBeNull ="true" /> < Column Name ="UnitsOnOrder" Type ="System.Int16" DbType ="SmallInt" CanBeNull ="true" /> < Column Name ="ReorderLevel" Type ="System.Int16" DbType ="SmallInt" CanBeNull ="true" /> < Column Name ="Discontinued" Type ="System.Boolean" DbType ="Bit NOT NULL" CanBeNull ="false" /> < Association Name ="FK_Order_Details_Products" Member ="OrderDetails" OtherKey ="ProductID" Type ="OrderDetails" DeleteRule ="NO ACTION" /> < Association Name ="FK_Products_Categories" Member ="Categories" ThisKey ="CategoryID" Type ="Categories" IsForeignKey ="true" /> < Association Name ="FK_Products_Suppliers" Member ="Suppliers" ThisKey ="SupplierID" Type ="Suppliers" IsForeignKey ="true" /> </ Type > </ Table > < Table Name ="dbo.Region" Member ="Region" > < Type Name ="Region" > < Column Name ="RegionID" Type ="System.Int32" DbType ="Int NOT NULL" IsPrimaryKey ="true" CanBeNull ="false" /> < Column Name ="RegionDescription" Type ="System.String" DbType ="NChar(50) NOT NULL" CanBeNull ="false" /> < Association Name ="FK_Territories_Region" Member ="Territories" OtherKey ="RegionID" Type ="Territories" DeleteRule ="NO ACTION" /> </ Type > </ Table > < Table Name ="dbo.Shippers" Member ="Shippers" > < Type Name ="Shippers" > < Column Name ="ShipperID" Type ="System.Int32" DbType ="Int NOT NULL IDENTITY" IsPrimaryKey ="true" IsDbGenerated ="true" CanBeNull ="false" /> < Column Name ="CompanyName" Type ="System.String" DbType ="NVarChar(40) NOT NULL" CanBeNull ="false" /> < Column Name ="Phone" Type ="System.String" DbType ="NVarChar(24)" CanBeNull ="true" /> < Association Name ="FK_Orders_Shippers" Member ="Orders" OtherKey ="ShipVia" Type ="Orders" DeleteRule ="NO ACTION" /> </ Type > </ Table > < Table Name ="dbo.Suppliers" Member ="Suppliers" > < Type Name ="Suppliers" > < Column Name ="SupplierID" Type ="System.Int32" DbType ="Int NOT NULL IDENTITY" IsPrimaryKey ="true" IsDbGenerated ="true" CanBeNull ="false" /> < Column Name ="CompanyName" Type ="System.String" DbType ="NVarChar(40) NOT NULL" CanBeNull ="false" /> < Column Name ="ContactName" Type ="System.String" DbType ="NVarChar(30)" CanBeNull ="true" /> < Column Name ="ContactTitle" Type ="System.String" DbType ="NVarChar(30)" CanBeNull ="true" /> < Column Name ="Address" Type ="System.String" DbType ="NVarChar(60)" CanBeNull ="true" /> < Column Name ="City" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" /> < Column Name ="Region" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" /> < Column Name ="PostalCode" Type ="System.String" DbType ="NVarChar(10)" CanBeNull ="true" /> < Column Name ="Country" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" /> < Column Name ="Phone" Type ="System.String" DbType ="NVarChar(24)" CanBeNull ="true" /> < Column Name ="Fax" Type ="System.String" DbType ="NVarChar(24)" CanBeNull ="true" /> < Column Name ="HomePage" Type ="System.String" DbType ="NText" CanBeNull ="true" UpdateCheck ="Never" /> < Association Name ="FK_Products_Suppliers" Member ="Products" OtherKey ="SupplierID" Type ="Products" DeleteRule ="NO ACTION" /> </ Type > </ Table > < Table Name ="dbo.Territories" Member ="Territories" > < Type Name ="Territories" > < Column Name ="TerritoryID" Type ="System.String" DbType ="NVarChar(20) NOT NULL" IsPrimaryKey ="true" CanBeNull ="false" /> < Column Name ="TerritoryDescription" Type ="System.String" DbType ="NChar(50) NOT NULL" CanBeNull ="false" /> < Column Name ="RegionID" Type ="System.Int32" DbType ="Int NOT NULL" CanBeNull ="false" /> < Association Name ="FK_EmployeeTerritories_Territories" Member ="EmployeeTerritories" OtherKey ="TerritoryID" Type ="EmployeeTerritories" DeleteRule ="NO ACTION" /> < Association Name ="FK_Territories_Region" Member ="Region" ThisKey ="RegionID" Type ="Region" IsForeignKey ="true" /> </ Type > </ Table > </ Database >
也可以使用该dbml生成code,命令如下,可以用language选项,控制生成vb.net或c#语言的代码。 sqlmetal YourDbml.dbml /code: nwind.cs
在C#3.0入门系列(七)--之OR工具介绍 一文中,我们介绍了OR Designer工具,它生成的就是dbml,可以使用记事本打开DataClasses1.dbml 文件来看。有些属性,是无法从数据库中抽提出来的,比如继承,等。而我们又想对其映射做继承,就需要我们自己手工去修改dbml。好在OR Designer提供这些功能(以后再介绍)。关系型数据的映射 数据间的关系,有2种基本关系。1: 1 和1: M。可以参阅C# 3.0入门系列(二) 一文,查阅northwind数据库中的关系图。本文用Order 和Order Detail 表,来阐述其关系的映射。Order 为订单,Order Detail 为订单详情。其关系形式为,一条Order记录对应多条Order Detail 记录。 你可以在上面产生的code中,找到相应的order类,其中有这么一段
[Table(Name
=
"
Orders
"
)]
public
partial
class
Order : INotifyPropertyChanging, INotifyPropertyChanged
{ [Association(Name = " Order_OrderDetail " , Storage = " _OrderDetails " , OtherKey = " OrderID " )] public EntitySet < OrderDetail > OrderDetails { get { return this ._OrderDetails; } set { this ._OrderDetails.Assign(value); } } }
在Order类中,有个property,叫OrderDetails,是EntitySet<OrderDetail> 类型的。EntitySet是个集合类型的模板。 其继承关系如下 EntitySet<TEntity> : IList, ICollection, IList<TEntity>, ICollection<TEntity>, IEnumerable<TEntity>, IEnumerable, IListSource where TEntity : class
在OrderDetails类中,也可以找到这么一段。
[Table(Name
=
"
Order Details
"
)]
public
partial
class
OrderDetail : INotifyPropertyChanging, INotifyPropertyChanged
{ private EntityRef < Order > _Order; [Association(Name = " Order_OrderDetail " , Storage = " _Order " , ThisKey = " OrderID " , IsForeignKey = true )] public Order Order { get { return this ._Order.Entity; } set { Order previousValue = this ._Order.Entity; if (((previousValue != value) || ( this ._Order.HasLoadedOrAssignedValue == false ))) { this .SendPropertyChanging(); if ((previousValue != null )) { this ._Order.Entity = null ; previousValue.OrderDetails.Remove( this ); } this ._Order.Entity = value; if ((value != null )) { value.OrderDetails.Add( this ); this ._OrderID = value.OrderID; } else { this ._OrderID = default ( int ); } this .SendPropertyChanged( " Order " ); } } } }
也就是说Order 在OrderDetail类中,是以EntityRef出现的。这样,在关系双方的各端,我们使用EntityRef和EntitySet来表示其关系。简言之,One在Many端,以EntityRef出现,而Many在One端,以EntitySet出现。上例中,在property 中,因其返回的是this ._Order.Entity,直接返回的是Order。 对于1:1的关系,双双彼此在各自的类中,均以EntityRef出现。大家可以自己试。这样,Order和 OrderDetail 的关系,在各自的类中,都有了体现。体现方式的不同,反映了它们关系主体的不同。