0

I'm trying to get a list of devices that have been registered to users based on a specific project.

My schema is as follows:

I have a many to many relationship between my Users and Projects table and that is done by a join table called ProjectUsers. My devices table is joined to users by a UserId field.

I need my list of devices to be returned based on the project ID but I'm having a hard time using Join<> because my ProjectUsers entity doesn't exist in my context.

Ultimately this is the query I need and when I run this in SSMS, I get the results I want:

select d.* from devices d
join users u on u.id = d.userid
join projectusers pu on pu.userid = u.id
join projects p on p.id = pu.projectid
where p.id = 7

Here are my models if this helps: Users:

public class User : IInt32Identity
{
    public int Id { get; set; }
    public string UserEmail { get; set; }
    public List<ProjectUsers> ProjectUsers { get; set; }
    ...
}

Projects:

public class Project : IInt32Identity
{
    public int Id { get; set; }
    public string ProjectName { get; set; }
    public List<ProjectUsers> ProjectUsers { get; set; }
    ...
}

ProjectUsers:

public class ProjectUsers
{
    public int UserId { get; set; }
    public User User { get; set; }
    public int ProjectId { get; set; }
    public Project Project { get; set; }
}

Devices:

public class Device : IInt32Identity
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int UserId { get; set; }
    ...
}

EDIT: Adding code that is working as two different expressions. When I combine them into a single expression (replace my "temporary" users object in the second expression with the full first expression), it does not work.

List<User> users = _context.Users
            .Include(x => x.ProjectUsers)
            .ThenInclude(b => b.Project)
            .Where(x => x.ProjectUsers
            .Any(y => y.ProjectId == id))
            .ToList();
        List<Device> devices = _context.Devices
            .Where(x => users
            .Any(l => x.UserId == l.Id))
            .ToList();
        return devices;
Blair Holmes
  • 1,521
  • 2
  • 22
  • 35
  • Is adding the ProjectUsers to your context a viable option? If not you could create a database view for this particular query. – IPValverde Jun 01 '19 at 23:07
  • What do you mean by "ProjectUsers entity doesn't exist in my context"? You have a class, you have 2 collection navigation properties referencing it, so it definitely should exist and should be mapped to a database table. If you refer to not having `DbSet` property in your context, that's a different story - it's not required, and you can always use `context.Set()` if you need access to the corresponding db set. But in this case you don't even need that because in EF we use navigation properties instead of manual joins. – Ivan Stoev Jun 02 '19 at 09:58
  • 1
    So the equivalent LINQ to Entities query of the SQL query in question would be something like `context.Devices.Where(d => d.User.ProjectUsers.Any(pu => pu.ProjectId == 7))`. See [Don’t use Linq’s Join. Navigate!](https://coding.abel.nu/2012/06/dont-use-linqs-join-navigate/). – Ivan Stoev Jun 02 '19 at 10:01
  • @IvanStoev devices doesn't contain a Users property (Just a UserId) so your expression won't quite work. I updated my question with a working solution but I"d like to consolidate that into one expression if I can. I'm really bad with lambda...I'll be the first to admit it. – Blair Holmes Jun 02 '19 at 14:21
  • 1
    I updated my model to include a full `User` property as I think that's probably the proper way to do it and your query works. If you want to make that an answer, I'll gladly accept it. – Blair Holmes Jun 02 '19 at 14:41
  • 1
    Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) could help you in the future? – NetMage Jun 03 '19 at 11:18
  • @NetMage that looks pretty comprehensive. Thanks for the resource! – Blair Holmes Jun 04 '19 at 16:26

0 Answers0