none
Can somebody help to translate (VB.NET Linq!)

    Question

  • Hey

    I have the following working SQL Statement

    SELECT
      CONCAT(a.NName, ' ', a.VName) AS myArzt,
      auf.Auftragsart AS Auftragsart,
      COUNT(auf.Bewegung) AS Zahl
    FROM Auftrag auf
      INNER JOIN kundenstamm k
        ON k.KundenId = auf.KundenId
      INNER JOIN aerzte a
        ON auf.AerzteID = a.ID
    WHERE k.firmenid = 1
    GROUP BY myArzt,
             auf.Auftragsart;

    Is there somebody out who coul help me translate it to a regular VB.NET Linq query?

    THX a lot - I don't get it working anyway...

    Tuesday, December 10, 2013 2:14 PM

Answers

  • Hello,

    Since I do not know the exact table structures, I assume them to be like below:

    CREATE TABLE [dbo].[kundenstamm] (
    
        [KundenId] INT NOT NULL,
    
        [firmenid] INT NOT NULL,
    
        PRIMARY KEY CLUSTERED ([KundenId] ASC)
    
    );
    
    CREATE TABLE [dbo].[Auftrag] (
    
        [AuftragId]   INT        NOT NULL,
    
        [KundenId]    INT        NOT NULL,
    
        [AerzteID]    INT        NOT NULL,
    
        [Auftragsart] NCHAR (10) NOT NULL,
    
        [Bewegung]    NCHAR (10) NOT NULL,
    
        PRIMARY KEY CLUSTERED ([AuftragId] ASC)
    
    );
    
    
    CREATE TABLE [dbo].[aerzte] (
    
        [ID]    INT        NOT NULL,
    
        [NName] NCHAR (10) NOT NULL,
    
        [VName] NCHAR (10) NOT NULL,
    
        PRIMARY KEY CLUSTERED ([ID] ASC)
    
    );
    
    

    Then the corresponding VB linq query should be:

    Dim db As VBDBEntities = New VBDBEntities()
    
            Dim result = (From Auftrag In db.Auftrags
    
                         Join kundenstamm In db.kundenstamms On Auftrag.KundenId Equals kundenstamm.KundenId
    
                         Join aerzte In db.aerztes On aerzte.ID Equals Auftrag.AerzteID
    
                         Group myArzt = aerzte.NName + " " + aerzte.VName, Auftragsart = Auftrag.Auftragsart, Bewegung = Auftrag.Bewegung By myArzt = aerzte.NName + " " + aerzte.VName, Auftragsart = Auftrag.Auftragsart Into Group
    
                         Select New TempEntity With {.myArzt = myArzt, .Auftragsart = Auftragsart, .Bewegung = Group.Count()}).ToList()
    
    If this does not work for you, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Zero-G. _ Wednesday, December 11, 2013 12:43 PM
    Wednesday, December 11, 2013 2:56 AM