Sql Query to Linq using joins

Question

Actually I'm trying to convert my SQL Query to Linq. My query contains 3 tables :

My query :

select ST.TEMPLATE_ID,ST.TEMPLATE_NAME,ST.CREATED_DATE
,count(distinct SQ.QUESTION_ID) as NOQ
,(
select count(*) from SURVEY_EMAIL_BLAST SEB 
where SEB.TEMPLATE_ID=ST.TEMPLATE_ID
and SEB.STATUS='Delivered'
) as [Sent]
,(
select count(*) from SURVEY_EMAIL_BLAST SEB 
where SEB.TEMPLATE_ID=ST.TEMPLATE_ID
and (SEB.EMAIL_RESULT_TEXT='Viewed' or seb.EMAIL_RESULT_TEXT='Completed')
) as [View]
,(
select count(*) from SURVEY_EMAIL_BLAST SEB 
where SEB.TEMPLATE_ID=ST.TEMPLATE_ID
and SEB.EMAIL_RESULT_TEXT='Completed'
) as [Reply]
from SURVEY_TEMPLATE ST left outer join SURVEY_QUESTION  SQ
on ST.TEMPLATE_ID=SQ.TEMPLATE_ID
group by ST.TEMPLATE_ID,ST.TEMPLATE_NAME,ST.CREATED_DATE
order by ST.TEMPLATE_ID

and My result is :

and My linq Query goes like this :

var data111 = (from st in VDC.SURVEY_TEMPLATE
               join sq in VDC.SURVEY_QUESTION on new { TEMPLATE_ID = st.TEMPLATE_ID } equals new { TEMPLATE_ID = (Int64)sq.TEMPLATE_ID } into fg
               from z in fg.DefaultIfEmpty()
               group st by new
               {
                  st.TEMPLATE_ID,
                  st.TEMPLATE_NAME,
                  st.CREATED_DATE,                                           
               } into g
               orderby
               g.Key.TEMPLATE_ID
               select new
               {
                  TEMPLATE_ID = (Int64?)g.Key.TEMPLATE_ID,
                  g.Key.TEMPLATE_NAME,
                  CREATED_DATE = (DateTime?)g.Key.CREATED_DATE,
                  Sent = (Int64?)
                  (from seb in VDC.SURVEY_EMAIL_BLAST
                   where
                  seb.SURVEY_TEMPLATE.TEMPLATE_ID == g.Key.TEMPLATE_ID &&
                    seb.STATUS == "Delivered"
                    select new
                    {
                      seb
                    }).Count(),
                  View = (Int64?)
                  (from seb in VDC.SURVEY_EMAIL_BLAST
                    where
                    seb.SURVEY_TEMPLATE.TEMPLATE_ID == g.Key.TEMPLATE_ID && (
                    seb.EMAIL_RESULT_TEXT == "Viewed" ||
                    seb.EMAIL_RESULT_TEXT == "Completed")
                    select new
                     {
                       seb
                     }).Count(),
                    Reply = (Int64?)
                     (from seb in VDC.SURVEY_EMAIL_BLAST
                       where
                       seb.SURVEY_TEMPLATE.TEMPLATE_ID == g.Key.TEMPLATE_ID &&
                       seb.EMAIL_RESULT_TEXT == "Completed"
                       select new
                        {
                          seb
                         }).Count()
                   }).ToList();

and Now My result is :

I'm Unable to display NOQ column. Can anyone help me?

---

Answer 1

First of all, add z into group by source:

           group new { st , z } by new
           {
              st.TEMPLATE_ID,
              st.TEMPLATE_NAME,
              st.CREATED_DATE,                                           
           } into g

And then use it to get NOQ in select:

NOQ = g.Select(x => x.z.QUESTION_ID).Distinct().Count()

Or just replace st with z, as you're not using st anywhere later:

           group z by new
           {
              st.TEMPLATE_ID,
              st.TEMPLATE_NAME,
              st.CREATED_DATE,                                           
           } into g

But it will require a change in select as well:

NOQ = g.Select(x => x.QUESTION_ID).Distinct().Count()

And to be honest: your query looks terrible. Are you sure the same cannot be done better?