Entity framework多表查询



/// <summary>
/// 查询的数据
///</summary>
///<param name="order">升序asc（默认）还是降序desc</param>
///<param name="sort">排序字段</param>
///<param name="search">查询条件</param>
///<param name="listQuery">额外的参数</param>
///<returns></returns>
public IQueryable<BasicComponentView> 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 onnew { 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_IDinto 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)
{
casenull:
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);
}
elseif (string.IsNullOrEmpty(entity.Com) && !string.IsNullOrEmpty(entity.Unit))
{
int id =int.Parse(entity.Unit);
data =from m in data where (from f in db.OMBASICCOMPONENTTBwhere f.STATUS != "0" && f.FATHER_ID == idselect
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);
}
elseif (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.OMBASICCOMPONENTTBwhere f.STATUS != "0" && f.FATHER_ID == id
select f.COMPONENT_ID).Any(x => x == m.FATHER_ID) select m;
}
elseif (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);
}
elseif (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;
}
elseif (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;
}
elseif (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;
}
}
elseif (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);
}

}
elseif (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);
}
}
elseif (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);
}
elseif (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;

}