Linq to Sql Many-One relationship

Question

I have 2 views in SQL set up:

• PurchaseOrder
• PurchaseOrderLineItems

These have many columns aliased (the tables they view/join are not sensibly named... it's a 3rd party product)

I have 2 classes (simplified below)

    class PurchaseOrder
    {
        public string PoNumber { get; set; }
        public string Vendor { get; set; }
        public DateTime DateCreated { get; set; }

        public IEnumerable<PurchaseOrderLineItems> LineItems { get; set; }
    }

and

    class PurchaseOrderLineItems
    {
       public string PoNumber { get; set; }
       public string Name { get; set; }
       public double Price { get; set; }
    }

I'm using Linq to Sql - with XML mapping file (created with help from sqlmetal.exe)

What I want to do is effectivly populate the IEnumerable in PurchaseOrder with records from the PurchaseOrderLineItem view - effectively joining the tables

I wanted to do this using POCO - without having to add EntitySet<> to my class, as eventually, I will change my ORM to something like nHibernate (which has bag attribute i believe...?)

Currently, I've got a stored procedure - sp_getPurchaseOrderLineItems which takes the PONumber, and then returns a list of PurchaseOrderLineItem objects, that i then add to my result set (this is far, far from ideal)

is there any way I can do what i need? So that basically, a query on PurchaseOrder returns an already populated IEnumerable of LineItems within the instance?

It's worth mentioning that this will only ever be read-only, we'll never be inserting / updating data using this.

Answer 1

You can extend your PurchaseOrder class to implement the OnLoadedMethod:

public partial class PurchaseOrder 
{
    partial void OnLoaded()
    {
        LineItems = FunctionToCall_sp_getPurchaseOrderLineItems_AndBuildSet();
    }
}

This will at least get the line items automatically when you get your PO.

Answer 2

This is the n+1 problem. nHibernate has a solution for this, which is called join-fetch querying. What it basically does is a outer-join query between order and order-line, which will result in the product of the row counts of the two tables.

I don't think Linq2SQL does have a solution for it. But you can still use your stored procedure to generate the join-fetch output, and have some Linq2Objects code to distinct the unique orders and the order-lines out of the result.