奇迹从脚开始

导航

NHibernate Linq 的 join (联合查询) 的例子

    1. 如果一个表中的id为非空而另一个表的id为可空 如何链接
    var q =
    from o in db.Orders
    join e in db.Employees
    on o.Employee.EmployeeId equals (int?) e.EmployeeId into emps
    from e in emps
    select new {o.OrderId, e.FirstName};

    2. 用where连接

    var q =
    from e1 in db.Employees
    from e2 in e1.Subordinates
    where e1.Address.City == e2.Address.City
    select new
    {
    FirstName1 = e1.FirstName,
    LastName1 = e1.LastName,
    FirstName2 = e2.FirstName,
    LastName2 = e2.LastName,
    e1.Address.City
    };

    3.用join后的结果统计

    var q =
    from c in db.Customers
    join o in db.Orders on c.CustomerId equals o.Customer.CustomerId into orders
    select new {c.ContactName, OrderCount = orders.Average(x => x.Freight)};
    var q =
    from c in db.Customers
    join o in db.Orders on c.CustomerId equals o.Customer.CustomerId into ords
    join e in db.Employees on c.Address.City equals e.Address.City into emps
    select new {c.ContactName, ords = ords.Count(), emps = emps.Count()};

    4.group join

    var q = from c in db.Customers
    join o in db.Orders on c.CustomerId equals o.Customer.CustomerId
    group new { c, o } by c.ContactName
    into g
    select new { ContactName = g.Key, OrderCount = g.Average(i => i.o.Freight) };  

posted on 2011-01-17 04:47  脚上的奇迹  阅读(1188)  评论(0编辑  收藏  举报