How to use correct LinQ in VB.Net when using left join and sum

Question

Hey.

I have 4 tables, which are all related together

CREATE TABLE tagesabschluss (
  ID bigint NOT NULL AUTO_INCREMENT,
  auftragid bigint DEFAULT NULL,
  Druck int NOT NULL,
  ErstSignatur varchar(32) DEFAULT NULL COMMENT 'Erstsignatur lt. E131 Formular',
  name text DEFAULT NULL,
  adresse text DEFAULT NULL,
  AbgabeDatum date NOT NULL,
  BezahlDatum date DEFAULT NULL,
  Abgegeben text NOT NULL,
  AufBetrag double DEFAULT NULL,
  bezart text DEFAULT NULL,
  BezBetrag double DEFAULT NULL,
  firmenid bigint DEFAULT NULL,
  Zeitstempel timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (ID)
)
ENGINE = INNODB
 
  PRIMARY KEY (id)
)
ENGINE = INNODB

CREATE TABLE tagesabschlussdetails (
  id bigint NOT NULL AUTO_INCREMENT,
  firmenid bigint DEFAULT NULL,
  tagabid bigint DEFAULT NULL,
  renummer bigint NOT NULL,
  datum date NOT NULL,
  artikelid bigint DEFAULT NULL,
  code char(20) DEFAULT NULL,
  artikelbezeichnung char(30) DEFAULT NULL,
  menge int DEFAULT NULL,
  preis decimal(10, 2) DEFAULT NULL,
  preis_netto decimal(10, 2) DEFAULT NULL,
  mwst decimal(10, 2) DEFAULT NULL,
  Zuordnung int DEFAULT NULL,
  Zeitstempel timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
)
ENGINE = INNODB

CREATE TABLE tagabkrankenkassenanteil (
  id bigint NOT NULL AUTO_INCREMENT,
  tagabid bigint DEFAULT NULL,
  kassenanteil double DEFAULT NULL,
  selbstbehalt double DEFAULT NULL,
  Zeitstempel timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
)
ENGINE = INNODB

CREATE TABLE receiptresponse (
  Id bigint NOT NULL AUTO_INCREMENT,
  KundenId bigint DEFAULT NULL,
  ReNummer bigint NOT NULL,
  Jahr int DEFAULT NULL,
  FirmenId bigint NOT NULL,
  Referenz varchar(255) DEFAULT NULL,
  Moment datetime DEFAULT NULL COMMENT 'UTC Format',
  ReferenzId varchar(255) DEFAULT NULL,
  Zeitstempel timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (Id)
)
ENGINE = INNODB

Now I need a Linq, which helps me to flatten some of the data. Therefore, I use the following (working) Linq

 Dim locResult = From locDailyReport In locDataContext.Tagesabschluss
                                    Join locDailyReportDetails In locDataContext.Tagesabschlussdetails
                                        On locDailyReport.Id Equals locDailyReportDetails.Tagabid
                                    Group Join locReceiptResponse In locDataContext.receiptresponses On locDailyReportDetails.Renummer Equals locReceiptResponse.renummer And locDailyReport.abgabedatum.Year Equals locReceiptResponse.jahr And locDailyReport.Firmenid Equals locReceiptResponse.firmenid Into locReceiptJoin = Group From locReceiptResponse In locReceiptJoin.DefaultIfEmpty()
                                    Group Join locInsurance In locDataContext.Tagabkrankenkassenanteils On locDailyReport.Id Equals locInsurance.Tagabid Into locRightJoin = Group From locGroupedInsurance In locRightJoin.DefaultIfEmpty()
                                    Where locDailyReport.Firmenid = LinqProvider.GetAktivCompanyId AndAlso
                                        (locDailyReport.abgabedatum >= locFrm.vonDatum And locDailyReport.abgabedatum <= locFrm.bisDatum)
                                    Order By locDailyReportDetails.Id
                                    Select New Klasse.Export With {.AbgabeDurch = locDailyReport.Abgegeben,
                                                                          .AbgabeDatum = locDailyReportDetails.Datum,
                                                                          .Adresse = locDailyReport.Adresse,
                                                                          .Bezahlart = locDailyReport.Bezart,
                                                                          .BezahlDatum = locDailyReport.abgabedatum,
                                                                          .BezahlterBetrag = locDailyReport.Bezbetrag,
                                                                          .Bezeichnung = locDailyReportDetails.Artikelbezeichnung,
                                                                          .Id = locDailyReport.Id,
                                                                          .KrankenkassaTarif = locGroupedInsurance.Kassenanteil - locGroupedInsurance.Selbstbehalt,
                                                                          .Menge = locDailyReportDetails.Menge,
                                                                          .Name = locDailyReport.Name,
                                                                          .Preis = locDailyReportDetails.Preis,
                                                                          .RechnungNummer = locDailyReportDetails.Renummer,
                                                                          .RegistrierkassaMoment = locReceiptResponse.moment,
                                                                          .RegistrierkassaReferenz = locReceiptResponse.referenz,
                                                                          .RegistrierkassaReferenzId = locReceiptResponse.referenzid,
                                                                          .Skonto = locDailyReport.Bezbetrag - locDailyReport.Aufbetrag}

What I try to achieve is, that I need a Sum over the locGroupedInsurance Variable

