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
.
精彩评论