Using linq2SQL to return a 1 to many relationship?

Question

Hi there,

is there a good way of of returning a 1 to many relationship from linq2sql, probably needs some explanation ! :-)

Basically i have a table that has invoices and another table that is invoice details..

I have my linq2sql classes that were automatically created using linq2sql designer ...

So to return the query where invoice 1232 has 15 different items/details... The way i see it at the moment is that i have to Do a query on the invoice and then a query on the invoice query detail...

I am sure there must be an easier way ? rather than doing 2 queries ... i.e. a JOIN?

Also i wanted to see if i could return the values in a Iqueryable but with the linked table?

Basicall i have my method that is Iqueryable<> GetInvoice();

of course Iqueryable is a generic and can contain Invoice or Invoice Details - NOT both, or maybe i am missing something?

Answer 1

If your database design already contains a connection ('constraint') between the primary key in your invoices table, and the foreign key (invoice id) in your invoice details table, then you should be able to access your invoice details from your invoice object like this:

myInvoice.Details

Linq to SQL should have created this relationship for you when you dragged your tables into the designer. In other words, you can reach out from your Invoice object, and access the details directly.

If this relationship does exist, you should see it in the Linq to SQL designer as a line drawn between the invoices table and the invoice details table.

Answer 2

If you have your relationships set up in your db, then you created you objects using the designer, you should automatically be able to get invoice details from an invoice.

something like this....

IList<InvoiceDetail> invoiceDetails = GetInvoice().SingleOrDefault(x => x.invoiceKey == 1232).InvoiceDetails.ToList();