c# - LINQ - multi join with Group by and get average -

i'm trying write select in linq im not successful fix long time. tried linq - join group , average doesn't work in code. i'm wrong.

sql:

select name_type, avg(t.price) avgprice type tp join location l on l.id_type = tp.id  join event e on e.id_location = l.id join ticket t on t.id_event = e.id group tp.name_type 

linq:

var q3 = l in db.location join tp in db.type on l.id_type equals tp.id join e in db.event on l.id equals u.id_location join t in db.ticket on e.id equals t.id_event  group tp new {type_name = tp.type_name} grp select new {      type_name = grp.key.type_name,      avgprice = grp.average( x => x.ticket.price) }; 

there few problems:

1. there error in second join—i believe u.id_location needs e.id_location.
2. i think grouping on wrong entity, try grouping t instead of tp.
3. you don't need anonymous type in group by.

try this:

var results =       l in db.location      join tp in db.type on l.id_type equals tp.id      join e in db.event on l.id equals e.id_location      join t in db.ticket on e.id equals t.id_event       group t new tp.type_name grp      select new      {           type_name = grp.key,           avgprice = grp.average(x => x.price)      }; 

if happen have navigation properties set between entities, lot easier. it's pretty hard tell how entities supposed related, i'm thinking work:

// average ticket price per location type var results =      t in db.ticket     group t t.event.location.type.type_name g     select new     {          type_name = g.key,          avgprice = g.average(x => x.price)     };  

or in fluent syntax:

var results = db.ticket.groupby(t => t.event.location.type.type_name)                        .select(g => new                                 {                                     type_name = g.key,                                     avgprice = g.average(x => x.price)                                 });