convert sql query to LINQ statement

Question

I am struggling to translate this sql statement to a LINQ statement.

select r.FirstName + ' ' + r.Surname as [Name], count(*) as  [Number of Submissions]
from Researcher r
join ResearcherSubmission rs on r.ResearcherID = rs.ResearcherID
join Submission s on s.SubmissionID = rs.SubmissionID
group by r.FirstName, r.Surname

this is what I got so far, but I could be completely wrong as I am very new to writing LINQ statements

from r in Researcher
join rs in ResearcherSubmission on rs.ID equal s in Submission
count s in submission
group by r FirstName + ' ' + Surname

the SQL query does give me results I'm looking for.

Answer 1

Your sql query can be translated this way:

var query= from r in db.Researchers
           join rs in db.ResearcherSubmissions on r.ResearcherID equals rs.ResearcherID
           join s in db.Submissions  on s.SubmissionID equals rs.SubmissionID
           group r by new {r.FirstName, r.Surname} into g
           select new {
                       Name= g.Key.FirstName + ' ' + g.Key.Surname,
                       NumberOfSubmissions= g.Count() 
                      };

return Json(query.ToList(), JsonRequestBehavior.AllowGet);

Update

If you are using Entity Framework, this query could be easier if you use navigation properties instead joins. Seeing your sql query I notice you have a many to many relationship between Researchers and Submissions. Supposing you have a model like this:

public class Researcher
{
   public Researcher 
   {
     ResearcherSubmissions=new List<ResearcherSubmission>();
   }
   public int ResearcherId{get;set;}
   public string FirstName{get;set;}
   public string Surname{get;set;}
   public virtual ICollection<ResearcherSubmission> ResearcherSubmissions{get;set;}
}

public class ResearcherSubmission
{
    [Key, ForeignKey("Researcher"), Column(Order=1)]
    public int ResearcherId{get;set;}
    [Key, ForeignKey("Submission"), Column(Order=2)]
    public int SubmissionId {get;set;}

    public virtual Researcher Researcher{get;set;}
    public virtual Submission Submission {get;set;}
}

public class Submission
{
   public Submission
   {
     ResearcherSubmissions=new List<ResearcherSubmission>();
   }
   public int SubmissionId{get;set;}

   public virtual ICollection<ResearcherSubmission> ResearcherSubmissions{get;set;}
}

Your query would be:

 var query= db.Researchers
              .Where(r=>r.ResearcherSubmissions.Count>0)// Researchers that have submissions
              .Select(r=>new {
                               Name= r.FirstName + ' ' + r.Surname,
                               NumberOfSubmissions= r.ResearcherSubmissions.Count 
                             }
                     );
 return Json(query.ToList(), JsonRequestBehavior.AllowGet);

The solution I show above is mapping the junction table (ResearcherSubmissions), but if that table only consists of the foreign keys and no other columns, then that table can be abstracted by EF and the two sides get a navigational property exposing a collection of the other side (check this link if you want to learn more about that subject):

public class Researcher
{
   public Researcher 
   {
     Submissions=new List<Submission>();
   }
   public int ResearcherId{get;set;}
   public string FirstName{get;set;}
   public string Surname{get;set;}
   public virtual ICollection<Submission> Submissions{get;set;}
}

public class Submission
{
   public Submission
   {
     Researchers=new List<Researcher>();
   }
   public int SubmissionId{get;set;}

   public virtual ICollection<Researcher> Researchers{get;set;}
}

And your query could be this way:

 var query= db.Researchers
              .Where(r=>r.Submissions.Count>0)
              .Select(r=>new {
                               Name= r.FirstName + ' ' + r.Surname,
                               NumberOfSubmissions= r.Submissions.Count 
                             }
                     );
 return Json(query.ToList(), JsonRequestBehavior.AllowGet);