Giocanni (13) [Avatar] Offline
#1
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
fabrice.marguerie (224) [Avatar] Offline
#2
Re: query problem with 2 FK in table
If you design your entities with the relationships, you should be able to write the following query to get the expected result

from travel in db.Travels
select new {
travel.Travel_ID,
Departure = travel.DeparturePortObject.PortLocation,
Arrival = travel.ArrivalPortObject.PortLocation
}


Do you have the relationships correctly defined in the database and in the LINQ to SQL dbml? You should see one navigation property for each port on the travel object. Here I named them DeparturePortObject and ArrivalPortObject, but you can specify other names using the designer.
Giocanni (13) [Avatar] Offline
#3
Re: query problem with 2 FK in table
> see one navigation property for each port on the
> travel object. Here I named them
> DeparturePortObject and
> ArrivalPortObject, but you can specify other
> names using the designer.

thank you! I'm working in VB so I had to adapt it but it's working now!

I found the port objects you talk, but they are called Port and Port1 in the Intellisense, and I see many references to them in the designer

but the designer was generated, I didn't assign those names.. and I think they are confusing, how can I change these names without messing everything up?

like from port1 ---> to ArrivalPort
fabrice.marguerie (224) [Avatar] Offline
#4
Re: query problem with 2 FK in table
You are right, the default names look like Port and Port1. You can rename the properties in the designer. Are you using the LINQ to SQL designer or SQLMetal.exe?

If you use the designer (for the .dbml file), you can click on the relationships and select Properties in the context menu. You can then specify the names you want under Child Property and Parent Property. You should see Port1 there.

If you use SQLMetal, I don't think that you can change the names, unless you do it in the generated .vb file. But that would be lost the next time you regenerate the file...
Giocanni (13) [Avatar] Offline
#5
Re: query problem with 2 FK in table
I don't know how to use sqlmetal.exe, I've never tried, I work with visual designer only

that works anyway! thanks a LOT

I'm still trying to learn but I struggle to find nice tutorials apart the ones in the help and the videos of ms....
fabrice.marguerie (224) [Avatar] Offline
#6
Re: query problem with 2 FK in table
Did you read LINQ in Action? smilie
We may not cover the LINQ to SQL designer in details (not with videos at least), but it should get you started.
Giocanni (13) [Avatar] Offline
#7
Re: query problem with 2 FK in table
I was told that LINQ in action is oriented to C# programming, and that all examples are in C#... Since I program in VB (and I'm a newb, started 1 month ago), I can't really understand all examples in C#...
fabrice.marguerie (224) [Avatar] Offline
#8
Re: query problem with 2 FK in table
I'd be curious to know who told you that LINQ in Action is for C# only.
We cover both C# and VB.NET in the book. This is one big difference with the competing books. Even if not all code samples are in both languages all the time in the book, we make sure to demonstrate and explain the differences between both languages when it makes sense.
In addition, the complete source code for all the code samples in the book is available as a free download.
Giocanni (13) [Avatar] Offline
#9
Re: query problem with 2 FK in table
it was on some forum or newsgroup, somebody asking for VB linq resources and was told that linq in action was good but more for c# users...

I'm going to buy it then, thanks again
fabrice.marguerie (224) [Avatar] Offline
#10
Re: query problem with 2 FK in table
To determine if this book is for you before buying it, you can take a look at all the resources we made available for free. This includes 3 free chapters and the source code. You can find them all on the book homepage.

Fabrice