Find entities not in many-to-many relation

Question

I might be looking at this the wrong way but I have a basic many-to-many code-first setup in EF Core 3.1 with Department <-> DepartmentDay <-> Day.

modelBuilder.Entity<DepartmentDay>(entity =>
{
    entity.HasKey(dd => new { dd.DepartmentId, dd.DayId });
    entity.HasOne(dp => dp.Day)
        .WithMany(p => p.DepartmentDays)
        .HasForeignKey(d => d.DayId);
    entity.HasOne(dp => dp.Department)
        .WithMany(p => p.DepartmentDays)
        .HasForeignKey(d => d.DepartmentId);
});

First question: Is this relationship optional in the way that I can have days not connected to a department? I need this since this is related to opening hours and want to have generic days that affects all departments without having to make a specific connection to all departments. but as I stated in the beginning, I might look at this the wrong way.

Second question: If question one is true and valid setup, how do I get those days not connected to a department in a Linq-query?

What I have so far is (EDIT: changed allDays from Hashset to List.)

var allDays = await _context.Days.ToListAsync();
var allDepartmentDays = _context.DepartmentDays.Select(dd => dd.DayId).ToHashSet();
var genericDays = allDays.Where(d => !allDepartmentDays.Contains(d.Id));

Or is it better to use an raw query here for performance?

SELECT Id
FROM Day
WHERE Id NOT IN (SELECT DayId FROM DepartmentDay)

Edit 2: Including the whole data model

public class Department
{
    public int Id { get; set; }
    public int DepartmentNr { get; set; }
    public string Service { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Address { get; set; }
    public string Postal { get; set; }
    public string City { get; set; }
    public string Url { get; set; }
    public string MapUrl { get; set; }
    public DateTime Created { get; set; }
    public string CreatedBy { get; set; }
    public DateTime? Updated { get; set; }
    public string UpdatedBy { get; set; }
    public ICollection<DepartmentPeriod> DepartmentPeriods { get; set; }
    public ICollection<DepartmentDay> DepartmentDays { get; set; }
}

public class DepartmentDay
{
    public int DepartmentId { get; set; }
    public int DayId { get; set; }
    public Department Department { get; set; }
    public Day.Models.Day Day { get; set; }
}

public class Day
{
    public int Id { get; set; }
    public int PeriodId { get; set; }
    public string Service { get; set; }
    public string City { get; set; }
    public DateTime? Date { get; set; }
    public DayOfWeek? DayOfWeek { get; set; }
    public DateTime? OpenTime { get; set; }
    public DateTime? CloseTime { get; set; }
    public bool IsClosed { get; set; }
    public string Description { get; set; }
    public DateTime Created { get; set; }
    public string CreatedBy { get; set; }
    public DateTime? Updated { get; set; }
    public string UpdatedBy { get; set; }

    public ICollection<DepartmentDay> DepartmentDays { get; set; }
    public virtual Period.Models.Period Period { get; set; }
}

modelBuilder.Entity<Day>(entity =>
{
    entity.HasOne(d => d.Period)
        .WithMany(p => p.Days)
        .HasForeignKey(d => d.PeriodId);          
});

There is another relation not included in the initial question which kinds of answers my first question which is Department 1-M DepartmentPeriod M-1 Period 1-1 Day. So there will be days in the Day table that has no relation to DepartmentDay but only to Period and to neither, correct?

Answer 1

First question: Is it optional? Really a tough question without your data types, but suppose you have the navigation properties correctly in place, you only need to specify the navigation properties if they keys are not named {DataType}Id, so if you name them like that it is not required, otherwise you have to specify which foreign key fields to use and which are the keys. If you do not specify relations, entity framework will generate the tables for you for the many to many relations.

Second question: In terms of performance the SQL query will always outperform if you are able to write a good query, the one you suggest or even

select a.id from [day] a left join departmentday b on a.id = b.dayid where b.dayid is null

could perform slightly better even so, However in terms of testability the sql is a problem because we'd want to run an InMemory model of our entity dbcontext and then your sql cannot execute typically at least.

So the question is if you really require that extra performance and is is possible to write a linq query that efficiently enable the linq engine to write a similar query, by making it part of the same expression instead of two or more.

In Your case, I could be missing something in the data model presently not provided, but it seems like you could get what you want like this:

_context.Days.Include(d => d.DepartmentDays).Where(!d.DepartmentDays.Any());

update: Looking at the model it seems there are some model work missing and that the projection You're looking for can be done much along the lines suggested.

Changes/ addition to model creating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Department>()
        .HasKey(k => k.Id);

    modelBuilder.Entity<DepartmentDay>()
        .HasKey(k => new { k.DayId, k.Department });

    modelBuilder.Entity<Day>()            
        .HasOne(d => d.Period)
        .WithMany(p => p.Days)
        .HasPrincipalKey(k => k.PeriodId)
        .HasForeignKey(d => d.Id);
   
    //Notice your departmentid is not alone a foreign key in the relationship table, as departments can have one row per day
    modelBuilder.Entity<DepartmentDay>()
        .HasOne(a => a.Department)
        .WithMany(b => b.DepartmentDays)
        .HasPrincipalKey(p => p.Id)
        .HasForeignKey(f => new { f.DepartmentId, f.DayId });

    base.OnModelCreating(modelBuilder);
}

and the in the context we can make such query using:

public List<Day> GetDaysWithoutAnyDepartmentDays()
{
    return Days
        .Include(i => i.DepartmentDays)
        .Where(x => !x.DepartmentDays.Any()
        ).ToList();
}