Means:

locDailyReport -> Left Outer Join to locDailyReportDetails

locDailyReport -> Left Outer Joint to locReceiptResponse (based on two fields)
locDailyReport -> Left Outer Join to locInsurance with Sum of fields (Kassenanteil - Selbstbehalt) or DefaultIfEmpty

Could someone please help me, how to achieve this!?

THX in advance

Answer 1

hope this one helps

Dim locResult = From locDailyReport In locDataContext.Tagesabschluss
                Join locDailyReportDetails In locDataContext.Tagesabschlussdetails
                    On locDailyReport.Id Equals locDailyReportDetails.Tagabid
                Group Join locReceiptResponse In locDataContext.receiptresponses On locDailyReportDetails.Renummer Equals locReceiptResponse.renummer And locDailyReport.abgabedatum.Year Equals locReceiptResponse.jahr And locDailyReport.Firmenid Equals locReceiptResponse.firmenid Into locReceiptJoin = Group From locReceiptResponse In locReceiptJoin.DefaultIfEmpty()
                Group Join locInsurance In locDataContext.Tagabkrankenkassenanteils On locDailyReport.Id Equals locInsurance.Tagabid Into locRightJoin = Group From locGroupedInsurance In locRightJoin.DefaultIfEmpty()
                Where locDailyReport.Firmenid = LinqProvider.GetAktivCompanyId AndAlso
                    (locDailyReport.abgabedatum >= locFrm.vonDatum And locDailyReport.abgabedatum <= locFrm.bisDatum)
                Order By locDailyReportDetails.Id
                Select New Klasse.Export With {
                    .AbgabeDurch = locDailyReport.Abgegeben,
                    .AbgabeDatum = locDailyReportDetails.Datum,
                    .Adresse = locDailyReport.Adresse,
                    .Bezahlart = locDailyReport.Bezart,
                    .BezahlDatum = locDailyReport.abgabedatum,
                    .BezahlterBetrag = locDailyReport.Bezbetrag,
                    .Bezeichnung = locDailyReportDetails.Artikelbezeichnung,
                    .Id = locDailyReport.Id,
                    .KrankenkassaTarif = locGroupedInsurance.Sum(Function(x) x.Kassenanteil - x.Selbstbehalt),
                    .Menge = locDailyReportDetails.Menge,
                    .Name = locDailyReport.Name,
                    .Preis = locDailyReportDetails.Preis,
                    .RechnungNummer = locDailyReportDetails.Renummer,
                    .RegistrierkassaMoment = locReceiptResponse.moment,
                    .RegistrierkassaReferenz = locReceiptResponse.referenz,
                    .RegistrierkassaReferenzId = locReceiptResponse.referenzid,
                    .Skonto = locDailyReport.Bezbetrag - locDailyReport.Aufbetrag
                }

Answer 2

THX a lot!

Based on your input, I found the correct solution!

I have hade to delete the second group join and use the locDailyReport extension.

Dim locResult = From locDailyReport In locDataContext.Tagesabschluss
                                    Join locDailyReportDetails In locDataContext.Tagesabschlussdetails
                                        On locDailyReport.Id Equals locDailyReportDetails.Tagabid
                                    Group Join locReceiptResponse In locDataContext.receiptresponses On locDailyReportDetails.Renummer Equals locReceiptResponse.renummer And locDailyReport.abgabedatum.Year Equals locReceiptResponse.jahr And locDailyReport.Firmenid Equals locReceiptResponse.firmenid Into locReceiptJoin = Group From locReceiptResponse In locReceiptJoin.DefaultIfEmpty()
                                    Where locDailyReport.Firmenid = LinqProvider.GetAktivCompanyId AndAlso
                                        (locDailyReport.abgabedatum >= locFrm.vonDatum And locDailyReport.abgabedatum <= locFrm.bisDatum)
                                    Order By locDailyReportDetails.Id
                                    Select New Klasse.E131BAOExport With {.AbgabeDurch = locDailyReport.Abgegeben,
                                                                          .AbgabeDatum = locDailyReportDetails.Datum,
                                                                          .Adresse = locDailyReport.Adresse,
                                                                          .Bezahlart = locDailyReport.Bezart,
                                                                          .BezahlDatum = locDailyReport.abgabedatum,
                                                                          .BezahlterBetrag = locDailyReport.Bezbetrag,
                                                                          .Bezeichnung = locDailyReportDetails.Artikelbezeichnung,
                                                                          .Id = locDailyReport.Id,
                                                                          .KrankenkassaTarif = locDailyReport.Tagabkrankenkassenanteils.Sum(Function(locInsurance) locInsurance.Kassenanteil - locInsurance.Selbstbehalt),
                                                                          .Menge = locDailyReportDetails.Menge,
                                                                          .Name = locDailyReport.Name,
                                                                          .Preis = locDailyReportDetails.Preis,
                                                                          .RechnungNummer = locDailyReportDetails.Renummer,
                                                                          .RegistrierkassaMoment = locReceiptResponse.moment,
                                                                          .RegistrierkassaReferenz = locReceiptResponse.referenz,
                                                                          .RegistrierkassaReferenzId = locReceiptResponse.referenzid,
                                                                          .Skonto = locDailyReport.Bezbetrag - locDailyReport.Aufbetrag}