c# - Nested LINQ to get summary or survey with the best performance -

i have database contain 3 table is

• survey table
• survey question table (link survey id)
• survey question log table (link survey question id)

since survey flexible support survey many style ( data survey generate graph, analyze or predict )

that's why, want question log relate specific survey process...

so wonder best performance way query?

i not familiar linq much, small brain, can think 3 ways

---

1 nested (from inside from)

however, know bad-dest thing in normal sql, linq? choice? advantage: minimum transfer data, survey table , survey question table (1 per each) disadvantage: take query = survey x survey_question x survey_question_log times

from sv in surveys sv.project_id == 6 &&         sv.active == 1 select new {sv, sq = (from sq in survey_questions                         sq.survey_id == sv.id                         select new { sq, sql = (                                     sql in survey_question_logs                                     sql.survey_question_id == sq.id                                     select new { sql }) })} 

---

2 single query ( left join )

if in normal sql, think best way advantage: 1 query, safe database server disadvantage: alot of transfer data, alot of useless data ( alot of duplicate survey data & survey question data)

from sv in surveys  join svq in survey_questions on sv.id equals svq.survey_id ssvq subsvq in ssvq.defaultifempty()  join svql in survey_question_logs on subsvq.id equals svql.survey_question_id ssvql subsvql in ssvql.defaultifempty()  sv.project_id == 6 && sv.active == 1 select new { sv, subsvq, subsvql } 

---

3 external query (get data database , query yourself) think of query data out 3 tables, , use advantage of linq query using server cpu... considered? advantage: query 3 times (survey, survey_question, survey_question_log) disadvantage: no-idea, not sure 1 faster? database + sql? server + linq?

from sv in surveys select sv; svq in survey_questions select svq; svql in survey_question_logs select svql;  //foreach linq here //may same case 1, in server here 

---

any other case welcome!

thanks in advance

in opinion, best performance have third option.

the database server designed return data efficiently. think if make stored procedures better why stored procedures compiled , taken account in execution plain.

all linq have performance.

database + stored proedures maybe don't need use linq.

you must think in data volume manage.