樹上的芽

1.class定義

//class 定義
class Aclass
{
    public int idA { get; set; }        //流水號id
    public string name { get; set; }    //名稱
}

class Bclass
{
    public int idB { get; set; }        //流水號id
    public int nameId { get; set; }     //對應名稱id
    public string address { get; set; } //地址
}

class Cclass
{
    public int idA { get; set; }        //名稱流水號id
    public string name { get; set; }    //名稱
    public int idB { get; set; }        //地址流水號id
    public string address { get; set; } //地址
}

2.資料預設

//資料預設
List<Aclass> AA = new List<Aclass>
{
    new Aclass { idA = 1, name = "Arabela" },
    new Aclass { idA = 2, name = "Bonnie"  },
    new Aclass { idA = 3, name = "Cheryl"},
    new Aclass { idA = 4, name = "Diana"} //注意 idA = 4
};
List<Bclass> BB = new List<Bclass>
{
    new Bclass { idB = 101, nameId = 1, address = "ArabelaAddress" } ,
    new Bclass { idB = 122, nameId = 2, address = "BonnieAddress"  },
    new Bclass { idB = 311, nameId = 3, address = "CherylAddress"},
    new Bclass { idB = 341, nameId = 5, address = "EllenAddress"} //注意 nameId = 5,
};

3.語法

//單表簡易查詢 查詢AA>=2的逆排資料
//LINQ語法
    var linq_01 = from ua in AA
                    where ua.idA >= 2
                    orderby ua.idA descending
                    select new Aclass { idA = ua.idA ,name = ua.name };

//LINQ語法+lambda
    var lambda_01 = AA.Where(o => o.idA >= 2)
                    .OrderByDescending(o => o.idA)
                    .Select(o => new Aclass { idA = o.idA ,name = o.name });
//結果
	//linq_01 = lambda_01 =
	[
	  {
		"idA": 4,
		"name": "Diana"
	  },
	  {
		"idA": 3,
		"name": "Cheryl"
	  },
	  {
		"idA": 2,
		"name": "Bonnie"
	  }
	]

//inner join只有兩邊有的才有
//LINQ語法
var linq_02 = from aa in AA
                join bb in BB on aa.idA equals bb.nameId
                select new Cclass { idA = aa.idA ,name = aa.name ,idB = bb.idB ,address = bb.address };

//LINQ語法+lambda
var lambda_02 = from aa in AA
                from bb in BB
                .Where(o => o.nameId == aa.idA)
                select new Cclass() { idA = aa.idA ,name = aa.name ,idB = bb.idB ,address = bb.address };
//結果
    //linq_02 = lambda_02 =
    [
      {
        "idA": 1,
        "name": "Arabela",
        "idB": 101,
        "address": "ArabelaAddress"
      },
      {
        "idA": 2,
        "name": "Bonnie",
        "idB": 122,
        "address": "BonnieAddress"
      },
      {
        "idA": 3,
        "name": "Cheryl",
        "idB": 311,
        "address": "CherylAddress"
      }
    ]

//left join 用AA當基準沒有的給0或""
    //LEFT JOIN LINQ語法
    var linq_03 = from aa in AA
                    join bb in BB on aa.idA equals bb.nameId into aabb
                    from bb02 in aabb.DefaultIfEmpty()
                    select new Cclass
                    {
                        idA = aa.idA ,
                        name = aa.name ,
                        idB = (bb02 == null) ? 0 : bb02.idB , //必須做判斷避免為空
                        address = (bb02 == null) ? "" : bb02.address //必須做判斷避免為空
                    };

    //LEFT JOIN LINQ語法+lambda
    var lambda_03 = from aa in AA
                    from bb in BB
                            .Where(o => o.nameId == aa.idA)
                            .DefaultIfEmpty()
                    select new Cclass()
                    {
                        idA = aa.idA ,
                        name = aa.name ,
                        idB = (bb == null) ? 0 : bb.idB , //必須做判斷避免為空
                        address = (bb == null) ? "" : bb.address //必須做判斷避免為空
                    };

//結果
    //linq_03 = lambda_03 =
    [
      {
        "idA": 1,
        "name": "Arabela",
        "idB": 101,
        "address": "ArabelaAddress"
      },
      {
        "idA": 2,
        "name": "Bonnie",
        "idB": 122,
        "address": "BonnieAddress"
      },
      {
        "idA": 3,
        "name": "Cheryl",
        "idB": 311,
        "address": "CherylAddress"
      },
      {
        "idA": 4,
        "name": "Diana",
        "idB": 0,
        "address": ""
      }
    ]
 

.

我只是一棵樹