LinqでjoinとかGroup by sumとか。

まとまってないけど、とりあえずメモ。

JOIN

INNER JOIN

TableAにTableBとTableCと紐づくIDがあることが前提。

var query = from a in db.TableA
            join b in db.TableB on a.id equals b.id
            join c in db.TableC on a.id equals c.Id
            select new { name1 = a.AName, name2 = b.BName, name3 = c.CName};

OUTER JOIN

DefaultIfEmpty()を入れるとOUTER JOINになる。

var query = from a in db.TableA
            join b in db.TableB on a.id equals b.id into A
            from x in A.DefaultIfEmpty()
            join c in db.TableC on a.id equals c.id into B
            from y in B.DefaultIfEmpty()
            select new { name1 = a.AName, name2 = x.BName, name3 = y.CName };

Group By + Sum

Group byしてSum。

var query = from x in db.TableA
            group x by x.GroupName into A
            select new { A.Key, sum = A.Sum(a => (int)a.Price) };

JOINして複数キーでGroup ByしてSum

いろいろな書き方出来ますが、例えば下記のような感じ。FirstOrDefault()の使い方がポイント。

var ret = from a in TableA
          join b in TableB on a.id equals b.id
          group new { a, b } by new { a.item, b.item } into X
          orderby X.FirstOrDefault().a.code
          select new { item1 = X.FirstOrDefault().a.item, sum = X.Sum(x => x.a.status), item2 = X.FirstOrDefault().b.item };