locked
Help with a linq query RRS feed

  • Question

  • Hello, I have a problem with a query... and 2 tables

    I want to associate each travel with a departure and an arrival port. I keep all the ports in another table

    TravelsTable      -        Travel_ID(pk) ; DeparturePort(fk) ; ArrivalPort(fk)
    PortTable         -         Port_ID(pk) ; PortLocation

    my two foreign keys in the TravelsTable  are linked to the Port_ID in the PortTable... so I have records like this in the Tables

    Travel_ID=1       DeparturePort = 1           ArrivalPort = 4
    Travel_ID=2       DeparturePort = 3           ArrivalPort = 2
    Travel_ID=3       DeparturePort = 4           ArrivalPort = 5

    Port Table
    Port_ID=1        PortLocation= London
    Port_ID=2        PortLocation= Le Havre
    Port_ID=3        PortLocation= New York
    Port_ID=4        PortLocation= La Rochelle
    Port_ID=5        PortLocation= Zeebrugge

    ----------------

    What I need is to have a list with the Name of the departure/arrival ports... I need something like this

    Travel_ID      Departure         Arrival
    1                  London            La Rochelle
    2                 New York         Le Havre
    ....              

    Can you help me writing this query, in Linq or sql, or both?    I read about joins but I can't seem to put things together... I'm not even sure is the way to do it

    Thanks
    Wednesday, March 5, 2008 12:40 PM

Answers

  • T-SQL

     

    SELECT

      t.Travel_IDd.PortLocation, a.PortLocation

    FROM

      TravelsTable t

        JOIN

      PortTable d ON t.DeparturePort = d.Port_ID

        JOIN

      PortTable a ON t.ArrivalPort = d.Port_ID

     

     

    LINQ:

     

    Create two relationships in the designer... One from TravelTable(DeparturePort) to PortTable(Port_ID), the other from TravelTable(ArrivalPort) to PortTable(PortID)

     

    var q = from t in myDC.Travels

               select t.TravelID, t.DeparturePort.PortLocation, t.ArrivalPort.PortLocation;

    Wednesday, March 5, 2008 2:20 PM

All replies

  • T-SQL

     

    SELECT

      t.Travel_IDd.PortLocation, a.PortLocation

    FROM

      TravelsTable t

        JOIN

      PortTable d ON t.DeparturePort = d.Port_ID

        JOIN

      PortTable a ON t.ArrivalPort = d.Port_ID

     

     

    LINQ:

     

    Create two relationships in the designer... One from TravelTable(DeparturePort) to PortTable(Port_ID), the other from TravelTable(ArrivalPort) to PortTable(PortID)

     

    var q = from t in myDC.Travels

               select t.TravelID, t.DeparturePort.PortLocation, t.ArrivalPort.PortLocation;

    Wednesday, March 5, 2008 2:20 PM
  • Thanks, I'm making some tests and it seems that's working... the only annoying thing is that I have those stupid names Port and Port1, that were auto-assigned and I can't seem to change them from the dbml view....


    (the names are a bit different from my simplified example but the concept is the same)

    Code Snippet Visual Basic

            Dim query2 = From ship In db.Shippings _

     Select ship.ShippingID, Departure = ship.Port.PortLocation,
     Arrival = ship.Port1.PortLocation

    Wednesday, March 5, 2008 4:35 PM
  • See my reply in the LINQ in Action forum.
    Wednesday, March 5, 2008 4:37 PM
  • Surprise

    you're here as well!
    Wednesday, March 5, 2008 7:06 PM
  • I'm everywhere... as soon as LINQ is concerned Wink
    Wednesday, March 5, 2008 7:06 PM