EF C# lambda wiele do wiele

Od wczoraj męczy mnie pewne proste zagadnienie, mianowicie chciałbym pobrać z bazy danych obiekt, który jest reprezentowany w relacji wiele do wiele. Łącznie mam trzy tabelki, Delegation, DelegationToPerson oraz Person. W wyniku mojego zapytania oczekuję jednego obiektu Delegation (o konkretnym id_delegation), który będzie w sobie zawierał listę obiektów DelegationToPerson.

Moje zapytanie wygląda następująco:

db.Delegation.Include(dtp => dtp.DelegationToPerson.Select(s => s.Person)).FirstOrDefault(del => del.id == delegationId);

Mój problem polega na tym, że osoba mogła zostać usunięta z delegacji, tzn w tabeli korelacyjnej ma postawioną flagę is_deleted i właśnie tak chciałbym przefiltrować obiekt DelegateToPerson. W TSQL wygląda to następująco:

SELECT * 
FROM Delegation AS d
INNER JOIN DelegationToPerson AS dtp on dtp.id_delegation = d.id
INNER JOIN Person AS p on dtp.id_person = p.id
WHERE 
	p.is_deleted = 0 AND 
	dtp.is_deleted = 0 AND
	dtp.id_delegation = 55

Dodatkowo, zdałem sobie również sprawę, że oprócz usuniętych powiązań użytkownika oraz delegacji muszę też odfiltrować ewentualnie usuniętych użytkowników (Person -> is_deleted).

Z góry dziękuję za pomoc!

Odwróciłem kolejność.

db.DelegationToPerson.Include(dtp => dtp.Delegation).Include(dtp => dtp.Person).Where(dtp => !dtp.is_deleted && !dtp.Person.is_deleted && dtp.id_delegation == delegationId).ToList();

Potem w widoku:

@model IEnumerable<inEwi.Models.DelegationToPerson>

@{
    var delegation = Model.First().Delegation;
}

@foreach (var dtp in Model)
{
   ...
}

Nie podoba mi się to rozwiązanie, da się to zrobić tak jak docelowo chciałem, czyli przez obiekt Delegation?

Pokaż model.

    public partial class Delegation
    {
        public Delegation()
        {
            this.DelegationToPerson = new HashSet<DelegationToPerson>();
        }
    
        public int id { get; set; }
        public string name { get; set; }
        public System.DateTime start_date { get; set; }
        public System.DateTime end_date { get; set; }
        
        public bool is_deleted { get; set; }
        public int id_organization { get; set; }
    
        public virtual Organization Organization { get; set; }
        public virtual ICollection<DelegationToPerson> DelegationToPerson { get; set; }
    }

    public partial class DelegationToPerson
    {
        public int id { get; set; }
        public Nullable<System.TimeSpan> duration { get; set; }
        public Nullable<System.DateTime> start_date { get; set; }
        public Nullable<System.DateTime> end_date { get; set; }
        
        public int id_person { get; set; }
        public int id_delegation { get; set; }
        public bool is_deleted { get; set; }
    
        public virtual Delegation Delegation { get; set; }
        public virtual Person Person { get; set; }
    }

        public Person()
        {
            this.DelegationToPerson = new HashSet<DelegationToPerson>();
        }
    
        public int id { get; set; }
        public int id_organization { get; set; }
        public string firstname { get; set; }
        public string surname { get; set; }

        public Nullable<int> id_location { get; set; }
    
        public virtual ICollection<DelegationToPerson> DelegationToPerson { get; set; }
    }

Wszelkie niespójności mogą wynikać z tego, że okroiłem model na potrzeby tego posta. Tabelka korelacyjna nadpisuje pola, personalizując użytkownikowi delegację.

Coś takiego działa?

db.Delegation.Where(d=> !d.is_deleted)
    .Include(dtp => dtp.DelegationToPerson
        .Where(d=> !d.is_deleted)
        .Select(s => s.Person))
    .FirstOrDefault(del => del.id == delegationId);

Niestety nie, też wydaje mi się logiczne takie podejście, ale nie da się do include dokleić where.. Nie mam jak sprawdzić tego na 100% bo już zmieniłem logikę, ale próbowałem tego jeszcze dzisiaj.

To i to nie działa.

najprościej jeżeli w tym przypadku na bazie danych byłby dostępny widok który wyświetla tylko nieusunięte rekordy
logika aplikacji się znacznie uprości i będzie estetyczniej

Zgadzam się z Tobą. Jest to jedno z rozwiązań, równie dobrze można zrobić zapytanie przez czystego TSQL, lub procedurę, ale zależy mi na rozwiązaniu przez linq. Da się tak zrobić?

Nie da się tego zrobić przez linq?

Próbuje raz jeszcze i odpuszczam. Proszę o pomoc :)