LINQ-Abfrage über drei Tabellen

[Smart]

Hallo Forum,

ich brauche wieder mal eure Hilfe bei einer LINQ-Abfrage.
Ich habe da 3 Tabellen wie folgt. Ich benenne nur die relevanten Spalten:

Tabelle A:
AID
1
2
3
4

Tabelle B: (Verknüpft mit Tabelle A durch AID)
BID | AID | BName
1 | 1 | a
2 | 1 | b
3 | 1 | c
4 | 2 | d
5 | 2 | f
6 | 3 | g
7 | 4 | h
8 | 3 | i

Tabelle C: (Verknüpft mit Tabelle B durch BID)
CID | BID | CName
1 | 1 | aa
2 | 1 | bb
3 | 2 | cc
4 | 3 | dd
5 | 3 | ee
6 | 1 | ff
7 | 4 | ff
8 | 4 | ff
9 | 5 | ff

Ich brauche nun diese Anzeige:
A_1 hat 3XB und 6XC (BID -> 1, 2, 3 und CID -> 1, 2, 3, 4, 5, 6)
A_2 hat 2XB und 3XC (BID -> 4, 5 und CID -> 7, 8, 9)
A_3 hat 2XB und 0XC (BID -> 6, 8)
A_4 hat 1XB und 0XC (BID -> 7)

Ich habe bereits versucht über diese LINQ-Anweisung das Ergebnis zu bekommen, es gelingt mir aber nicht:

from b in B
join a in A on b.AID equals a.AID
join c in C on b.BID equals c.BID
group b by new { a.AID, b.AID, c.BID } into grp
select new PlantDeviceViewModel
{
  myAID = grp.Key.AID,
  AnzahlB = grp.Count(),
  ANZAHLC = grp.Select(x => x.CID).Distinct().Count()
};

Hat jemand eventuell eine Idee, wie ich das Ergebnis wie oben beschriebn erhalten kann? Ist das überhaupt mit nur einer Abfrage zu schaffen?

Danke im Voraus

[akretschmer]

Hat jemand eventuell eine Idee, wie ich das Ergebnis wie oben beschriebn erhalten kann? Ist das überhaupt mit nur einer Abfrage zu schaffen?

Danke im Voraus

Ich denk schon, daß das geht. Ich hab es mal in meiner Lieblings-DB (PostgreSQL) versucht, rein aus sportlichem Ehrgeiz:

test=*# select a.aid, string_agg(distinct b.bid::text,',') as b, string_agg(distinct c.cid::text,',') as c from a left join b on a.aid=b.aid left join c on b.bid=c.bid group by a.aid;
 aid |   b   |      c
-----+-------+-------------
   1 | 1,2,3 | 1,2,3,4,5,6
   2 | 4,5   | 7,8,9
   3 | 6,8   |
   4 | 7     |
(4 rows)

Da jetzt noch die Elemente zu zählen ist dann eher trivial.

[Smart]

Hi,

vielen Dank für deine Bemühung.
Ich suchte eigentlich eine LINQ-Lösung. Die Schreibsweis 1,2,3,4,5,6 sollte nur als Erklärung dienen und nicht so ausgegeben werden. Die Ausgabe sollte z.B. so erfolgen:

ID1 von Tabelle A: 3 X B und 6 X C oder 1 | 3 | 6
ID2 von Tabelle A: 2 X B und 3 X C oder 2 | 2 | 3

Gruß

[akretschmer]

Hi,

vielen Dank für deine Bemühung.
Ich suchte eigentlich eine LINQ-Lösung. Die Schreibsweis 1,2,3,4,5,6 sollte nur als Erklärung dienen und nicht so ausgegeben werden. Die Ausgabe sollte z.B. so erfolgen:

ID1 von Tabelle A: 3 X B und 6 X C oder 1 | 3 | 6
ID2 von Tabelle A: 2 X B und 3 X C oder 2 | 2 | 3

Gruß

Nach deinem ersten Postinmg:

test=*# select 'A_' || aid::text || ' hat ' || (coalesce(length(b) - length(regexp_replace(b,',','','g')),0)+1)::text || 'XB' || ' und ' ||  (coalesce(length(c) - length(regexp_replace(c,',','','g')),0)+1)::text || 'XC' from (select a.aid, string_agg(distinct b.bid::text,',') as b, string_agg(distinct c.cid::text,',') as c froma left join b on a.aid=b.aid left join c on b.bid=c.bid group by a.aid) foo;
      ?column?
