select average of a column from joined tables in linq

Question

I have this SQL code:

select  AVG(passing.Duration), passing.pId, p.name, zn.title 

from passing inner join zn on passing.zId = zn.zId 

inner join p on passing.pId = p.pId 

group by  passing.pId, zn.title, p.name

and I want to write it in linq format, I have written as below but I don't know how use avergae function for passing.Duration, It doesn't exist average function for p.Duration!

var pass = from p in db.passings
           join z in db.zns on p.zId equals z.zId
           join pr in db.ps on p.pId equals pr.pId
           select new {p.Duration, p.pId, pr.name, z.title};

Answer 1

Try this one:

var pass = from p in db.passings
       join z in db.zns on p.zId equals z.zId
       join pr in db.ps on p.pId equals pr.pId
       select new {p.Duration, p.pId, pr.name, z.title} into temp
       from t in temp
       group t by new{t.pId, t.name, t.title} into gr
       select new{gr.Key.pId, 
                  gr.Key.name, 
                  gr.Key.title, 
                  duration = gr.Average(c=>c.Duration)};