LINQ中的连接(join)用法示例

来源：互联网发布：粉晶手串淘宝编辑：程序博客网时间：2024/05/29 11:03

http://www.cnblogs.com/kongxiaoshuang/p/6782381.html

Linq中连接主要有组连接、内连接、左外连接、交叉连接四种。各个用法如下。

1、组连接

组连接是与分组查询是一样的。即根据分组得到结果。如下例,根据publisther分组得到结果。

使用组连接的查询语句如下：

 //使用组连接            var GroupQuery = from publisher in SampleData.Publishers                             join book in SampleData.Books                                  on publisher equals book.Publisher into publisherBooks                             select new                             {                                 PublisherName = publisher.Name,                                 Books = publisherBooks                             };

与上边等同的GroupBy语句如下：

//使用Group            var QueryByGroup = from book in SampleData.Books                        group book by book.Publisher into grouping                        select new                        {                            PublisherName = grouping.Key.Name,                            Books = grouping                        };

2、内连接

内连接与SqL中inner join一样，即找出两个序列的交集。如下例找出book中的Publisher存在于SampleData.Publishers的资料。

内连接查询语句如下：

//join查询语句            var joinQuery = from publisher in SampleData.Publishers                            join book in SampleData.Books                                on publisher equals book.Publisher                            select new                            {                                PublisherName = publisher.Name,                                BookName = book.Title                            };

与上边等同的查询操作符语句如下：

//join操作符语句            SampleData.Publishers.Join(                SampleData.Books,               //join 对象                publisher => publisher,         //外部的key                book => book.Publisher,         //内部的key                (publisher, book) => new        //结果                {                    PublisherName = publisher.Name,                    BookName = book.Title                });

3、左外连接

左外连接与SqL中left join一样。如下例找出根据publisher中找出SampleData.Publishers中所有资料和book中存在于publisher的资料。

左外连接查询语句如下：

//left join, 为空时用default            var leftJoinQuerybyDefault = from publisher in SampleData.Publishers                                         join book in SampleData.Books                                           on publisher equals book.Publisher into publisherBooks                                         from book in publisherBooks.DefaultIfEmpty()                                         select new                                         {                                             PublisherName = publisher.Name,                                             BookName = (book == default(Book)) ? "" : book.Title //这里主要第二个集合有可能为空。需要判断                                         };

注：上例中使用了DefaultIfEmpty操作符，它能够为实序列提供一个默认的元素。DefaultIfEmpty使用了泛型中的default关键字。default关键字对于引用类型将返回null,而对于值类型则返回0。对于结构体类型，则会根据其成员类型将它们相应地初始化为null(引用类型)或0(值类型)。我们可以不使用default关键字，但在要DefaultIfEmpty中给定当空时的默认对象值。语句如下：

//left join, 为空时使用默认对象            var leftJoinQuery = from publisher in SampleData.Publishers                                        join book in SampleData.Books                                          on publisher equals book.Publisher into publisherBooks                                        from book in publisherBooks.DefaultIfEmpty(                                        new Book { Title = "" }                         //设置为空时的默认值                                        )                                        select new                                        {                                            PublisherName = publisher.Name,                                            BookName = book.Title                                        };

4、交叉连接

交叉连接与SqL中Cross join一样。如下例中找出SampleData.Publishers与SampleData.Books的交叉连接。

交叉连接查询语句：

var crossJoinQuery = from publisher in SampleData.Publishers                                 from book in SampleData.Books                                 select new                                 {                                     PublisherName = publisher.Name,                                     BookName = book.Title                                 };

查询操作符语句：

//不使用查询表达式            SampleData.Publishers.SelectMany(publisher => SampleData.Books.Select(                book => new                {                    PublisherName = publisher.Name,                    BookName = book.Title                }                ));

5.Linq实现左连接，写法如下

 var leftJoinSql = from student in db.Student                              join book in db.Book on student.ID equals book.StudentID into temp                              from tt in temp.DefaultIfEmpty()                              select new                              {                                   sname= student.Name,                                   bname = tt==null?"":tt.Name//这里主要第二个集合有可能为空。需要判断                              };

6.Linq实现右连接，写法如下

 var rightJoinSql = from book in db.Book                               join stu in db.Student on book.StudentID equals stu.ID into joinTemp                               from tmp in joinTemp.DefaultIfEmpty()                               select new {                                sname=tmp==null?"":tmp.Name,                               bname=book.Name                               };

阅读全文

0 0