Alias from clause

Let suppose I have 2 tables

Sale

> department  sale  
  a             20 
  a             30  
  b             25

Return

> department   return 
     a           21            
     a           23 
     a           24 
     c           29

Now I want result as

Department   sale  return    
  a           50     68
  b  开发者_如何学Python         25      0
  c            0      29

What I have tried so far.

var saleData = from b in Sale.AsEnumerable()
               join a in return.AsEnumerable()
               on  b["Department"] equals a["Department"] 

               group b by b.Field<string>("Department") into g

               let list = g.ToList()
               select new
               {
                   departmentId = g.Key,
                   departmentCount = list.Count,
                   saleSum = list.Sum(x => x.Field<double>("sale")),
                   returnSum = list.Sum(x => x.Field<double>("Return"))
               };

But the above query will not work as the Return column does not belong to the list.

How do I select data from both a and b and group by Department?

Using this initialisation

DataTable Sale=new DataTable("sale");
Sale.Columns.Add("department",typeof(string));
Sale.Columns.Add("sale",typeof(double));
Sale.Rows.Add("a",20);
Sale.Rows.Add("a",30);
Sale.Rows.Add("b",25);

DataTable Return=new DataTable("Return");
Return.Columns.Add("department",typeof(string));
Return.Columns.Add("return",typeof(double));
Return.Rows.Add("a",21);
Return.Rows.Add("a",23);
Return.Rows.Add("a",24);
Return.Rows.Add("c",29);

you need the following

var salesData = from b in Sale.AsEnumerable()
                join a in Return.AsEnumerable()
                on b["department"] equals a["department"]
                group new {b,a} by b.Field<string>("department") into g
                select new {
                    departmentId=g.Key,
                    departmentCount=g.Count(),
                    saleSum=g.Sum(x=>x.b.Field<double>("sale")),
                    returnSum=g.Sum(x=>x.a.Field<double>("return"))
                };

However this code won't do the summing and full outer join you require

What you actually need is the following

// Calculate the sale totals
var SaleSums = from b in Sale.AsEnumerable()
           group b by b.Field<string>("department") into g
           select new {
                department=g.Key,
                sale=g.Sum(x=>x.Field<double>("sale"))
        };

// Calculate the return totals
var ReturnSums = from a in Return.AsEnumerable() 
           group a by a.Field<string>("department") into g
           select new {
               department=g.Key,
               Return=g.Sum(x=>x.Field<double>("return"))
        };

// do a left outer join to find all the sale totals with the matching (if present) return totals
var leftData= from b in SaleSums
                join a in ReturnSums
                on b.department equals a.department into left
                from l in left.DefaultIfEmpty()
                select new {a=l,b};

// do a left outer join to find all the return totals without the matching sale totals
// (hence the r==null check)
var rightData=  from a in ReturnSums
                join b in SaleSums
                on a.department equals b.department into right
                from r in right.DefaultIfEmpty()
                where r == null
                select new {a,b=r};


var salesData = from d in leftData.Union(rightData)         
                select new {
                    department=d.a==null ? d.b.department : d.a.department,
                    sale=d.b==null ? 0 : d.b.sale,
                    Return = d.a==null ? 0 : d.a.Return
                };