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

Question

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

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

---

Samson

Answer 1

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

Answer 2

这几天时间真小，我没有时间表达清楚，其实我是做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

Answer 3

Hello Samson，

 

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

OUTER JOIN：

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

 

运用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

                                                                                                        });

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

 

运用SelectMany和DefaultIfEmpty:

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

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 论坛好帮手

Answer 4

谢谢版主帮助，今天想来想去，发觉是不是方向错了，实体数据模型操作是一个实例，怎样包好像都是不对，刚刚想应该回到原点，现在常试用复杂类型解决，希望我想的是对。

---

Samson