Would I lie to you? Would I? I would.
Yes, Gentle Reader, in my last SQL and OneStream Finance Business Rule post, I pinky promised that the next post (this one) would finally cover really using SQL to drive said rules. I also said (actually I only did this in response to a comment from Cosimo Palmisano) that I’d cover LINQ and SQL.
I was going to do that, sooner or later and quite probably later, but Daniel Willis messaged me on LinkedIn with a code sample and the note, “Utilising LINQ to output things like lists to the error log. I feel like this example ups the geek level of your latest post even further.” His code is proof, if proof be needed, that there are many ways to approach and accomplish a task in OneStream because of its open nature. Said flexibility is a geek’s delight.
This post isn’t Cosimo’s LINQ and SQL just yet, not is it SQL driving OneStream Finance Business Rules, but it is a powerful example of how Language-Integrated Query (LINQ) can be used to within OneStream itself in Finance Business Rules and practically anywhere else in the product.
Speak geeky to me
A note before I go further with this: “speak geeky to me” is sort of a running joke with we more functional OneStream practitioners. What follows is pretty geeky albeit powerful and worth knowing because of its power. It is also confusing.
When I come across something I don’t understand in OneStream or really anything in life, I must understand the how and the why, for without the latter the former will never be done correctly.
Cue Yr. Obt. Svt. going off for a few hours understanding how on earth LINQ works. Tick-tock, tick-tock, tick-tock…
Okay, I’m back. I had a quick-ish scan of this excellent overview which I heartily suggest you read if you want to understand the power of LINQ. With that, here’s what I’ve learnt.
Language Integrated Query – LINQ – is an ADO.NET data access component within the .NET framework that allows queries of enumerable classes.
A VB.NET (or C#) DataTable is an enumerable in-memory table class.
LINQ can query enumerable DataTables using (amongst other approaches) lambda functions.
A lambda expression is an unnamed (or anonymous) function.
An anonymous method is simply a method without a name.
A delegate is an object that points to a method. It can have explicit or inferred typing.
LINQ and Lambda
Lambda functions within LINQ expose delegates – such as DataRows – for querying.
Querying with LINQ
NB – Daniel’s LINQ query assumes the already populated DataTable dtSimple as shown in my previous post.
The single-line (ignore the nice formatting – there is no For..Each loop statement in this code; .Select performs that loop in a single statement) LINQ query uses the DataTable.AsEnumerable method (which has a generic parameter of DataRow) to loop the table and query the results. That’s pretty cool.
Function(x) – “Function(x)” is what made me start this mad journey into LINQ because I had no understanding of what it really does – is a lambda expression where x is an inferred variable whose type is DataRow.
The .Select method loops the DataTable. Its results are concatenated into a string which is then joined to itself and then passed up to api.LogMessage.
What could possibly be simpler? There is a mild amount of sarcasm there but for those smarter than me (practically everyone) or a normie (also practically everyone but me), it isn’t that hard to grasp or use. LINQ is very powerful and its capabilities go far beyond the above example.
Two pages to describe one line
I have surpassed myself in my comment-to-code ratio, i.e. two pages in Word for one line of code. Sorry, but I had to understand how something so simple-ish looking could do so much. Hopefully you do now as well. There is more – much more – out there on LINQ and I will be exploring how LINQ and SQL really interact with each other in the next post.
One day, probably, I will cover my original premise of using SQL to drive Finance Business Rules, but I fear I must stop endlessly predicting that I’ll do so in the next post as there’s so much more to explore. I’ll get to it sooner or later. Probably. Definitely. Maybe.
Be seeing you.
- Using SQL in OneStream Finance Business Rules, Part 3 – Lies, LINQ, and Lambda - July 20, 2022
- Using SQL in OneStream Finance Business Rules, Part 2 – 1 or 0 or Nothing at All – Looping Data Table Collections In OneStream - July 6, 2022
- Using SQL in OneStream Finance Business Rules, Part 2 – 1 or 0 or Nothing at All – Looping Data Table Collections In OneStream - July 5, 2022