开发者

LINQ Normalizing data

I am using an OMS that stores up to three line items per record in the database.

Below is an example of an order containing five line items.

Order Header
Order Detail
   Prod 1
   Prod 2
   Prod 3
Order Detail
   Prod 4
   Prod 5

One order header record and two detail records.

My goal is have a one to one relation for details records(i.e., one detail record per line item). In the past, I used an UNION ALL SQL statement to extract the data. Is there a better approach to this problem using LINQ?

Below is my first attempt at using LINQ. Any feedback, suggestions or recommendations would greatly be appreciated. For what I have read, an UNION statement can tax the process?

var orderdetail =
    (from o in context.ORDERSUBHEADs
        select new { 
            edpNo = o.EDPNOS_001, price = o.EXTPRICES_001, 
            qty = o.ITEMQTYS_001 }
    ).Union(from o in context.ORDERSUBHEADs
        select new { edpNo = o.EDPNOS_002, price = o.EXTPRICES_002, 
            qty = o.ITEMQTYS_002 }
    ).Union(from o in context.ORDER开发者_如何学PythonSUBHEADs
        select new { edpNo = o.EDPNOS_003, price = o.EXTPRICES_003, 
            qty = o.ITEMQTYS_003 });


Id build a simple sub-class

class Record 
{
    public object SubHeading { get; set; }
    public int EdpNo { get; set; }
    public decimal Price { get; set; }
    public int Quantity { get; set; }
}

Then you just loop over it all

var orders = context.ORDERSUBHEADs.Select(o => 
   new Record[] {
      new Record { SubHeading = o, EdpNo = o.EDPNOS_001, Price = o.EXTPRICES_001, Quantity = o.ITEMQTYS_001 },
      new Record { SubHeading = o, EdpNo = o.EDPNOS_002, Price = o.EXTPRICES_002, Quantity = o.ITEMQTYS_002 },
      new Record { SubHeading = o, EdpNo = o.EDPNOS_003, Price = o.EXTPRICES_003, Quantity = o.ITEMQTYS_003 }
   }
);

IEnumerable allOrders = IEnumerable.Empty;
foreach(Record[] r in orders)
    allOrders = allOrders.Concat(r);

IEnumerable allRecords = allOrders.Cast<Record>();

But frankly your o structure sucks, you should build the list of items as an IEnumerable or IList or something, not #Parameters * #Rows.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