Wyrażenie LINQ w relacjach many2many

Załóżmy że mamy następujące klasy:

public class Entity
{
     public int EntityID { get; set; }
     public String Name { get; set; }
     public virtual ICollection<FooBar> FooBars;
}

public class FooBar
{
     public int FooBarID { get; set; }
     public int Value { get; set; }
     public virtual ICollection<Entity> Entities;
}

EF w przypadku tych dwóch klas stworzy jeszcze dodatkową tabelę EntityFooBar (junction table) z tytułu relacji wiele do wielu. Teraz pytanie jest następujące: jak wyglądałoby zapytanie LINQ (w postaci rozszerzeniowej, Where(), Join() itp.) które w wyniku zwracałoby listę w postaci:

EntityID | FooBarID
1        | 1
1        | 2
1        | 3
2        | 3
2        | 4

Czyli w skrócie: listę możliwych kombinacji Entity z FooBarem. Największą trudność sprawia mi zrobienie odpowiedniego joina, ponieważ nie bardzo wiem jak on mógłby wyglądać w przypadku kolekcji a nie pojedynczych ID:

context.Entity.Join(ctx.FooBar, p => p.EntityID, q => q.Entities.?, ...

Można próbować też w drugą stronę, tj.:

context.Entity.SelectMany(p => p.FooBars).?

Z tym że nadal mamy tylko listę FooBarów, bez informacji o powiązanych z nimi Entities.

No to tak: mamy taką bazę:

Uzupełniamy tabele, specjalnie nie wpisując wszystkich możliwości:

insert entity(name) values ("Grzegorz");
insert entity(name) values ("Piotrek");
insert entity(name) values ("Maciek");

insert foobar(name) values ("Programista");
insert foobar(name) values ("Spawacz");
insert foobar(name) values ("Krętacz");

insert foobar_has_entity(foobar_id, entity_id)
values
(2,1),
(2,3);

Robimy testowe zapytanie Full join. Jako, że to mysql muszę posłużyć się zamiennikiem:

select e.id as EntityId, e.Name as EntityName, fhs.Entity_id, fhs.Foobar_id, f.Id as FoobarId, f.Name as FoobarName from entity e
right join foobar_has_entity fhs on e.id = fhs.entity_id
right join foobar f on fhs.foobar_id = f.id
union 
select e.id as EntityId, e.Name as EntityName, fhs.Entity_id, fhs.Foobar_id, f.Id as FoobarId, f.Name as FoobarName from entity e
left join foobar_has_entity fhs on e.id = fhs.entity_id
left join foobar f on fhs.foobar_id = f.id;

Oto wyniki:

To teraz Entity. Niestety nie uświadczymy tutaj obsługi full joina więc trzeba posłużyć się unią analogiczną do tego co wyżej w sqlu:

using (var db = new FoobarEntities())
{
    var foo = (from e in db.Entities
                from f in e.Foobars.DefaultIfEmpty()
                select new
                {
                    EntityId = e.Id,
                    EntityName = e.Name,
                    FoobarId = f == null ? -1 : f.Id,
                    FoobarName = f == null ? "" : f.Name
                }).Union(from f in db.Foobars
                        from e in f.Entities.DefaultIfEmpty()
                        select new
                        {
                            EntityId = e == null ? -1 : e.Id,
                            EntityName = e == null ? "" : e.Name,
                            FoobarId = f.Id,
                            FoobarName = f.Name
                        });

    foreach (var f in foo.ToList())
    {
        Console.WriteLine("Entity: {0} {1}, Foobar: {2} {3}", f.EntityId, f.EntityName, f.FoobarId, f.FoobarName);
    }
}

Wynik jest taki sam:

Entity: 1 Grzegorz, Foobar: 2 Spawacz
Entity: 2 Piotrek, Foobar: -1
Entity: 3 Maciek, Foobar: 2 Spawacz
Entity: -1 , Foobar: 1 Programista
Entity: -1 , Foobar: 3 Krętacz

Joinów raczej robić nie musisz, ponieważ spokojnie ORM przykryje relacje. Niestety EF wygeneruje pod spodem małego potworka, jednakże nie jest tak straszny jak się spodziewać można i nawet zbliżony do tego zapytania SQL, które napisałem: plus dwie projekcje, którymi EF raczył obdarować naszego selecta ;)

Świetne, wielkie dzięki za tak obszerne rozpisanie! Generalnie rzecz biorąc, do tej pory zawsze pisałem w LINQ Method Syntax ale z tego co widzę to czas powoli się przestawić w przypadku bardziej skomplikowanych zapytań :)

Method syntax potrafi być bardzo upierdliwe jeżeli chodzi o coś bardziej skomplikowanego, a przecież ORM jest w końcu po to żeby ułatwiać życie. Przynajmniej dla mnie. :)

Wszystko rozbija się właśnie o to, że nie ma/nie widziałem prostego sposobu na takie sprytne zrobienie dwóch "from" i sprawne manipulowanie na nich z poziomu metod. Zwłaszcza, gdy nie mamy jeszcze dostępu do tabeli pośredniej między Entity a Foobar. Zresztą nawet jakby był, to pewnie zrobiłoby się gęsto od joinów. No ale cóż, nie można mieć wszystkiego :)

Jeżeli jest to tabela łącząca jedynie many to many to EF obsługuje ją sobie pod spodem, fakt. Musiałbyś mieć w niej coś więcej niż tylko dwie kolumny z id'kami, żeby była widoczna.Czy zrobiłoby się gęsto od joinów? Nie sądzę. Może jeszcze jedna warstwa from by doszła ale pod spodem generalnie chodziłoby o to samo więc sam SQL wygenerowany przez EF byłoby pewnie bardzo zbliżony, jeśli nawet nie taki sam. ;)