Linq to Sql Many-One Relationship
I have 2 views in SQL:
- PurchaseOrder
- PurchaseOrderLineItems
They have a lot of column aliases (the tables they are looking at / joining are not named intelligently ... this is a third 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 am using Linq to Sql - with XML mapping file (generated with sqlmetal.exe)
What I want to do is to effectively fill the IEnumerable in the PurchaseOrder with records of the view PurchaseOrderLineItem - effectively join tables
I wanted to do this with POCO - without adding the EntitySet <> to my class as I would end up changing my ORM to something like nHibernate (which has a bag attribute that I believe ...?)
I currently have a stored procedure - sp_getPurchaseOrderLineItems that takes a PONumber and then returns a list of PurchaseOrderLineItem objects that are then added to my result set (far from ideal)
Is there a way to do what I need? So basically, a PurchaseOrder request returns an already populated IEnumerable from LineItems in an instance?
It's worth noting that this will only ever be read-only, we will never insert / update data using this.
source to share
This is an n + 1 problem. For this, nHibernate has a solution called fetch join. Basically it is a query for an outer join between order and order line, which will result in a row count result from two tables.
I don't think Linq2SQL has a solution for it. But you can still use your stored procedure to generate the join-fetch output and have some Linq2Objects code to distinguish the unique orders and order lines from the result.
source to share
You can extend the PurchaseOrder class to implement OnLoadedMethod:
public partial class PurchaseOrder
{
partial void OnLoaded()
{
LineItems = FunctionToCall_sp_getPurchaseOrderLineItems_AndBuildSet();
}
}
This will at least automatically receive positions when you receive your software.
source to share