How do I filter an entity based on another entity? Linq many-to-many in C#.

Question

This is easy to do in SQL and I'm having a very hard time achieving this using Linq to SQL. I have two entities setup, Project and ProjectsbyUser:

[Table(Name = "Projects")]
public class Project
{
     [Column(IsPrimaryKey = true, IsDbGenerated = true, Name="Job")]
     public string ProjectId { get; set; }
     [Column(Name = "Description")]
     public string Description { get; set; }        

     [Association(OtherKey = "ProjectId")]
     private EntitySet<ProjectList> _projectlist = new EntitySet<ProjectList>();
     public IEnumerable<ProjectList> ProjectList { get { return _projectlist; } }
}

[Table(Name = "ProjectLists")]
public class ProjectList
{
    [Column(IsPrimaryKey = true)]
    public string UserId { get; set; }
    [Column(IsPrimaryKey = true)]
    public string ProjectId { get; set; }
}

So in my ProjectsController I'll have this:

    public ViewResult myProject()
    {
        var query = projectsRepository.Projects
                         .Where(x => x.ProjectId == "H1000").ToList();
        return View(query.ToList());
    }

And hey, what do you know, it will show all the users attached to project H1000 with a couple of nice nested foreach statements inside my view. Easy as can be, but I'm trying to filter based on user. I can get the user easy enough (User.Identity.Name.ToString() which matches the UserId column). This has to be easy to do but I'm not figuring out a way to accomplish it without writing terrible code. This blog post explains what I want to do but it appears to use "Linq to SQL classes" template and right now my entities are sitting in nice C# class files and not in VS2008 fancy GUI designer files. There must be an elegant way to solve this, I've been working on this for about a week and think I might have run myself into a corner. Thanks for your help!

Answer 1

Does this do what you need?

projectsRepository.Projects
    .Where( x1 => x1.ProjectId == "H1000" &&
        x1.ProjectList.Any( x2 => x2.UserId == User.Identity.Name.ToString() ) )
    .ToList()

Answer 2

Side note: you are doing ToList() twice, which isn't necessary :)

Answer to your question:

Create a parent association between ProjectList and Project, so that each ProjectList item has a property called "Project" (or whatever you want to call it).

Then, just do:

string userId = User.Identity.Name.ToString();
var query = projectsRepository.ProjectLists.Where(pl => pl.UserId == userId).Select(pl => pl.Project);

...and bam, you have a list of the projects for that user.

EDIT: the other solution posted that works on the child relationship will work as well, but in my opinion this is cleaner and if you profile the query, it is actually more efficient. The other solution ends up doing expensive joins internally.

Answer 3

string userID = User.Identity.Name.ToString();
var query = from p in repository.Projects
        from pl in p.ProjectList
        where pl.UserID = userID
        select p;

Maybe this way is the most readable.

Answer 4

I think I asked the question as an unregistered user and can't seem to log in and correctly mark the best answer. For posterity Mike Maryonwski's method worked beautifully and avoided the unnecessary joins I was trying to avoid. The only code I added to the myproject entity:

[Association(ThisKey = "ProjectId")]
public Project Project { get; set; }

According to SQL Profiler it runs very efficiently, only pulling what is needed (which I guess is sort of the point but I'm still amazed it doesn't generate a messy TSQL statement). Misha's method looks like it would have also worked but I prefer the Lambda expression and style of Mike's vs the "inverted SQL" statement. It looks so wrong to me, but I've been writing TSQL forever.