Linq - join

最新推荐文章于 2024-07-25 10:12:11 发布

原创最新推荐文章于 2024-07-25 10:12:11 发布 · 222 阅读

CC 4.0 BY-SA版权

本文介绍了SQL查询中多条件连接和分组查询的应用，通过具体示例展示了如何使用JOIN、GROUP BY等操作来实现复杂的数据筛选与聚合，包括左连接查询、分组聚合及条件过滤技巧。

多条件：

var queyAnnual = from A in queryVw
                                 join q in leaveDefault on new { A.LeaveType, A.EmployeeID } equals new { q.LeaveType, q.EmployeeCode }
                                 select new
                                 {
                                     A,
                                     AnnulLeaveTo = q.LeaveType
                                 };

var query = people.GroupJoin(pets, person => person, pet => pet.Owner, (person, petCollection) => new { OwnerName = person.Name, Pet = PetCollection.Select( p => p.Name ) .DefaultIfEmpty() } ).ToList();

左链接：

Group BY join

public IList<StructureRootDTO> GetRootStructureInfos()
        {
            using (ETCDataContext etcDataContext = this.EtcDataContext)
            {
                var query =
                       from item in
                           (
                               from parent in etcDataContext.SYS_Structures
                               where parent.ParentNode == "root"
                               join child in etcDataContext.SYS_Structures
                               on parent.Name equals child.ParentNode into childAll
                               from childItem in childAll.DefaultIfEmpty()
                               select new
                               {
                                   parent.ID,
                                   parent.Name,
                                   childItem.ParentNode
                               }
                           )
                       group item by item.Name into groupChild                      
                       select new StructureRootDTO
                       {
                           ID = groupChild.Max(o => o.ID),
                           Name = groupChild.Max(o => o.Name),
                           ChildParentNodeName = groupChild.Max(o => o.ParentNode)
                       };
                return query.OrderBy(o => o.ID).ToList();
            }
        }