Search code examples
sql-serverlinqlinq-to-sqlforeign-keysentity-relationship

Using relationship/foreign key with linq2sql?

Can anyone help?

I have a created a relationship between my Reservation(prim key) and Insurance(for key) tables and imported into linq2sql and checked my automatically created c# files and sure enough i have reservation.MyFieldNames etc etc PLUS reservation.Insurance which is my relationship but reservation.Insurance i can't see the fieldnames of Insurance - am i missing something?

Do i have to call a GetInsurances or something? I don't see anything.. In fact Insurance is of type Linq.entityset???

Also i was hoping to create my reservation table (which was nice and easy) and then automatically "INJECT" a Insurance (i.e. 1 to many) from Ilist or something..

Is this not possible,

Any help really appreciated

p.s. I did create my automatically created c# dto files via T4 Toolbox but this shouldn't make any difference. I just have a separate file for each entity..

Solution

  • Not 100% sure what you mean by "I don't see anything".

    Assuming you have a Reservations table and an Insurance table which references the "reservations" - what kind of relationship is this? 1:1 ? 1:many ? Which way around?? Can you post a screenshot of your DBML designer surface? (upload it to www.tinypic.com and include the link as an image here in your question)?

    The "Reservations" class will contain one instance of an "Insurance" (EntityRef<Insurance>) or a list (EntitySet) of "Insurance" objects (EntitySet<Insurance>) - depending on the nature of the relationship - and you should be able to navigate those in code, e.g.

    string foo =  myReservation.Insurance.CustomerName; // or whatever

    You won't see those on the designer surface - that's just a link to another class somehwere on your design surface, right?

    Same goes for the other way around - if the Insurance is associated with exactly one Reservation, you should be able (in code) to do:

     string bar = myInsuranceObject.Reservation.MyFieldName1; // or whatever

    So it's not quite clear to me which case you're really referring to - can you elaborate on your question a bit more and make it clearer??

    Based on the NerdDinner sample, I'll try to elaborate. Check out the DBML design surface:

    alt text

    Here, you don't see any of the properties used to move back and forth between the objects. But you do see that the "RSVP" class has a "DinnerID" foreign key which links it to the "Dinner" class. So this is a 1:n relationship: one Dinner has n RSVP's, and every RSVP is for exactly one Dinner.

    In your code, you see these created properties now - check out the RSVP class first:

    alt text

    You can see that the RSVP class has a EntityRef<Dinner> - a reference (link) to exactly one Dinner - that dinner that this RSVP is for.

    On the other hand, the Dinner class has a whole list of RSVPs of all the geek planning to attend that dinner!

    alt text

    So in the Dinner class, you have a EntitySet<RSVP> - a whole list of attendees, which you can then navigate when you're working with your Dinner class.

    Does this make things a bit clearer?

    Marc