how to write C# lamba queries more efficiently

Question

Is there any way to shorten these three lambda expressions? Right now I need three steps to get the result.

First I look up all the targets who belong to 'someone'. Then I look in the link table for all the projectIds who belong to these targets. The final lambda returns all the projects by their id.

I can't help but think that there's a more efficient way but I can't seem to find it...

public async Task<List<Project>> GetProjectsFromSomeone(string someone) {
    var targetIds = from target in Context.Targets
                     where target.Someone.ToLower().Contains(someone.ToLower())
                     select target.Id;

    var projectIds = from pt in Context.ProjectTargets
                 where targetIds.Any(id  => id == pt.TargetId)
                 select pt.ProjectId;

    var projects = from prj in Context.Projects
                where projectIds.Any(id => id == prj.Id)
                select prj;

    return await projects.ToListAsync(); 
}

public class ProjectTarget
{
    public int ProjectId { get; set; }
    public int TargetId { get; set; }

    public Project Project { get; set; }
    public Target Target { get; set; }
}

public class Target
{
    public int Id { get; set; }
    public string Code { get; set; }
    public string Someone { get; set; }
}

public class Project
{
    public int Id { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }
}

Answer 1

You can use Linq joins. Linq will take care of joins and where conditions.

public async Task<List<Project>> GetProjectsFromSomeone(string someone) 
{    
    var projects = from target in Context.Targets
                join pt in Context.ProjectTargets on target.Id equals pt.TargetId
                join prj in Context.Projects on pt.ProjectId equals prj.Id
                where target.Someone.ToLower().Contains(someone.ToLower())
                select prj;
    return await projects.ToListAsync(); 
}

Hope this helps!

Answer 2

I think you can use the join queries.

from P in Context.Projects
join PT in Context.ProjectTargets on P.Id equals PT.id
join T in Context.Targets on PT.TargetId equals T.id
  .Select(m => new
  {
      //select the fields which you want select
  });