結合サンプル

SQLCompiler for LINQ(C#) のサンプル (LINQPad 用 LINQ to Entities 結合サンプル ) 本サンプルで使用した Visual Studio プロジェクトの pubs データベースの概念モデルは 以下のテーブル名とカラム名が 直接 SQL Sever へクエリする場合と異なるので が補正されています テーブル名が異なるもの employee employees royshed roysheds titleauthor titleauthors カラム名が異なるもの discounts.discount discounts.discount1 titles.title titles.title1 Northwind データベースについては 本サンプルで使用しているテーブル名とカラム名は 概念モデルと一致しています < 一覧表 > 説明リンク InnerJoin(S1-1) 内部結合 ( 等結合 1 個 ) + 内部結合 ( 等結合 1 個 ) Inner(S1-1) InnerJoin(S2-1) クロス結合 + 内部結合 ( 等結合が AND で 2 個 ) Inner(S2-1) InnerJoin(S3-1) 内部結合 ( 等結合が AND で 2 個 ) Inner(S3-1) InnerJoin(S4-1) 内部結合 ( 等結合と不等結合を AND) Inner(S4-1) InnerJoin(S5-1) 内部結合 ( 等結合 1 個 ), グループ化あり Inner(S5-1) LeftJoin(S1-1) 左外部結合 ( 等結合 1 個 ) Left(S1-1) LeftJoin(S2-1) 左外部結合 ( 等結合 1 個 ) + 左外部結合 ( 等結合 1 個 ) Left(S2-1) LeftJoin(S3-1) 内部結合 ( 等結合 1 個 ) + 左外部結合 ( 等結合 1 個 ) Left(S3-1) LeftJoin(S4-1) 左外部結合 ( 等結合 1 個 ) + 内部結合 ( 等結合 1 個 ) Left(S4-1) LeftJoin(S8-1) 左外部結合 ( 等結合 2 個 ) Left(S8-1) RightJoin(S1-1) 右外部結合 ( 等結合 1 個 ) Right(S1-1) FullJoin(S1-1) 完全外部結合 ( 等結合 1 個 ) Full(S1-1) InnerJoin(S1-1).linq 説明内部結合 ( 等結合 1 個 ) + 内部結合 ( 等結合 1 個 ) SELECT e.emp_id, e.job_id, j.job_desc, e.pub_id, p.pub_name FROM employees AS e INNER JOIN publishers AS p ON e.pub_id = p.pub_id INNER JOIN jobs AS j ON e.job_id = j.job_id 1

ORDER BY e.emp_id employees.join(publishers, e => e.pub_id, (e, p) => newe, p.join(jobs, x1 => x1.e.job_id, j => j.job_id, (x1, j) => newx1, j.where(x2 => x2.x1.e.job_id > 10) x2.x1.e.emp_id, x2.x1.e.job_id, x2.j.job_desc, x2.x1.e.pub_id, x2.x1.p.pub_name.orderby(t => t.emp_id); InnerJoin(S2-1).linq 説明クロス結合 + 内部結合 ( 等結合が AND で 2 個 ) SELECT e.emp_id, e.job_id, j.job_desc, e.pub_id, p.pub_name FROM publishers AS p CROSS JOIN jobs AS j INNER JOIN employees AS e ON p.pub_id = e.pub_id AND j.job_id = e.job_id ORDER BY e.emp_id publishers.selectmany(j => jobs, (p, j) => newp, j.join(employees, x1 => newx1.p.pub_id, x1.j.job_id}, e => newe.pub_id, e.job_id}, (x1, e) => newx1, e.where(x2 => x2.e.job_id > 10) x2.e.emp_id, x2.e.job_id, x2.x1.j.job_desc, x2.e.pub_id, x2.x1.p.pub_name.orderby(t => t.emp_id); 2

InnerJoin(S3-1).linq 説明内部結合 ( 等結合が AND で 2 個 ) SELECT t.title_id, r.royalty FROM titles AS t INNER JOIN royscheds AS r ON t.title_id = r.title_id AND t.royalty = r.royalty titles.join(royscheds, t => newt.title_id, t.royalty}, r => newr.title_id, r.royalty}, (t, r) => newt, r x1.t.title_id, x1.r.royalty ; 説明 InnerJoin(S4-1).linq 内部結合 ( 等結合と不等結合を AND) SELECT t.title_id, t.royalty, r.royalty FROM titles AS t INNER JOIN royscheds AS r ON t.title_id = r.title_id AND t.royalty <> r.royalty WHERE r.title_id = 'BU1032' OR r.title_id = 'PC1035' titles.selectmany(r => royscheds, (t, r) => newt, r.where(x1 => x1.t.title_id == x1.r.title_id && x1.t.royalty!= x1.r.royalty).where(x1 => x1.r.title_id == "BU1032" x1.r.title_id == "PC1035") x1.t.title_id, root_alias_2 = x1.t.royalty, root_alias_3 = x1.r.royalty ; 説明 InnerJoin(S5-1).linq 内部結合 ( 等結合 1 個 ), グループ化あり SELECT e.pub_id, COUNT(*) AS num, MAX(e.job_lvl) AS maxlvl FROM employees AS e INNER JOIN jobs AS j 3

ON e.job_id = j.job_id GROUP BY e.pub_id HAVING COUNT(*) > 1 ORDER BY e.pub_id employees.join(jobs, e => e.job_id, j => j.job_id, (e, j) => newe, j.where(x1 => x1.e.job_id > 10).GroupBy(x1 => newgrkey1 = x1.e.pub_id.where(g1 => g1.count() > 1).Select(g1 => new pub_id = g1.key.grkey1, num = g1.count(), maxlvl = g1.max(x1 => x1.e.job_lvl).orderby(t => t.pub_id); LeftJoin(S1-1).linq 説明左外部結合 ( 等結合 1 個 ) SELECT t.title_id, t.title1, t.price, p.pub_name FROM titles AS t LEFT OUTER JOIN publishers AS p ON t.pub_id = p.pub_id titles.groupjoin(publishers, t => t.pub_id, (t, ig1) => newt, ig1 (o1, i1) => newo1, i1 x1.o1.t.title_id, x1.o1.t.title1, x1.o1.t.price, pub_name = (x1.i1.pub_name!= null? x1.i1.pub_name : null) ; LeftJoin(S2-1).linq 説明左外部結合 ( 等結合 1 個 ) + 左外部結合 ( 等結合 1 個 ) 4

SELECT t.title_id, t.title1, t.price, p.pub_name, ta.au_id FROM titles AS t LEFT OUTER JOIN publishers AS p ON t.pub_id = p.pub_id LEFT OUTER JOIN titleauthors AS ta ON t.title_id = ta.title_id titles.groupjoin(publishers, t => t.pub_id, (t, ig1) => newt, ig1 (o1, i1) => newo1, i1.groupjoin(titleauthors, x1 => x1.o1.t.title_id, ta => ta.title_id, (x1, ig2) => newx1, ig2.selectmany(gj => gj.ig2.defaultifempty(), (o2, i2) => newo2, i2 x2.o2.x1.o1.t.title_id, x2.o2.x1.o1.t.title1, x2.o2.x1.o1.t.price, pub_name = (x2.o2.x1.i1.pub_name!= null? x2.o2.x1.i1.pub_name : null), au_id = (x2.i2.au_id!= null? x2.i2.au_id : null) ; LeftJoin(S3-1).linq 説明内部結合 ( 等結合 1 個 ) + 左外部結合 ( 等結合 1 個 ) SELECT e.emp_id, e.job_id, j.job_desc, e.pub_id, p.pub_name FROM employees AS e INNER JOIN publishers AS p ON e.pub_id = p.pub_id LEFT OUTER JOIN jobs AS j ON e.job_id = j.job_id ORDER BY e.emp_id employees.join(publishers, e => e.pub_id, (e, p) => newe, p.groupjoin(jobs, x1 => x1.e.job_id, j => j.job_id, (x1, ig1) => newx1, ig1 (o1, i1) => newo1, i1 5

.Where(x2 => x2.o1.x1.e.job_id > 10) x2.o1.x1.e.emp_id, x2.o1.x1.e.job_id, job_desc = (x2.i1.job_desc!= null? x2.i1.job_desc : null), x2.o1.x1.e.pub_id, x2.o1.x1.p.pub_name.orderby(t => t.emp_id); LeftJoin(S4-1).linq 説明左外部結合 ( 等結合 1 個 ) + 内部結合 ( 等結合 1 個 ) SELECT e.emp_id, e.job_id, j.job_desc, e.pub_id, p.pub_name FROM employees AS e LEFT OUTER JOIN publishers AS p ON e.pub_id = p.pub_id INNER JOIN jobs AS j ON e.job_id = j.job_id ORDER BY e.emp_id employees.groupjoin(publishers, e => e.pub_id, (e, ig1) => newe, ig1 (o1, i1) => newo1, i1.join(jobs, x1 => x1.o1.e.job_id, j => j.job_id, (x1, j) => newx1, j.where(x2 => x2.x1.o1.e.job_id > 10) x2.x1.o1.e.emp_id, x2.x1.o1.e.job_id, x2.j.job_desc, x2.x1.o1.e.pub_id, pub_name = (x2.x1.i1.pub_name!= null? x2.x1.i1.pub_name : null).orderby(t => t.emp_id); LeftJoin(S8-1).linq 説明 左外部結合 ( 等結合 2 個 ) SELECT p.pub_id, p.pub_name, t.title_id, t.title1 6

FROM publishers AS p LEFT OUTER JOIN titles AS t ON p.pub_id = t.pub_id AND t.type = 'business' publishers.groupjoin(titles, p => newp.pub_id, key1 = "business"}, t => newt.pub_id, key1 = t.type}, (p, ig1) => newp, ig1 (o1, i1) => newo1, i1 x1.o1.p.pub_id, x1.o1.p.pub_name, title_id = (x1.i1.title_id!= null? x1.i1.title_id : null), title1 = (x1.i1.title1!= null? x1.i1.title1 : null) ; RightJoin(S1-1).linq 説明右外部結合 ( 等結合 1 個 ) SELECT t.title_id, t.title1, p.pub_id, p.pub_name FROM titles AS t RIGHT OUTER JOIN publishers AS p ON t.pub_id = p.pub_id ORDER BY t.title_id publishers.groupjoin(titles, t => t.pub_id, (p, ig1) => newp, ig1 (o1, i1) => newo1, i1 title_id = (x1.i1title_id!= null? x1.i1.title_id : null), title1 = (x1.i1.title1!= null? x1.i1.title1 : null), x1.o1.p.pub_id, x1.o1.p.pub_name.orderby(t => t.title_id); FullJoin(S1-1) 説明 完全外部結合 ( 等結合 1 個 ) SELECT s.stor_id, s.state, p.pub_id, p.state 7

FROM stores AS s FULL OUTER JOIN publishers AS p ON s.state = p.state ORDER BY s.stor_id stores.groupjoin(publishers, s => s.state, p => p.state, (s, ig1) => news, ig1 (o1, i1) => newo1, i1 x1.o1.s.stor_id, root_alias_2 = x1.o1.s.state, pub_id = (x1.i1.pub_id!= null? x1.i1.pub_id : null), root_alias_4 = (x1.i1.state!= null? x1.i1.state : null).union(publishers.groupjoin(stores, p => p.state, s => s.state, (p, ig2) => newp, ig2.selectmany(gj => gj.ig2.defaultifempty(), (o2, i2) => newo2, i2 stor_id = (x2.i2.stor_id!= null? x2.i2.stor_id : null), root_alias_2 = (x2.i2.state!= null? x2.i2.state : null), x2.o2.p.pub_id, root_alias_4 = x2.o2.p.state ).OrderBy(t => t.stor_id); 8