★适用场景:在表关系中有一对一关系,一对多关系,多对多关系。对各表之间的关系,就用这些实现对多个表的操作。

1:一对多关系(1 to Many)

var query=
    from c in db.Customers
    from o in c.Orders
    where c.City == "London"
    select o;

 



语句描述:Customers和Orders是一对多的关系,即Orders在Customers类中是以IDbSet形式出现。所以第二个from是从c.Orders而不是从db.Orders中筛选。

注:该语句生成的Sql语句为:

SELECT t1.OrderID, t1.CustomerID AS CustomerID1,t1.OrderDate
FROM Customers AS t0
INNER JOIN Orders AS t1 ON (t1.CustomerID = t0.CustomerID)
WHERE (t0.City = @p0)
2:多对多关系(Many to Many)

 

  var q =
      (from e in db.Orders
       from et in e.Details 
       from ett in db.Products.Where(o=>o.ID==et.ProductID)
       where e.CustomerID == "ALFKI"
       
       select new
       {
           e.CustomerID, 
           et.ProductID
       }).ToList();
语句描述:多对多连接一般会涉及三个表(如果有一个表是自连接的那就可能是2个表)。这个语句涉及三个表Orders,Order Details,Products。他们的关系是1:M:1。

注:生成的Sql语句是:

 

SELECT t0.CustomerID, t1.ProductID
FROM Orders AS t0
INNER JOIN [Order Details] AS t1
  ON (t1.OrderID = t0.OrderID)
INNER JOIN Products AS t2
  ON (t2.ID = t1.ProductID)
WHERE (t0.CustomerID = @p0)

Last edited Nov 6, 2012 at 9:54 AM by lf_dream8, version 7

Comments

No comments yet.