using System;

using System.Collections.Generic;

using System.Linq;

using Microsoft.EntityFrameworkCore;

using Microsoft.EntityFrameworkCore.SqlServer;

public class Program

{

    public static void Main()

    {

        using var ctx = new MyContext();

        var query = ctx.Cisterns;

        Console.WriteLine(query.ToQueryString());

        var query2 = ctx.CisternBalances;

        Console.WriteLine(query2.ToQueryString());

    }

}

​

public class MyContext : DbContext

{

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

    {

        optionsBuilder.UseSqlServer("Data Source=.");

    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)

    {

        int? ouid = 10;

        modelBuilder.Entity<Cistern>()

            .HasQueryFilter(x => x.OrganizationUnitId == ouid);

        modelBuilder.Entity<CisternBalance>()

            .HasQueryFilter(x => !ouid.HasValue || x.Cistern.OrganizationUnitId == ouid);

    }

    public DbSet<Profile> Profiles {get;set;} = null!;

    public DbSet<OrganizationUnit> OrganizationUnits {get;set;} = null!;

    public DbSet<Cistern> Cisterns {get;set;} = null!;

    public DbSet<CisternBalance> CisternBalances {get;set;} = null!;

}

​

public class UsersOrganizationUnit

{

    public int UserId {get;set;}

    public int UsersOrganizationUnitId {get;set;}

}

​

public class Profile

{

    public long Id { get; set; }

    public string Username { get; set; } = null!;

    public UsersOrganizationUnit? UsersOrganizationUnit { get; set; }

    // ...

}

​

public class OrganizationUnit

{

    public int Id { get; set; }

    public string Name { get; set; } = null!;

    public ICollection<UsersOrganizationUnit> UsersOrganizationUnits { get; set; }

    public ICollection<Cistern> Cisterns { get; set; }

}

​

public class Cistern

{

    public int Id { get; set; }

    public string Name { get; set; } = null!;

    public int OrganizationUnitId { get; set; }

    public OrganizationUnit OrganizationUnit { get; set; } = null!;

    public ICollection<CisternBalance> CisternBalances { get; set; }

}

​

public class CisternBalance

{

    public int Id { get; set; }

    public int CisternId { get; set; }

    public Cistern Cistern { get; set; } = null!;

    public decimal Balance { get; set; }

}