How to get associated tables with LINQ

Question

let's say I have to tables: "Customer" (parent) & "Address" (child). They are associated, so there is a 1:1 relationship between them.

   Table<Customer> table = db.GetTable<Customer>();

   var query = from c in table
               select p;

Is there a possibility to query against tables that are associated with "Customer" using the selected Customer-tables or do I have to get all Address-tables in a separate query?

Besides if I use a DELETE-command on a Customer-table, does this DELETE all the associated tables too?

Thanks in advance,

Prot

We are having a good time up here. :) – Prot Aug 21 '12 at 10:55 — Prot, Aug 21 '12 at 10:55

score 1 · Accepted Answer · edited May 23 '17 at 12:27

If they are related with a foreign key, then it should be very straight forward. The Address should just be a property of Customer.

var query = from c in table
            select c.Address;

Or you could do it with a join if a foreign key doesn't exist.

var query = from c in table
            join address in [AddressTable] on c.AddressId equals address.Id
            select address;

The type of DELETE you're referring to is called a cascade delete. You'll need to enable it on your foreign key (you'll need an FK for this to work). See this thread.

score 0 · Answer 2 · answered Aug 21 '12 at 10:50

you don't have to get the address out separately, you should be able to just lazy load the address if you have set up an association in the dbml (i.e the arrow linking the tables).

Instellisense should show the property;

  var query = from c in table
               select p.Address;

for the delete set up an on delete cascade on your foreign key on the tables themselves and this will delete the associated address every time you delete a customer record.

How to get associated tables with LINQ

2 Answers2