---------------------
 A_1 hat 3XB und 6XC
 A_2 hat 2XB und 3XC
 A_3 hat 2XB und 1XC
 A_4 hat 1XB und 1XC
(4 rows)

so vielleicht?

[Ralf Jansen]

from a in A
select new 
{  
   myAID = a.AID,
   AnzahlB = B.Count(b=> b.AID == a.AID),
   AnzahlC = C.Count(c=> c.b.AID == a.AID)
};

[Smart]

Hallo Ralf,

vielen Dank für deine Antwort.
Sehr interessante Lösung. Ich tue mir da etwas schwer, das Ganze nachzuvollziehen. Wie komme aber bei "c.b.AID == a.AID" auf b über c (c.b.)? Ist das b das gleiche b in "b=> b.AID == a.AID" oder meinst du damit B? Wenn ja, in c ist Und wie wird in C gruppiert gezählt nach AID in B?

Ist das eventuell möglichm, die Bedingung etwas zu erweitern? Z.B. nur die Datenzätze von B, deren ID (BID) auch tatsächlich in C vorkommen?

Gruß

[Ralf Jansen]

Es sollte B sein richtig erkannt.

Ist das eventuell möglichm, die Bedingung etwas zu erweitern? Z.B. nur die Datenzätze von B, deren ID (BID) auch tatsächlich in C vorkommen?

Ähm das habe ich wahrscheinlich nicht ganz verstanden.

c.B.AID heißt navigiere über die Navigational Property B zum Datensatz in B mit dem c über den Foreign Key BID verknüpft ist und sieh dir dort die AID an.
Eine entsprechende Navigational Property gibt es natürlich auch an B an der alle Datensätze aus C hängen die mit diesem B verknüpft sind. Also sollte es reichen zu prüfen ob die Property b.C leer ist.

from a in A
select new 
{  
   myAID = a.AID,
   AnzahlB = B.Count(b=> b.AID == a.AID && b.C.Count() > 0),
   AnzahlC = C.Count(c=> c.B.AID == a.AID)
};

Bin mir aber nicht sicher ganz ob b.C nicht doch null ist wenn es keinen passenden Datensatz in C gibt. Dann halt noch vorher b.C != null zusätzlich testen.

[Smart]

Hallo,

leider klappt das nicht. Ich bekomme Anzahl alle Datensätze von B mit Count = 0, bei denen keine Verknüpfung zu A vorliegt und alle Datensätze von C mit Count = 0, bei denen auch keine Verknüpfung zu B vorliegt. Es sieht in etwa so aus:

A-Name - 3B, 2C
A-Name - 0B, 0C
A-Name - 2B, 0C

Die unteren beiden sollen eigentlich nicht mehr sichtbar sein.

Gruß

[Ralf Jansen]

Kann ich so nicht nachvollziehen. Bei mir verhält sich das erwartungskonform( Heißt zumindest entsprechend meinen Erwartungen)

LinqQuery1.jpgLinqQuery2.jpg


Edit: Oder meinst du dort wo AnzahlB = 0 ist soll gar kein Datensatz mehr erscheinen?
Dann

from a in A
let tmp = new 
{  
   myAID = a.AID,
   AnzahlB = B.Count(b=> b.AID == a.AID && b.C.Count > 0),
   AnzahlC = C.Count(c=> c.B.AID == a.AID)
}
where tmp.AnzahlC > 0
select tmp

[Smart]

Hallo Ralf,

vielen Dank. Das funktioniert jetzt. Ist aber an dieser Stelle "b.C.Count > 0" noch nötig? Es funktioniert anscheint auch ohne.

Kannst du mir bitte noch eine Frage beantworten. Ist das ohne weiteres möglich, LINQToSQL durch EF zu ersetzen? Ich meine, einfach die .dbml-Datei von LINQToSQL löschen und ein EF anlegen. Mein Projekt ist momentan weit forgeschritten und ich traue mich nicht, einfach alles über Board zu schmeissen. Wird bei EF auch eine DataContext-Klasse ähnlich LINQToSQL angelegt?

Gruß

[Ralf Jansen]

Ich hab Linq2Sql nie benutzt insofern kann ich zu den tatsächlichen Unterschieden nicht viel sagen. Aber bei EF hast du einen zentralen ObjectContext über den du an deine Objekte kommst, dynamische Queries abfeuerst, speicherst etc. In wieweit das dem Linq2Sql DataContext entspricht weiß ich nicht.