一步一步学Linq to sql（八）：继承与关系
[2] 一步一步学Linq to sql（八）：继承与关系
[3] 一步一步学Linq to sql（八）：继承与关系
[4] 一步一步学Linq to sql（八）：继承与关系
[5] 一步一步学Linq to sql（八）：继承与关系
系列文章导航：
一步一步学Linq to sql（二）：DataContext与实体
实体关系的使用
好了，现在我们就可以在查询句法中直接关联表了（数据库中不一定要设置表的外键关系）：
|
Response.Write("-------------查询分类为1的版块-------------<br/>"); var query1 = from b in ctx.Boards where b.Category.CategoryID == 1 select b; foreach (Board b in query1) Response.Write(b.BoardID + " " + b.BoardName + "<br/>"); Response.Write("-------------查询版块大于2个的分类-------------<br/>"); var query2 = from c in ctx.BoardCategories where c.Boards.Count > 2 select c; foreach (BoardCategory c in query2) Response.Write(c.CategoryID + " " + c.CategoryName + " " + c.Boards.Count + "<br/>"); |
在数据库中加一些测试数据，如下图：
运行程序后得到下图的结果：
我想定义实体关系的方便我不需要再用语言形容了吧。执行上述的程序会导致下面SQL的执行：
|
SELECT [t0].[BoardID], [t0].[BoardName], [t0].[BoardCategory] FROM [Boards] AS [t0] INNER JOIN [Categories] AS [t1] ON [t1].[CategoryID] = [t0].[BoardCategory] WHERE [t1].[CategoryID] = @p0 -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
SELECT [t0].[CategoryID], [t0].[CategoryName] FROM [Categories] AS [t0] WHERE (( SELECT COUNT(*) FROM [Boards] AS [t1] WHERE [t1].[BoardCategory] = [t0].[CategoryID] )) > @p0 -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2]
SELECT [t0].[BoardID], [t0].[BoardName], [t0].[BoardCategory] FROM [Boards] AS [t0] WHERE [t0].[BoardCategory] = @p0 -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1] |
可以看到，第二个查询并没有做外连接，还记得DataLoadOptions吗？我们可以要求Linq to sql在读取版块分类信息的时候也把版块信息一起加载：
|
DataLoadOptions options = new DataLoadOptions(); options.LoadWith<BoardCategory>(c => c.Boards); ctx.LoadOptions = options; Response.Write("-------------查询版块大于2个的分类-------------<br/>"); var query2 = from c in ctx.BoardCategories where c.Boards.Count > 2 select c; foreach (BoardCategory c in query2) Response.Write(c.CategoryID + " " + c.CategoryName + " " + c.Boards.Count + "<br/>"); |
查询经过改造后会得到下面的SQL：
|
SELECT [t0].[CategoryID], [t0].[CategoryName], [t1].[BoardID], [t1].[BoardName], [t1].[BoardCategory], ( SELECT COUNT(*) FROM [Boards] AS [t3] WHERE [t3].[BoardCategory] = [t0].[CategoryID] ) AS [count] FROM [Categories] AS [t0] LEFT OUTER JOIN [Boards] AS [t1] ON [t1].[BoardCategory] = [t0].[CategoryID] WHERE (( SELECT COUNT(*) FROM [Boards] AS [t2] WHERE [t2].[BoardCategory] = [t0].[CategoryID] )) > @p0 ORDER BY [t0].[CategoryID], [t1].[BoardID] -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2] |
|
BoardCategory dbcat = new BoardCategory() { CategoryName = "Database" }; Board oracle = new Board() { BoardName = "Oracle", Category = dbcat}; ctx.BoardCategories.Add(dbcat); ctx.SubmitChanges(); |
上述代码导致下面的SQL被执行：
|
INSERT INTO [Categories]([CategoryName]) VALUES (@p0)
SELECT [t0].[CategoryID] FROM [Categories] AS [t0] WHERE [t0].[CategoryID] = (SCOPE_IDENTITY())
-- @p0: Input AnsiString (Size = 8; Prec = 0; Scale = 0) [Database]
INSERT INTO [Boards]([BoardName], [BoardCategory]) VALUES (@p0, @p1)
SELECT [t0].[BoardID] FROM [Boards] AS [t0] WHERE [t0].[BoardID] = (SCOPE_IDENTITY())
-- @p0: Input AnsiString (Size = 6; Prec = 0; Scale = 0) [Oracle] -- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [23] |
