Using SQL in OneStream Finance Business Rules, Part 3 – Lies, LINQ, and Lambda

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.

LINQ

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.

Lambda

A lambda expression is an unnamed (or anonymous) function.

Anonymous method

An anonymous method is simply a method without a name.

Delegate

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.

Text Description automatically generated

That’s it!

Graphical user interface, text, application, email Description automatically generated

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.

 

Share This: