linq 左连接三表筛选指定字段集合通过union连接单表筛选指定字段集合

注意！

左连接：是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。

左连接 linq：

IQueryable<MenuDataModel> menus;

             menus =  (from fu in _context.B2C_FUNCTIONLIST.Where(a => a.PARENT_FUNC != null).OrderBy(a => a.FUNCTIONID)
                       join gr in _context.B2C_GROUPFUNCTION on fu.FUNCTIONID equals gr.FUNCTIONID
                        join me in _context.B2C_MEMBERS.Where(a => a.USERNAME == userName)
                          on gr.GROUPID equals me.GROUP_ID
                        select new MenuDataModel
                        {
                            MenuId = fu.FUNCTIONID,
                            MenuName = fu.FUNCTIONNAME,
                            parent = fu.PARENT_FUNC,
                            url = fu.URL,
                            sortby = fu.SORTBY,
                            enabled = fu.ENABLED,
                        })

 

 

通过 union 连接另一个集合，形成新的数据集合

IQueryable<MenuDataModel> menus;

             menus =  (from fu in _context.B2C_FUNCTIONLIST.Where(a => a.PARENT_FUNC != null).OrderBy(a => a.FUNCTIONID)
                       join gr in _context.B2C_GROUPFUNCTION on fu.FUNCTIONID equals gr.FUNCTIONID
                        join me in _context.B2C_MEMBERS.Where(a => a.USERNAME == userName)
                          on gr.GROUPID equals me.GROUP_ID
                        select new MenuDataModel
                        {
                            MenuId = fu.FUNCTIONID,
                            MenuName = fu.FUNCTIONNAME,
                            parent = fu.PARENT_FUNC,
                            url = fu.URL,
                            sortby = fu.SORTBY,
                            enabled = fu.ENABLED,
                        }).Union        //union连接  将两个集合进行合并操作，过滤相同的项 （查询字段需一致
                        (from f in _context.B2C_FUNCTIONLIST.Where(a => a.PARENT_FUNC ==null).OrderBy(a => a.FUNCTIONID)
                         select new MenuDataModel
                         {
                             MenuId = f.FUNCTIONID,
                             MenuName = f.FUNCTIONNAME,
                             parent = f.PARENT_FUNC,
                             url = f.URL,
                             sortby = f.SORTBY,
                             enabled = f.ENABLED,

                         });