Lately, I’ve been working on the Purchase Order portion of the EDI system I’ve blogged about. The system makes use of SubSonic, a .NET DAL framework, which comes in handy when dealing with the DB of an ERP system with 429 tables and approaching 2000 SPROCs.
Recently, I worked on the EDI 856 which is an "Advanced Ship Notice" (ASN) document that includes shipping details for the PO. Included in the line item details, as stored in the DB, are "special services" which have to be separated out from normal line items within the document. The are reason they’re stored together is historic though tangential to my point. Fetching the 856 line item data is done via a stored proc and the result set is an aggregate of fields from a number of tables as well as calculated values. Fortunately, Subsonic can generate wrappers for calling SPROCs though doesn’t provide a wrapper for the result set in cases such as this.
Working with SPROC results using SubSonic
Subsonic generates a generic method called ExecuteTypedList<> that takes a class that will be the type of an IList returned from the call like this:
SPs.SaRptShipConfirmDetails(OrderHeaderID, true).ExecuteTypedList<ShipmentDetail>();
SaRptShipConfirmDetails is a wrapper for the like named SPROC and this call returns an IList<ShipmentDetail> where ShipmentDetail declared as follows:
public class ShipmentDetail
{
public int LineNumber { get; set; }
public string SKU { get; set; }
public string ProductDescription { get; set; }
public int Ordered { get; set; }
public int Committed { get; set; }
public int Shipped { get; set; }
public decimal Price { get; set; }
public decimal Amount { get; set; }
public string CountryOfOrigin { get; set; }
public decimal UnitWeight { get; set; }
public string SizeName { get; set; }
public int OrderDetailID { get; set; }
public int ProductID { get; set; }
public string BuyerProductCode { get; set; }
public string UOMCode { get; set; }
public string EDIUOMCode { get; set; }
public int ShipmentDetailId { get; set; }
}
The get/set declarations above are required allowing ExecuteTypedList<> to determine where to stuff the returned SPROC column values into the ShipmentDetail structure. In fact, the SubSonic sources for the underlying SqlQuery.BuildTypedResult method read "//coerce the values, using some light reflection". From there you can easily use LINQ to query the IList results as desired. Below is a very simple example of filtering out these "special services" by ProductID:
var lineitems = from p in Details
where !p.ProductID.Equals(52276) &&
!p.ProductID.Equals(53257)
select p;
Clearly this is a simple example and it would be easy enough to write logic to skip the two ProductIDs indicated though simply playing around with LINQ in this manner has been interesting. For example, it gave me an opportunity to experiment with code completion in such a case which was interesting. I definitely recommend setting up a similar example where it’s easy to test the results and give yourself time to get used to "thinking" LINQ.
Using LINQ to query for specific instances of a class within an array
Another case where LINQ came in handy was in finding data read into an in memory structure of an EDI document. I’ve created a class hierarchy for working with EDI segments which make up an EDI document and with LINQ can now easily query a loaded document looking for specific segment instances like this:
public EdiRecord[] FindSegments<T>()
{
var query = from EdiRecord s in this
where s.GetType() == typeof(T)
select s;
return query.ToArray<EdiRecord>();
}
FindSegments is a generic method that takes the class that I’m looking for an returns an array of all the instances of that class type found. I’ll admit that starting to use LINQ has been an interesting if not counter intuitive experience for me as it’s hard not to think of doing this sort of thing the "old fashioned" way and immediate start writing that code.
Lastly, a minor blog note, I’m testing a new syntax highlighter via Windows Live Writer so it will be interesting to see how well it works (or not).
Anyway, what’s been your experience using LINQ?