Linq query with optional join on table using .NET, LinqToDB & Postgresql

Question

What I want to accomplish;

A scout can create reports. The scout is the creator of the report. We display the reports that this scout has created in a table. The very same scout can be given access to a report that someone else has created. All reports that this scout has access to should be displayed in the same table.

Tech stack

.NET 7 (LinqToDB), Postgresql

Database structure:

Table 1; "Reports". Columns; Id, CreatedBy

Table 2; "ReportAccess" Columns; ReportId, ScoutId

The linq query

Here's a sample of how I can get those reports that I have been given access to;

var query = from p in db.Reports
     join c in db.ReportAccess on p.Id equals c.ReportId
     where c.ScoutId == scoutId
     select p;

and here's a sample of how I can get those reports that I have created;

var query = from p in db.Reports
     where c.CreatedBy == scoutId
     select p;

The question

How does one merge the two linq queries with an OR clause so only one query is made towards the db? I want to be able to use paging (take/skip) functionality, therefore the need of a single query.

I've tried to find examples without any luck.

I understand that I should probably start by creating an SQL query that later is transformed into linq syntax. I am not aware of how to create the SQL query.

Answer 1

You can use LEFT JOIN for such query:

var query = 
    from p in db.Reports
    from c in db.ReportAccess
        .Where(c => c.ReportId == p.Id && c.ScoutId == scoutId)
        .DefaultIfEmpty()
    where c != null || c.CreatedBy == scoutId
    select p;

Or Any operator. But ensure that execution plan is not worse:

var query = 
    from p in db.Reports
    where c.CreatedBy == scoutId || 
        db.ReportAccess.Any(c => c.ReportId == p.Id && c.ScoutId == scoutId)
    select p;