public void linqInnerJoinExample() { var query = from product in products join product_ProductTypeRelationship in product_ProductTypeRelationships on product.Id equals product_ProductTypeRelationship.ProductID join productType in productTypes on product_ProductTypeRelationship.ProductTypeID equals productType.Id select new { product.Id, product.Name, product.Price, productTypeName = productType.Name }; var result = query.ToList(); }
結果:
說明:因為是InnerJoin，都要有重疊才會關聯出，而XBOX Series X的Id沒有在product_ProductTypeRelationships裡面，所以最後沒有被顯示出來。 基本上跟原生SQL的寫法挺像的。
public void linqLeftJoinExample() { // product 跟 product_ProductTypeRelationship 先left join var query1 = from product in products join product_ProductTypeRelationship in product_ProductTypeRelationships on product.Id equals product_ProductTypeRelationship.ProductID into tmp from product_ProductTypeRelationship in tmp.DefaultIfEmpty() select new { product.Id, product.Name, product.Price, ProductTypeID = product_ProductTypeRelationship?.ProductTypeID };
// query1結果再跟 productTypes left join var query2 = from q in query1 join productType in productTypes on q.ProductTypeID equals productType.Id into tmp from product_ProductTypeRelationship in tmp.DefaultIfEmpty() select new { q.Id, q.Name, q.Price, ProductName = product_ProductTypeRelationship?.Name }; var result = query2.ToList(); }
結果:
說明:可以得到XBOX Series X了。 因為LINQ沒有LEFT JOIN這種簡單作法，只能靠DefaultIfEmpty來操作，作法比較複雜、難直覺判斷。 ((ps.不是要用來讓大家好用的查詢嗎?搞複雜了
作法2:組多個DefaultIfEmpty
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
public void linqLeftJoinExample2() { var query = from a in products join b in product_ProductTypeRelationships on a.Id equals b.ProductID into result1 from ab in result1.DefaultIfEmpty() join c in productTypes on ab?.ProductTypeID equals c.Id into result2 from abc in result2.DefaultIfEmpty() select new{ a.Id, a.Name, a.Price, ProductName = abc?.Name } ; var result = query.ToList(); }
結果:
說明:可以得到XBOX Series X了。 這種多個DefaultIfEmpty組法感覺蠻難讀的。 要獨立出一個值，導致得用a、b、c來組不然難知道誰是誰，就很難閱讀 且要小心有些值讀出來是空的要使用「?.」的做法，把empty的值設定為null才有辦法繼續下去，否則會執行到中間就報錯了。
var p = (from a in products select new ProductViewModel { Id = a.Id, Name = a.Name, Price = a.Price, }).ToList();
foreach (var item in p) { var productTypeIDs = from a in product_ProductTypeRelationships where a.ProductID == item.Id select a.ProductTypeID;
var productTypeData = from a in productTypeIDs join b in productTypes on a equals b.Id select new ProductType { Id = b.Id, Name = b.Name }; item.ProductTypes = new List<ProductType>(); foreach (var x in productTypeData) { item.ProductTypes.Add(x); } } var result = p; string jsonData = JsonConvert.SerializeObject(result); System.Diagnostics.Debug.WriteLine(jsonData); }
