Entity Frameworkマルチテーブルマルチ条件動的クエリー

13708 ワード

方式一Linq To Entity形式:
/// 
        ///      
        /// 
        ///   asc(  )    desc
        ///     
        ///     
        ///      
        ///       
        public IQueryable DaoChuData(YKTEntities db, string order, string sort, QueryEntity entity, int userId)
        {
            int? type_id = null;
            int? userComId = null;

            if (userId > 0)
            {
                var d = db.SMUSERTB.Where(x => x.USER_ID == userId).Select(x => new { x.TYPE_ID, x.COMPONENT_ID }).FirstOrDefault();
                type_id = d.TYPE_ID;
                userComId = d.COMPONENT_ID;
            }
            var data = from a in db.OMBASICCOMPONENTTB
                       join b in db.BASE_DICTIONARY on a.GUILD_CODE equals b.ITEM_VALUE
                       into ComDir
                       from b in ComDir.DefaultIfEmpty()
                       join c in db.OMCOMPONENTTYPETB on new { a.GUILD_CODE, a.TYPE_CODE } equals new { c.GUILD_CODE, c.TYPE_CODE } into BaseComType
                       from c in BaseComType.DefaultIfEmpty()
                       join d in db.OMBASICCOMPONENTTB on a.FATHER_ID equals d.COMPONENT_ID into BaseCom
                       from d in BaseCom.DefaultIfEmpty()
                       where a.STATUS != "0"
                       select new BasicComponentView
                       {
                           COMPONENT_NAME = a.COMPONENT_NAME,
                           SEGMENT2 = a.SEGMENT2,
                           SEGMENT4 = a.SEGMENT4,
                           REMARK = a.REMARK,
                           SEGMENT3 = a.SEGMENT3,
                           SEGMENT40 = a.SEGMENT40,
                           IndustryName = b.ITEM_NAME,
                           TypeName = c.TYPE_NAME,
                           ParentCommponent = d.COMPONENT_NAME,
                           FATHER_ID = a.FATHER_ID,
                           TYPE_CODE = a.TYPE_CODE,
                           GUILD_CODE = a.GUILD_CODE,
                           COMPONENT_ID = a.COMPONENT_ID,
                           COMMUN_ID = a.COMMUN_ID,
                           CommunName = db.OMCOMMUNICATIONTB.Where(x => x.COMMUN_ID == a.COMMUN_ID && x.STATUS != "D").
Select(x => x.COMMUN_NAME).FirstOrDefault()
                       };
            if (entity.Industry == "BUS") //     
            {
                data = data.Where(x => x.GUILD_CODE == "BUS");
                switch (entity.Type)
                {
                    case null:
                    case "":
                        if (type_id != 1)
                        {
                            data = data.Where(x => x.FATHER_ID == userComId).AsQueryable();
                        }
                        break;
                    case "1": //  
                        if (type_id != 1)
                        {
                            data = data.Where(x => x.FATHER_ID == userComId && x.TYPE_CODE == 2);
                        }
                        else
                        {
                            data = data.Where(x => x.TYPE_CODE == 1);
                        }
                        break;
                    case "2": //   
                        data = data.Where(x => x.TYPE_CODE == 2);
                        if (!string.IsNullOrEmpty(entity.Unit))
                        {
                            int unit = int.Parse(entity.Unit);
                            data = data.Where(x => x.FATHER_ID == unit);
                        }
                        break;
                    case "3"://  
                        data = data.Where(x => x.TYPE_CODE == 3);
                        if (!string.IsNullOrEmpty(entity.Com))
                        {
                            int id = int.Parse(entity.Com);
                            data = data.Where(x => x.FATHER_ID == id);
                        }
                        else if (string.IsNullOrEmpty(entity.Com) && !string.IsNullOrEmpty(entity.Unit))
                        {
                            int id = int.Parse(entity.Unit);
                            data = from m in data where (from f in db.OMBASICCOMPONENTTB where f.STATUS != "0" && f.FATHER_ID == id select
 f.COMPONENT_ID).Any(x => x == m.FATHER_ID) select m;
                        }
                        break;
                    case "4": //  
                        if (!string.IsNullOrEmpty(entity.Line))
                        {
                            data = data.Where(x => x.TYPE_CODE == 4);
                            int id = int.Parse(entity.Line);
                            data = data.Where(x => x.FATHER_ID == id);
                        }
                        else if (string.IsNullOrEmpty(entity.Line) && !string.IsNullOrEmpty(entity.Com))
                        {
                            data = data.Where(x => x.TYPE_CODE == 4);
                            int id = int.Parse(entity.Com);
                            data = from m in data where (from f in db.OMBASICCOMPONENTTB where f.STATUS != "0" && f.FATHER_ID == id 
select f.COMPONENT_ID).Any(x => x == m.FATHER_ID) select m;
                        }
                        else if (string.IsNullOrEmpty(entity.Line) && string.IsNullOrEmpty(entity.Com) && !string.IsNullOrEmpty(entity.Unit))
                        {
                            int id = int.Parse(entity.Unit);
                            var temp1 = db.OMBASICCOMPONENTTB.Where(x => x.STATUS != "0" && x.FATHER_ID == id).Select(x => x.COMPONENT_ID);
                            var temp2 = db.OMBASICCOMPONENTTB.Where(x => x.STATUS != "0").Select(x => x.COMPONENT_ID);
                            var temp = from m in temp2 where temp1.Any(x => x == m) select m;

                            data = from m in data where temp.Any(x => x == m.FATHER_ID) select m;
                        }
                        else
                        {
                            data = data.Where(x => x.TYPE_CODE == 4);
                        }

                        break;
                    default: //car
                        if (!string.IsNullOrEmpty(entity.Car))
                        {
                            int id = int.Parse(entity.Car);
                            data = data.Where(x => x.TYPE_CODE == 5 && x.FATHER_ID == id);
                        }
                        else if (string.IsNullOrEmpty(entity.Car) && !string.IsNullOrEmpty(entity.Line))
                        {
                            int id = int.Parse(entity.Line);
                            var temp = db.OMBASICCOMPONENTTB.Where(x => x.STATUS != "0" && x.FATHER_ID == id).Select(x => x.COMPONENT_ID);
                            data = from m in data where temp.Any(x => x == m.FATHER_ID) select m;
                        }
                        else if (string.IsNullOrEmpty(entity.Car) && string.IsNullOrEmpty(entity.Line) && !string.IsNullOrEmpty(entity.Com))
                        {
                            int id = int.Parse(entity.Com);
                            var temp1 = db.OMBASICCOMPONENTTB.Where(x => x.STATUS != "0" && x.FATHER_ID == id).Select(x => x.COMPONENT_ID);
                            var temp2 = db.OMBASICCOMPONENTTB.Where(x => x.STATUS != "0").Select(x => x.COMPONENT_ID);
                            var temp = from m in temp2 where temp1.Any(x => x == m) select m;

                            data = from m in data where temp.Any(x => x == m.FATHER_ID) select m;
                        }
                        else if (string.IsNullOrEmpty(entity.Car) && string.IsNullOrEmpty(entity.Line) && string.IsNullOrEmpty(entity.Com) &&
 !string.IsNullOrEmpty(entity.Unit))
                        {
                            int id = int.Parse(entity.Unit);
                            var temp1 = db.OMBASICCOMPONENTTB.Where(x => x.STATUS != "0" && x.FATHER_ID == id).Select(x => x.COMPONENT_ID);
                            var temp2 = db.OMBASICCOMPONENTTB.Where(x => x.STATUS != "0").Select(x => x.COMPONENT_ID);
                            var temp3 = db.OMBASICCOMPONENTTB.Where(x => x.STATUS != "0").Select(x => x.COMPONENT_ID);
                            var temp4 = from m in temp2 where temp1.Any(x => x == m) select m;

                            var temp = from m in temp3 where temp4.Any(x => x == m) select m;

                            data = from m in data where temp.Any(x => x == m.FATHER_ID) select m;
                        }
                        else
                        {
                            data = data.Where(x => x.TYPE_CODE == 5);
                        }
                        break;
                }
            }
            else if (string.IsNullOrEmpty(entity.Industry))//     
            {

            }
            else //         
            {
                data = data.Where(x => x.GUILD_CODE == entity.Industry);
                if (string.IsNullOrEmpty(entity.Type))
                {
                    if (type_id != 1)
                    {
                        data = data.Where(x => x.FATHER_ID == userComId);
                    }

                }
                else if (entity.Type == "1")
                {
                    if (type_id != 1)
                    {
                        data = data.Where(x => x.FATHER_ID == userComId && x.TYPE_CODE == 2);
                    }
                    else
                    {
                        data = data.Where(x => x.TYPE_CODE == 1);
                    }
                }
                else if (entity.Type == "2")
                {
                    data = data.Where(x => x.TYPE_CODE == 2);
                    if (!string.IsNullOrEmpty(entity.Unit))
                    {
                        int id = int.Parse(entity.Unit);
                        data = data.Where(x => x.FATHER_ID == id);
                    }
                }
                else
                {
                    if (!string.IsNullOrEmpty(entity.Com))
                    {
                        int id = int.Parse(entity.Com);
                        data = data.Where(x => x.TYPE_CODE == 5 && x.FATHER_ID == id);
                    }
                    else if (entity.Com == "" && entity.Unit != "")
                    {
                        int id = int.Parse(entity.Unit);
                        data = data.Where(x => x.TYPE_CODE == 3);
                        var temp = db.OMBASICCOMPONENTTB.Where(x => x.STATUS != "0" && x.FATHER_ID == id).Select(x => x.COMPONENT_ID).FirstOrDefault();
                        data = data.Where(x => x.FATHER_ID == temp);
                    }
                    else
                    {
                        int id = int.Parse(entity.Type);
                        data = data.Where(x => x.TYPE_CODE == id);
                    }
                }
            }

            if (!string.IsNullOrEmpty(entity.Name))
            {
                data = data.Where(x => x.COMPONENT_NAME.Contains(entity.Name));
            }
            if (data != null)
            {
                data = LinqHelper.DataSorting(data, sort, order);
            }

            return data;

        }


方式二つづり文字列:
     /// 
        ///      
        /// 
        ///         
        ///     
        ///   asc(  )    desc
        ///      
        ///      
        ///       
        public IQueryable DaoChuData(YKTEntities db, string order, string sort, string search, params object[] listQuery)
        {
            string where = string.Empty;
            int flagWhere = 0;

            Dictionary queryDic = ValueConvert.StringToDictionary(search.GetString());
            if (queryDic != null && queryDic.Count > 0)
            {
                foreach (var item in queryDic)
                {
                    if (flagWhere != 0)
                    {
                        where += " and ";
                    }
                    flagWhere++;

                    if (!string.IsNullOrWhiteSpace(item.Key) && !string.IsNullOrWhiteSpace(item.Value) && item.Key.Equals("STATUS")) //       
                    {
                        where += "it." + item.Key + " = '" + item.Value + "'";
                        continue;
                    }
                    where += "it." + item.Key + " like '%" + item.Value + "%'";
                }
            }
            return db.SMROLETB
                     .Where(string.IsNullOrEmpty(where) ? "true" : where)
                     .OrderBy("it." + sort.GetString() + " " + order.GetString())
                     .AsQueryable();
        }