CODEWITHSUNDEEP
c#asp.netlinq
Darren
Darren

Reputation: 11021

How to use LINQ to only select if no results exist in related table?

I have my tables: aspnet_UsersInRoles aspnet_Role aspnet_Users ApiUsers

I want to select only users that have the role "ProductOwner" and that currently do not have an entry in ApiUsers. The reason I am using a hasMany table right now is the schema might change and this makes it easy to adjust. Here is my current query:

    ddNewApiUserParentUser.DataSource = from u in dbc.aspnet_UsersInRoles
                                        where u.aspnet_Role.RoleName == "ProductOwner"
                                        select new { u.aspnet_User.UserId, u.aspnet_User.UserName };

This gets all users with the role ProductOwner however still gets them if there is already at least one row in ApiUser. All the foreign keys are connected through UserId. How would I only get them with having a role name of ProductOwner if they have a 0 count in ApiUser (relation through UserId)?

Thanks so much, Darren

Upvotes: 0

Views: 166

Answers (1)

Adam Rackis
Adam Rackis

Reputation: 83358

I think you want something like this:

ddNewApiUserParentUser.DataSource = 
    from u in dbc.aspnet_UsersInRoles
    where u.aspnet_Role.RoleName == "ProductOwner" &&
        !u.aspnet_User.ApiUsers.Any()
    select new { u.aspnet_User.UserId, u.aspnet_User.UserName };

Upvotes: 1

Related Questions