Using LINQ on results from SubSonic's generic ExecuteTypedList

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 $g(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) &&
                    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 $g(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?

3 thoughts on “Using LINQ on results from SubSonic's generic ExecuteTypedList

  1. Mostly, I’ve found that LINQ’s pseudo-SQL syntax is just odd enough that it ends up being more of a distraction than a benefit. I usually write my queries using chained method calls with explicit lambdas for the projections and predicates, etc.
    This is slightly more important for LINQ to objects than to a DB: since there’s no optimizer it’s good to be clear about the sequencing of operations, so that e.g. filters can reduce the result set before a projection rather than after, which may result in discarding a large fraction of very recently constructed objects.
    There are other good things to be said about writing using the chained method call syntax. You can add your own extension methods and they’ll blend in just as well. There are many existing IEnumerable<T> extension methods that aren’t exposed by the LINQ syntax, and that would otherwise need an assignment or parenthesizing the entire expression to call.

  2. Just to make that earlier comment concrete, I’m taking one of your queries and writing it as chained method calls instead:
    var lineitems = from p in Details where !p.ProductID.Equals(52276) && !p.ProductID.Equals(53257) select p;
    var lineitems = Details.Where(p => p.ProductID != 52276 && p.ProductID != 53257);
    (I’m not sure why you were using !Int32.Equals(Int32) rather than the != operator. Also, apologies for the relatively mangled formatting; there’s no <pre> tag allowed in these comments 🙂

  3. Hey Bary,
    Thanks for the comments. Your point on distraction is spot on. In this case, when I wrote this post a few months ago I was messing around with numerous variations over the data I had and when it came time to finally posting I didn’t go back and review the code. I recall messing around with strings on one variation using ProductDescription so perhaps "unwinding" things, I ended up with what’s posted above though I don’t recall at this point. Admittedly, using ! with .Equals looks strange.
    The point on chained method calls is a good one though I haven’t spent time using that syntax.

Comments are closed.