In the app I'm working on I have users that can have playlists. They can either have access to playlists of their own (of which they then would be owner) or playlists that are related to a team where they are a member of. My entities look as follows (Id's are Guid type, and inherited from entity AuditEntity):
public class Team : AuditEntity
{
public Team()
{
Playlists = new HashSet<Playlist>();
}
public string TeamName { get; set;}
public Guid OwnerId { get; set; }
public virtual AppUser Owner { get; set; }
public ICollection<TeamsAppUsers> Members { get; set; }
public ICollection<Playlist> Playlists { get; private set; }
}
public class Playlist : AuditEntity
{
public string PlaylistName { get; set; }
public Guid OwnerId { get; set; }
public virtual AppUser Owner { get; set; }
public Team Team { get; set; }
}
public class TeamsAppUsers
{
public Guid AppUserId { get; set; }
public AppUser Member { get; set; }
public Guid TeamId { get; set; }
public Team Team { get; set; }
}
I need all the playlists (and only once) where the user is either Owner or is a member of a team that is related to the playlist.
For just the OwnerId
I have:
var playlists = await _context.Playlists
.Where(pl => pl.OwnerId == userId)
.ToListAsync()
How can I expand this statement so that is also includes playlist where the user is a member of the related team?
CodePudding user response:
You can with advantage move the code into Your context and use EntityFramework, I allowed myself to edit Your types a few places to be able to establish the modelbuilding relation, like so:
public class PlaylistsDbContext : DbContext
{
DbSet Teams { get; set; }
DbSet Playlists { get; set; }
DbSet AppUsers { get; set; }
//Not needed
DbSet TeamAppUsers { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity().HasKey(k => k.Id);
modelBuilder.Entity().HasKey(k => k.Id);
modelBuilder.Entity().HasKey(k => k.AppUserId);
//Not needed
modelBuilder.Entity().HasKey(k => new { k.AppUserId, k.TeamId });
modelBuilder.Entity().HasMany(x => x.Playlists).WithOne(y => y.Team);
modelBuilder.Entity()
.HasOne(x => x.Owner)
.WithMany(y => y.PlayLists)
.HasPrincipalKey(k => k.AppUserId)
.HasForeignKey(f => f.OwnerId);
modelBuilder.Entity()
.HasOne(x => x.Team)
.WithMany(y => y.Playlists)
.HasPrincipalKey(p => p.Id)
.HasForeignKey(f => f.TeamId);
//These relationship tables can be omitted, as entity framework can handle that for You
//Then you can also drop the explicit foreign key fields
base.OnModelCreating(modelBuilder);
}
public List GetUserPlaylists(Guid userId)
{
return Playlists.Include(x => x.Owner).Include(y => y.Team)
.Where(z => z.Owner.AppUserId == userId
|| z.Team.Members.Any(m => m.AppUserId == userId))
.Distinct()
.ToList();
}
}