实体关系的使用

好了，现在我们就可以在查询句法中直接关联表了（数据库中不一定要设置表的外键关系）：

 

       Response.Write("-------------查询分类为1 的版块-------------<br/>");

 

var query1 = from b inctx.Boards where b.Category.CategoryID == 1 select b;

 

foreach (Board b inquery1)

 

           Response.Write(b.BoardID + " " + b.BoardName + "<br/>");

 

       Response.Write("-------------查询版块大于2 个的分类-------------<br/>");

 

var query2 = from c inctx.BoardCategories where c.Boards.Count > 2 select c;

 

foreach (BoardCategory c inquery2)

 

           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 inctx.BoardCategories where c.Boards.Count > 2 select c;

 

foreach (BoardCategory c inquery2)

 

           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]