How do I do a left/right join using DbSet

Question

I have the following 3 models:

class Owner
{
    public Guid Id { get; set; }
    public IList<AccessShare> AccessShares { get; set; } = new List<AccessShare>()
}

class AccessShare
{
    public Guid OwnerId { get; set; }
    public Guid AccessorId { get; set; }
}

class File
{
    public Guid OwnerId { get; set; }
    public Owner Owner { get; set; }

    public string Filename { get; set; }
}

The purpose of the system is assign ownership over files to one user, and to only allow other users to see those entities when there is an AccessShare from Accessor to Owner.

I have the following code that I'm using to bring back a file:

Guid fileId = <code that gets the accessor id>
Guid accessorId = <code that gets the accessor id>

File? file = DBContext
    .Set<File>()
    .Where(e => e.Id == fileId)
    .Where(e => e.Owner.Id == accessorId ||  e.Owner.AccessShares.Any(a => a.AccessorId == accessorId))
    .Include(e => e.Owner)
    .Include(e => e.Owner.AccessShares)
    .FirstOrDefault();

The issue I'm getting is that if null is returned, I don't know it that's because there isn't a File entity with the given id, or if there isn't an access share that allows access.

If this was raw SQL I'd do a left join from Owners to AccessShares with the above condition, this would always give me back the file/owner if found, and then optionally any access shares that meet the criteria.

I can find examples of how to do it in SQL and in Linq, but I can't find any examples using the DbSet fluid style.

Feels like you are looking for something like a GroupJoin? https://stackoverflow.com/a/584840/125981 (to be fair I spent very little time before saying this) — Mark Schultheiss, Jan 04 '23 at 19:04

score 0 · Answer 1 · answered Jan 04 '23 at 21:16

From what you describe the part you are having issue with is:

.Where(e => e.Owner.Id == accessorId ||  e.Owner.AccessShares.Any(a => a.AccessorId == accessorId))

This will only return the desired file If the file is owned by the user, or has an access share.

Now if you want to return a File if it exists and provide an indication to the user whether they have permissions to access it, that could be done via projection rather than returning entities:

For example if I have a File ViewModel/DTO:

public class FileViewModel
{
    public int FileId { get; set; }
    // any other fields about the file I might display/use...

    public bool UserHasAccess { get; set; }
    public bool UserIsOwner { get; set; }
}

then I can query and populate these computed values via EF:

FileViewModel? file = DBContext
    .Set<File>()
    .Where(e => e.Id == fileId)
    .Select(e => new FileViewModel
    {
        FileId = e.FileId,
        // other fields...
        UserHasAccess = e.OwnerId == accessorId || e.Owner.AccessShares.Any(a => a.AccessorId == accessorId),
        UserIsOwner = e.OwnerId == accessorId
    }).SingleOrDefault();

This will return a file if it exists, and includes details that your view/logic can use to determine if the file can be accessed by the current user, and/or is owned by the current user. Note that we remove the extra Where condition, and we don't need Include to access those related members when projecting.

Alternatively computed properties like this can be added to the File entity to do the same thing, however for these properties to function would require that the Owner and AccessShares are eager loaded (/w Include) or can be lazy loaded, accepting the potential performance pitfalls that can come with that. IMHO Projection /w Select is almost always preferable as it can also improve the resource footprint and performance of the querying.

How do I do a left/right join using DbSet

1 Answers1