您的位置:知识库 » 数据库

一步一步学Linq to sql(八):继承与关系

作者: lovecherry  来源: 博客园  发布时间: 2008-09-26 23:14  阅读: 10568 次  推荐: 1   原文链接   [收藏]  

系列文章导航:

一步一步学Linq to sql(一):预备知识

一步一步学Linq to sql(二):DataContext与实体

一步一步学Linq to sql(三):增删改

一步一步学Linq to sql(四):查询句法

一步一步学Linq to sql(五):存储过程

一步一步学Linq to sql(六):探究特性

一步一步学Linq to sql(七):并发与事务

一步一步学Linq to sql(八):继承与关系

一步一步学Linq to sql(九):其它补充

一步一步学Linq to sql(十):分层构架的例子


实体关系的使用
    好了,现在我们就可以在查询句法中直接关联表了(数据库中不一定要设置表的外键关系):

        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]

    今天就讲到这里。大家可以自己尝试为帖子表也定义实体的关系,因为,是不是可以直接通过帖子获取帖子下的回复,或者直接通过回复得到所属帖子那?

1
0

数据库热门文章

    数据库最新文章

      最新新闻

        热门新闻