SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IFNOTEXISTS (SELECT*FROM dbo.sysobjects WHERE id =OBJECT_ID(N'[dbo].[User]') ANDOBJECTPROPERTY(id, N'IsUserTable') =1) BEGIN CREATETABLE[dbo].[User]( [UserId][nchar](10) NOTNULL, [UserName][nchar](10) NULL, CONSTRAINT[PK_User]PRIMARYKEYCLUSTERED ( [UserId]ASC ) ON[PRIMARY] ) ON[PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IFNOTEXISTS (SELECT*FROM dbo.sysobjects WHERE id =OBJECT_ID(N'[dbo].[Group]') ANDOBJECTPROPERTY(id, N'IsUserTable') =1) BEGIN CREATETABLE[dbo].[Group]( [GroupId][nchar](10) NOTNULL, [GroupName][nchar](10) NULL, CONSTRAINT[PK_Group]PRIMARYKEYCLUSTERED ( [GroupId]ASC ) ON[PRIMARY] ) ON[PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IFNOTEXISTS (SELECT*FROM dbo.sysobjects WHERE id =OBJECT_ID(N'[dbo].[UserInGroup]') ANDOBJECTPROPERTY(id, N'IsUserTable') =1) BEGIN CREATETABLE[dbo].[UserInGroup]( [UserId][nchar](10) NOTNULL, [GroupId][nchar](10) NOTNULL, CONSTRAINT[PK_UserInGroup]PRIMARYKEYCLUSTERED ( [UserId]ASC, [GroupId]ASC ) ON[PRIMARY] ) ON[PRIMARY] END GO IFNOTEXISTS (SELECT*FROM dbo.sysobjects WHERE id =OBJECT_ID(N'[dbo].[FK_UserInGroup_Group]') AND type ='F') ALTERTABLE[dbo].[UserInGroup]WITHCHECKADDCONSTRAINT[FK_UserInGroup_Group]FOREIGNKEY([GroupId]) REFERENCES[dbo].[Group] ([GroupId]) GO ALTERTABLE[dbo].[UserInGroup]CHECKCONSTRAINT[FK_UserInGroup_Group] GO IFNOTEXISTS (SELECT*FROM dbo.sysobjects WHERE id =OBJECT_ID(N'[dbo].[FK_UserInGroup_User]') AND type ='F') ALTERTABLE[dbo].[UserInGroup]WITHCHECKADDCONSTRAINT[FK_UserInGroup_User]FOREIGNKEY([UserId]) REFERENCES[dbo].[User] ([UserId]) GO ALTERTABLE[dbo].[UserInGroup]CHECKCONSTRAINT[FK_UserInGroup_User]
现在的问题是:User和Group的实体对象中,可不可以直接引用对方呢?就像User.Groups 或Group.Users。我们可不可以去改变其映射,直接越过UserInGroup,让User和Group直接发生关系呢?回答是肯定的。但是,数据库中,依然是三个表。你不可能在数据库中越过表UserInGroup的。表UserInGroup起了一个纽带的作用,想把它抛弃掉,不太可能。那可不可以,在mapping code中,做些手脚,让Linq To Sql自己去找这个纽带呢?回答是,Linq To Object可以,但是, Linq To Sql不可以。
为什么Linq To Sql不可以?其原因就是Linq To Sql需要先翻译成Sql语句,而,这个映射,必须真实反映数据库里表的情况。也就说,这个映射,无法逾越UserInGroup实体。它必须要有UserInGroup。 那Linq To Object怎么样可以呢?把下面的代码,加入到你的工程中。
using System.Data.Linq; using System.Data.Linq.Mapping; using System.Data; using System.Collections.Generic; using System.Reflection; using System.Linq; using System.Linq.Expressions; using System.Runtime.Serialization; using System.ComponentModel; using System; namespace ConsoleApplication1 { public partial class Group { public List<User> Users { get { return (from u inthis.UserInGroups select u.User).ToList(); } } } public partial class User { public List<Group> Groups { get { return (from g inthis.UserInGroups select g.Group ).ToList(); } } } }
publicstaticvoid Main(string[] arg) { DataClasses1DataContext db =new DataClasses1DataContext(); db.Log = Console.Out; var userSet = (from u in db.Users select u).ToList(); foreach (var use in userSet) { foreach (var group in use.Groups) { Console.WriteLine(group.GroupName); } } var groupSet = (from u in db.Groups select u).ToList(); foreach (var group in groupSet) { foreach (var user in group.Users) { Console.WriteLine(user.UserName); } } }
太好了。这正是我们想要的。那再来做个反面的测试吧。
var q = (from u in db.Users from g in u.Groups select new { u.UserName, g.GroupName }).ToList();
编译通过,在run-time时,出错。因为,Linq To Sql不知道该怎么去找这个关系。那么下面这个呢?
var q2 = (from u1 in (from u in db.Users select u).ToList() from g in u1.Groups select new { u1.UserName, g.GroupName }).ToList();
run-time没有问题。纠起原因,其在里面已经取回了数据,是Linq To Object的范畴了。
还可以使用下面的这个映射code。
using System.Data.Linq; using System.Data.Linq.Mapping; using System.Data; using System.Collections.Generic; using System.Reflection; using System.Linq; using System.Linq.Expressions; using System.Runtime.Serialization; using System.ComponentModel; using System; namespace ConsoleApplication1 { public partial class Group { public IEnumerable<User> Users { get { return from u inthis.UserInGroups select u.User; } } } public partial class User { public IEnumerable<Group> Groups { get { return from g inthis.UserInGroups select g.Group; } } } }