none
想问关于Linq表中1对1或1对0..1查询 RRS feed

  • 问题

  • 先打个以下例子,是1个Orders对多个OrderDetail查询,但有时设计数据时不一定多表关系,比如Users和Membership就是1对1,请问是怎样写?

    IQueryable<Orders> query = from o in db.Orders.Include("OrderDetail")
                         select o;

    Samson
    2011年3月22日 10:27

答案

  • Hello Samson

     

    上面的代码是典型的Inner Join的实现, 我想你需要用的是Left Outer Join,下面就是介绍怎样用Inner Join Left Outer JoinFAQ,希望能帮到你!

    OUTER JOIN

    我们可以像用SQLleft outer join一样用GroupJoin运算符。编译器通过在Join语句的下一语句加入关键字来区分GroupJoinJoin运算符。当给定的部门不存在课程的时候,如果我们需要枚举内部序列项目的每一个元素一个空值,这种工作就留给SelectManyDefaultIfEnpty扩展方法去做。

     

    运用GroupJoin运算符:

    ==========================================================

    var query = from d in db.Departments

                         join c in db.Courses

                         ond.DepartmetnID equals c.DepartmentID into courseList

                         select new

                         {

                             DepartmentName = d.Name,

                             Courses = courseList

                         };

    ========================================================== 

    运用GroupJoin扩展方法:

    ==========================================================

    var query = db.Departments.GroupJoin(db.Courses,

                                                                             d =>d.DepartmentID,

                                                                             c =>c.DepartmentID,

                                                                             (d, courseList) =>new

                                                                                                            {

                                                                                                                 DepartmentName = d.Name,

                                                                                                                 Courses = courseList

                                                                                                            });

    ==========================================================

     

    运用SelectManyDefaultIfEmpty:

    ==========================================================

    var query = from d in db.Departments

                         join c in db.Courses

                         ond.DepartmentID equals c.DepartmentID into courseList

                         from cl in courseList.DefaultIfEmpty()

                         select new

                         {

                              DepartmentName = d.Name,

                              CourseName = cl == null ?string.Empty :cl.Title

                         }

    ========================================================= 

    相关帖子:

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/fb6a48ea-cd98-4304-95a9-bd4534523fec

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/b6f2beff-ccdb-419d-9464-08a977d1bce5

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/541706be-8d21-4618-a0c0-72fa2ffdd047

     

    相关参考:

    http://codeexperiment.com/post/LINQ-To-SQL-Outer-Joins.aspx

    http://odetocode.com/blogs/scott/archive/2008/03/24/inner-outer-lets-all-join-together-with-linq.aspx

    谢谢!


    Jackie Sun [MSFT]
    如果您对我们的论坛在线支持服务有任何的意见或建议,请通过邮件告诉我们。
    MSDN 论坛好帮手 立刻免费下载  MSDN 论坛好帮手
    • 已标记为答案 Samson77 2011年3月25日 10:17
    2011年3月25日 1:58
    版主

全部回复

  • hi~~

    try:

     

    var query=from o in db.Orders

                   join od in db.OrderDetails

                  on o.odID equals od.id

                  select o;


    My CSDN HomePage
    2011年3月22日 23:54
  • 这几天时间真小,我没有时间表达清楚,其实我是做wpf+实体数据库edmx,其实我都是接触不久,如果说错请指交,他每一个表都是一个实例,所以我谈以下这可实现的事件IQueryable<Orders> query = from o in db.Orders.Include("OrderDetail")这查询是Orders包括OrderDetail这个表,他们关系是1对*,绑定是以下

    <Window.Resources>
      <CollectionViewSource x:Key="MasterViewSource" />
      <CollectionViewSource Source="{Binding Source={StaticResource MasterViewSource}, Path=OrderDetail}" x:Key="DetailsViewSource" />
     </Window.Resources>
    IQueryable<Orders> query = from o in db.Orders.Include("OrderDetail")
    select o;
    this.OrderData = new OrdersCollection(query, db);
    
    this.MasterViewSource = (CollectionViewSource)this.FindResource("MasterViewSource");
    this.DetailViewSource = (CollectionViewSource)this.FindResource("DetailsViewSource");
    this.MasterViewSource.Source = this.OrderData;
    
    this.MasterView = (ListCollectionView)this.MasterViewSource.View;
    this.DetailsView = (BindingListCollectionView)this.DetailViewSource.View;
    

    这是对应多表,增加时调

    this.MasterView.AddNew();
    this.MasterView.CommitNew();
    
    this.DetailsView.AddNew();
    this.DetailsView.CommitNew();
    
    又回到标题所说1对1时,实体数据库是以1个表为1实例,就是说上面这种查询应该不可以,请问我要什么实现1对1表增加。
    Samson
    2011年3月23日 11:28
  • Hello Samson

     

    上面的代码是典型的Inner Join的实现, 我想你需要用的是Left Outer Join,下面就是介绍怎样用Inner Join Left Outer JoinFAQ,希望能帮到你!

    OUTER JOIN

    我们可以像用SQLleft outer join一样用GroupJoin运算符。编译器通过在Join语句的下一语句加入关键字来区分GroupJoinJoin运算符。当给定的部门不存在课程的时候,如果我们需要枚举内部序列项目的每一个元素一个空值,这种工作就留给SelectManyDefaultIfEnpty扩展方法去做。

     

    运用GroupJoin运算符:

    ==========================================================

    var query = from d in db.Departments

                         join c in db.Courses

                         ond.DepartmetnID equals c.DepartmentID into courseList

                         select new

                         {

                             DepartmentName = d.Name,

                             Courses = courseList

                         };

    ========================================================== 

    运用GroupJoin扩展方法:

    ==========================================================

    var query = db.Departments.GroupJoin(db.Courses,

                                                                             d =>d.DepartmentID,

                                                                             c =>c.DepartmentID,

                                                                             (d, courseList) =>new

                                                                                                            {

                                                                                                                 DepartmentName = d.Name,

                                                                                                                 Courses = courseList

                                                                                                            });

    ==========================================================

     

    运用SelectManyDefaultIfEmpty:

    ==========================================================

    var query = from d in db.Departments

                         join c in db.Courses

                         ond.DepartmentID equals c.DepartmentID into courseList

                         from cl in courseList.DefaultIfEmpty()

                         select new

                         {

                              DepartmentName = d.Name,

                              CourseName = cl == null ?string.Empty :cl.Title

                         }

    ========================================================= 

    相关帖子:

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/fb6a48ea-cd98-4304-95a9-bd4534523fec

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/b6f2beff-ccdb-419d-9464-08a977d1bce5

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/541706be-8d21-4618-a0c0-72fa2ffdd047

     

    相关参考:

    http://codeexperiment.com/post/LINQ-To-SQL-Outer-Joins.aspx

    http://odetocode.com/blogs/scott/archive/2008/03/24/inner-outer-lets-all-join-together-with-linq.aspx

    谢谢!


    Jackie Sun [MSFT]
    如果您对我们的论坛在线支持服务有任何的意见或建议,请通过邮件告诉我们。
    MSDN 论坛好帮手 立刻免费下载  MSDN 论坛好帮手
    • 已标记为答案 Samson77 2011年3月25日 10:17
    2011年3月25日 1:58
    版主
  • 谢谢版主帮助,今天想来想去,发觉是不是方向错了,实体数据模型操作是一个实例,怎样包好像都是不对,刚刚想应该回到原点,现在常试用复杂类型解决,希望我想的是对。
    Samson
    2011年3月25日 10:16