linq 查询数据
/// <summary>
/// 汽车品牌及车型
/// </summary>
/// <returns></returns>
public string GetCarBrandSeries()
{
var result = from a in db.CR_BC_BRAND
join c in db.CR_BC_BRAND_SERIES on a.CR_BC_BRAND_ID equals c.CR_BC_BRAND_ID
select new
{
id = c.CR_BC_BRAND_SERIES_ID,
name = a.BRAND_NAME + c.SERIES_NAME
};
var JsonData = jsz.Serialize(result);
return JsonData;
}
/// <summary>
/// 城市信息
/// </summary>
/// <returns></returns>
public string CarInfo()
{
//linq联合查询。
var fromCityList = from a in db.CR_BC_CITY
join b in db.CR_BC_STORE on a.CR_BC_CITY_ID equals b.CR_BC_CITY_ID
join c in db.CR_BE_CAR_RENTAL on b.CR_BC_STORE_ID equals c.CR_BC_STORE_ID
join d in db.CR_BE_TAILWIND_CAR_RENTAL on c.CR_BE_CAR_RENTAL_ID equals d.CR_BE_CAR_RENTAL_ID
select a;
//linq联合查询。
var toCityList = from a in db.CR_BC_CITY
join b in db.CR_BE_TAILWIND_CAR_RENTAL on a.CR_BC_CITY_ID equals b.CR_BC_CITY_ID
select a;
var humpPinyinsList = from a in fromCityList join b in toCityList on a.CR_BC_CITY_ID equals b.CR_BC_CITY_ID select new { a.HUMP_PINYINS };
foreach (var v in humpPinyinsList)
{
//这里用到了用split分割字符串。
humpPinyins = v.HUMP_PINYINS.Split(',').ToList();
}
//将fromCityList(表) 与toCityList(表) 用Concat方法连接起来 然后用Distinct方法去除重复数据
var allCity = from a in fromCityList.Concat(toCityList).Distinct()
select new
{
capitalcity = a.CAPITAL_CITY,//是否为省会城市
code = a.CR_BC_CITY_ID,//城市ID
enName = a.EN_NAME,
flag = 1,
home = false,
humpPinyins, //城市名称缩写序列
municipality = a.MUNICIPALITY,
name = a.CITY_NAME,
newflag = false,
szm = db.CR_BC_STORE.Where(r=>r.CR_BC_CITY_ID==a.CR_BC_CITY_ID).Count()+"家门店",
top = a.ISTOP,
tour = a.ISTOUR,
};
var fromCity = from a in fromCityList.Distinct()
select new
{
capitalcity = a.CAPITAL_CITY,
code = a.CR_BC_CITY_ID,
enName = a.EN_NAME,
flag = 1,
home = false,
humpPinyins,
municipality = a.MUNICIPALITY,
name = a.CITY_NAME,
newflag = false,
opening_time = "",
szm = db.CR_BC_STORE.Where(r => r.CR_BC_CITY_ID == a.CR_BC_CITY_ID).Count() + "家门店",
top = a.ISTOP,
tour = a.ISTOUR,
};
var toCity = from a in toCityList.Distinct()
select new
{
citys = new
{
capitalcity = a.CAPITAL_CITY,
code = a.CR_BC_CITY_ID,
enName = a.EN_NAME,
flag = 1,
home = false,
humpPinyins ,
municipality = a.MUNICIPALITY,
name = a.CITY_NAME,
newflag = false,
opening_time = "",
szm = db.CR_BC_STORE.Where(r => r.CR_BC_CITY_ID == a.CR_BC_CITY_ID).Count() + "家门店",
top = a.ISTOP,
tour = a.ISTOUR,
},
fromCityId = a.CR_BC_CITY_ID,
};
var x = new { allCity, fromCity, toCity };
var jsonData = jsz.Serialize(x);
return jsonData;
}
构造出来的对象 转成js格式
{
"allCity": [
{
"capitalcity": true,
"code": 2,
"enName": "guagnzhou",
"flag": 1,
"home": false,
"humpPinyins": [
"GZ"
],
"municipality": false,
"name": "广州",
"newflag": false,
"szm": "2家门店",
"top": true,
"tour": true
},
{
"capitalcity": true,
"code": 3,
"enName": "shenzhen",
"flag": 1,
"home": false,
"humpPinyins": [
"GZ"
],
"municipality": false,
"name": "深圳",
"newflag": false,
"szm": "0家门店",
"top": true,
"tour": true
}
],
"fromCity": [
{
"capitalcity": true,
"code": 2,
"enName": "guagnzhou",
"flag": 1,
"home": false,
"humpPinyins": [
"GZ"
],
"municipality": false,
"name": "广州",
"newflag": false,
"opening_time": "",
"szm": "2家门店",
"top": true,
"tour": true
}
],
"toCity": [
{
"citys": {
"capitalcity": true,
"code": 2,
"enName": "guagnzhou",
"flag": 1,
"home": false,
"humpPinyins": [
"GZ"
],
"municipality": false,
"name": "广州",
"newflag": false,
"opening_time": "",
"szm": "2家门店",
"top": true,
"tour": true
},
"fromCityId": 2
},
{
"citys": {
"capitalcity": true,
"code": 3,
"enName": "shenzhen",
"flag": 1,
"home": false,
"humpPinyins": [
"GZ"
],
"municipality": false,
"name": "深圳",
"newflag": false,
"opening_time": "",
"szm": "0家门店",
"top": true,
"tour": true
},
"fromCityId": 3
}
]
}