title: Using result of aggregate from top level group inside lower level group
body: <p>I have 2 tables A{int id,int grp}, B{int aid,int cat}.</p>
  
  <p>Table B contains list of categories that record of table A belongs to, so B.aid is Foreign Key that references A.id.</p>
  
  <p>A.id is unique primary key of table A.</p>
  
  <p>B.cat contains category number from 1 to 5, A.grp contains numbers from 1 to 1000.</p>
  
  <p>Table A has 3 million of records, table B - about 5 million.</p>
  
  <p>For each group A.grp I need to calculate % of records in A that contain B.cat out of  number of records within group A.grp.</p>
  
  <p>So if A:[{1,1},{2,1},{3,2}], B:[{1,3},{1,4},{2,3},{3,4}] then result of the query should be the following 3 column table:
  R{int grp,int cat,double percent}:[{1,3,100},{1,4,50},{2,4,100}]</p>
  
  <p>How can I do it with one single query in Linq ?</p>
  
  <p>It is desired that A to appear only once in that query because I want to be able to replace A with A.Where(e=>some complicated expression) without duplicating it many times in that single query.</p>
  
  <p>Tables A and B are imported into Linq to Entities with foreign keys so that it's possible to reference <code>from a in A from b in a.B select b.cat</code> or <code>from b in B select b.A.grp</code></p>