c# – Help with writing a Linq2sql left join query

Question:

How to write a query that will return several (2-3) top (best skills) for each applicant? There are tables:

Applicant:

public class Applicant
{
    public int ApplicantId { get; set; }        
    public string Name { get; set; }
    public DateTime BirthDate { get; set; }

    public ICollection<CurrentSkill> CurrentSkills { get; set; }   

    public Applicant()
    { 
        CurrentSkills = new List<CurrentSkill>(); 
    }
}

grade

 public class Rate
 {
    public Rate()
    {
        this.CurrentSkills = new HashSet<CurrentSkill>();
    }

    public int Id { get; set; }       
    public int RateItem { get; set; }

    public virtual ICollection<CurrentSkill> CurrentSkills { get; set; }   
}

Skill:

 public class Skill
 {
    public int Id { get; set; }
    public string Name { get; set; }
 }

There is a link table for organizing many-to-many relationships

public class CurrentSkill
{
    public int Id { get; set; }
    public Nullable<int> SkillId { get; set; }
    public Nullable<int> RateId { get; set; }

    public virtual Rate Rates { get; set; }
    public virtual Skill Skills { get; set; }

    public int? ApplicantId { get; set; }
    public Applicant Applicant { get; set; }
}

now i am getting data like this

   var applicantArray = (from i in  db.Applicants
                              .Include("CurrentSkills")   
                               select i  ).ToArray();

    // вспомагательный класс для отображения на экране             
         List<ApplicantViewModel> applModel = new List<ApplicantViewModel>();

        foreach (var applicantItem in applicantArray)
        {
            ApplicantViewModel appl = new ApplicantViewModel();
            StringBuilder topSkillName = new StringBuilder();  

            if (applicantItem.CurrentSkills.Count() > 0)
            {
                var skills = applicantItem.CurrentSkills.OrderByDescending(i => i.Rates.RateItem).Take(3);

                foreach (var skill in skills)
                {
                    topSkillName.Append(skill.Skills.Name); 
                    topSkillName.Append("/");                           
                }

            }

            appl.Id = applicantItem.ApplicantId;
            appl.AddedDate = applicantItem.AddedDate;            
            appl.FName = applicantItem.Name.FirstName;            

            appl.Skills = topSkillName.ToString();
            applModel.Add(appl);
        }

how can you get this data with one request?

Answer:

I have a feeling that your models are incomplete, they lack certain properties, but the result will be something like this:

List<ApplicantViewModel> applModel = db.Applicants.Select(x => new ApplicantViewModel
    {
        Id = x.ApplicantId,
        FName = x.Name, //Не нашел свойства в выложенной вами модели
        AddedDate = x.BirthDate, //Не нашел свойства в выложенной вами модели
        Skills = String.Join("/"
                , x.CurrentSkills
                    .OrderByDescending(y => y.Rates.RateItem)
                    .Take(3)
                    .Select(y => y.Skills.Name))
    }).ToList();

As you can see, I just used the String.Join() method to make a delimited string from the list.