some part of your sql statement is nested too deeply rewrite the query or break it up into smaller queries

i error some part of sql statement nested rewrite query or break smaller queries  in production server whereas runs without break in development server.

please find code block used

 

   

contactslist = (from c in context.chap                                 join s in context.score on c.chapterid equals s.chapterid                                 join r in context.reg on c.chapterregionid equals r.regionid                                 join st in context.state on c.schoolstate equals st.stateid                                 s.year == year && s.achievement_level == level && c.ispublic == true                                 orderby c.number                                 select new contactdetails                                 {                                     chapter_number = c.number,                                     chapter_name = c.name,                                     chapter_school_name = c.schoolname,                                     chapter_school_addressi = c.schooladdress1,                                     chapter_school_addressii = c.schooladdress2,                                     school_city = c.schoolcity,                                     state = st.state1,                                     school_zipcode = c.schoolzipcode,                                     region_name = r.name,                                     president_first_name = (presidents) ? ((from m in context.school join ch in context.chap on m.memrecid equals ch.presmemrecid ch.number == c.number select m.firstname).firstordefault() ?? "") : "",                                     president_last_name = (presidents) ? ((from m in context.school join ch in context.chap on m.memrecid equals ch.presmemrecid ch.number == c.number select m.lastname).firstordefault() ?? "") : "",                                     president_email_id = (presidents) ? ((from m in context.school join ch in context.chap on m.memrecid equals ch.presmemrecid ch.number == c.number select m.emailaddress).firstordefault() ?? "") : "",                                     reporter_first_name = (reporters) ? ((from m in context.school join ch in context.chap on m.memrecid equals ch.rptrmemrecid ch.number == c.number select m.firstname).firstordefault() ?? "") : "",                                     reporter_last_name = (reporters) ? ((from m in context.school join ch in context.chap on m.memrecid equals ch.rptrmemrecid ch.number == c.number select m.lastname).firstordefault() ?? "") : "",                                     reporter_email_id = (reporters) ? ((from m in context.school join ch in context.chap on m.memrecid equals ch.rptrmemrecid ch.number == c.number select m.emailaddress).firstordefault() ?? "") : "",                                     treasurer_first_name = (treasurer) ? ((from m in context.school join ch in context.chap on m.memrecid equals ch.treasmemrecid ch.number == c.number select m.firstname).firstordefault() ?? "") : "",                                     treasurer_last_name = (treasurer) ? ((from m in context.school join ch in context.chap on m.memrecid equals ch.treasmemrecid ch.number == c.number select m.lastname).firstordefault() ?? "") : "",                                     treasurer_email_id = (treasurer) ? ((from m in context.school join ch in context.chap on m.memrecid equals ch.treasmemrecid ch.number == c.number select m.emailaddress).firstordefault() ?? "") : "",                                     advisor_first_name = (advisor) ? ((from con in context.conta join ch in context.chap on con.chapterid equals ch.chapterid ch.number == c.number && con.contacttypeid == 1 select con.firstname).firstordefault() ?? "") : "",                                     advisor_last_name = (advisor) ? ((from con in context.conta join ch in context.chap on con.chapterid equals ch.chapterid ch.number == c.number && con.contacttypeid == 1 select con.lastname).firstordefault() ?? "") : "",                                     advisor_email_id = (advisor) ? ((from con in context.conta join ch in context.chap on con.chapterid equals ch.chapterid ch.number == c.number && con.contacttypeid == 1 select con.emailaddress).firstordefault() ?? "") : "",                                     coadvisor_first_name = (coadvisor) ? ((from con in context.conta join ch in context.chap on con.chapterid equals ch.chapterid ch.number == c.number && con.contacttypeid == 2 select con.firstname).firstordefault() ?? "") : "",                                     coadvisor_last_name = (coadvisor) ? ((from con in context.conta join ch in context.chap on con.chapterid equals ch.chapterid ch.number == c.number && con.contacttypeid == 2 select con.lastname).firstordefault() ?? "") : "",                                     coadvisor_email_id = (coadvisor) ? ((from con in context.conta join ch in context.chap on con.chapterid equals ch.chapterid ch.number == c.number && con.contacttypeid == 2 select con.emailaddress).firstordefault() ?? "") : "",                                     dean_first_name = (dean) ? ((from con in context.conta join ch in context.chap on con.chapterid equals ch.chapterid ch.number == c.number && con.contacttypeid == 3 select con.firstname).firstordefault() ?? "") : "",                                     dean_last_name = (dean) ? ((from con in context.conta join ch in context.chap on con.chapterid equals ch.chapterid ch.number == c.number && con.contacttypeid == 3 select con.lastname).firstordefault() ?? "") : "",                                     dean_email_id = (dean) ? ((from con in context.conta join ch in context.chap on con.chapterid equals ch.chapterid ch.number == c.number && con.contacttypeid == 3 select con.emailaddress).firstordefault() ?? "") : "",                                     department_chair_first_name = (depart) ? ((from con in context.conta join ch in context.chap on con.chapterid equals ch.chapterid ch.number == c.number && con.contacttypeid == 4 select con.firstname).firstordefault() ?? "") : "",                                     department_chair_last_name = (depart) ? ((from con in context.conta join ch in context.chap on con.chapterid equals ch.chapterid ch.number == c.number && con.contacttypeid == 4 select con.lastname).firstordefault() ?? "") : "",                                     department_chair_email_id = (depart) ? ((from con in context.conta join ch in context.chap on con.chapterid equals ch.chapterid ch.number == c.number && con.contacttypeid == 4 select con.emailaddress).firstordefault() ?? "") : "",                                     achievement_level = level                                 }).tolist();

above mentioned 1 making 10 queries.
don't know what's exact reason issue.

in advance,

subash

like above mentioned 1 making 10 queries.
don't know what's exact reason issue.

it's complicated , need bust up.  whole linq query absolutely horrible. can see t-sql generated must horrible, , entity framework or linq-2-sql telling horrible.

what need make custom object/concrete class that has properties you trying project out, below.

chapter_number = c.number,
chapter_name = c.name,
chapter_school_name = c.schoolname,
chapter_school_addressi = c.schooladdress1,
chapter_school_addressii = c.schooladdress2,
school_city = c.schoolcity,
state = st.state1,
school_zipcode = c.schoolzipcode,

the above can populated president_first_name

 and rest of properties need projected out empty string values or whatever.

you go loop on return query of projected objects, , ternary , other stuff you are trying populate other properties in collection of objects you created in initial projected objects, because doing way, rest of querying doing individual queries while in loop, , not linq query total nightmare orm trying do.

just because can it, doesn't mean should, , orm ef or whatever using telling not it.

you away are trying if using entity-sql , not linq. 

Visual Studio Languages  ,  .NET Framework  >  Visual C#