使用Linq 查詢資料 建構對象 select new{}

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
    }
  ]
}