If you found Lambda Syntax difficult to work with, then Query just might be what you were looking for.

What Makes It So “Special”?

Unlike Lambda Syntax, you don’t have to chain coaches of methods one after the other and worry about the anonymous functions not having enough brackets to bottle up its logic in place.

dt_sampleData.AsEnumerable().Select(Function(s) dt_introResult.Clone.LoadDataRow({s("Title"),If(Convert.ToString(s("Color/B&W")).Equals("Black and White"), "B&W", "Colored"),s("Genre"), s("Rating")},False)).CopyToDataTable()

Lambda Syntaxes are nowhere near as readable as Query Syntaxes, which is why people new to LINQ tend to lean towards it.

What Makes It So “Readable”?

When you have to perform checks which involve lengthy syntaxes which you have to call over and over again, you are better off storing the result to a variable and call that instead.

var str_randomString = @"Goodbye Cruel World!";
var str_sentimentAnalysis = If(str_randomString.ToLower.Contains("cruel"), "Bad", "Good!"); 
Console.WriteLine("Result of Sentiment Analysis: "+str_sentimentAnalysis);

Its better to store the result to a variable, instead of repeating the same block of operation over and over again.

When you store results to a variable inside Anonymous Functions, it generates Anonymous Objects. Query Syntax is comfortable working with anonymous objects, unlike Lambda Syntax which restricts its usage.

dt_sampleData.AsEnumerable().Select(Function(s) New With{.Year = Convert.ToDateTime(s("Release Date")).Year, .Month =
Convert.ToDateTime(s("Release Date")).Month, .Day = Convert.ToDateTime(s("Release Date")).Day}).ToList()

The whole point of creating anonymous objects is so that we may convert results into properties that we can reference.

If date_Variable.Year gives us the Year, then anonObj.Year should give us a collection of Years, but it doesn’t. The For Each Activity is not smart enough to realize that the Object has so and so properties and is incapable of dynamically retrieving them.

You won’t be using New With anywhere, other than inside of an Invoke Code, that also rarely, so you might as well ignore it.

Lets first create a simple Lambda Expression that will retrieve the rows where the Rating is “Approved”, and then convert that to Query Syntax.

dt_sampleData.AsEnumerable().Where(Function(w) Convert.ToString(w("Rating")).ToLower.Equals("approved")).CopyToDataTable()

How would that translate into query?

Lets have a look.

(From row In dt_sampleData.AsEnumerable()
Where Convert.ToString(row("Rating")).Trim.ToLower.Equals("approved")
Select row).CopyToDataTable()

As you can see, its pretty straightforward and much easier to craft.

But where is the benefit here? Is it only limited to better readability or is there something more?

You know the answer to that one.

Let’s Kick It Up a Notch!

What if you only wanted to populate your DataTable with Specific Columns?

While it can be achieved using Lambda(we just did it in the introduction), it will also leave you afflicted with Bracket Induced Trauma Syndrome.

Query does a magnificent job, but you can’t simply assign the collection to a DataTable which is either not initialized or initialized without any Data Columns.

The DataTable has to be initialized and the columns have to be added before you populate it with data.

You can also use the DefaultView Method to initialize DataTables like so:

dt_sampleData.DefaultView.ToTable(False,"Title","ColorB&W").Clone()

But don’t too get fancy.

Its better to use a For Each Loop, since it serves as a visual indicator as to what operation is being performed. Condensing everything into an Assign Activity only leads to more confusion, which although can be mitigated by providing detailed annotations, is not the best way to go about developing automations.

If you are interested in learning more about the DefaultView Method, then click here.

Now that our DataTable has been populated with columns, we can start populating it with rows.

(From row In dt_sampleData.AsEnumerable()
Where Convert.ToString(row("Rating")).Trim.ToLower.Equals("approved")

Like I mentioned earlier, you can declare variables inside a Query Syntax and store whatever you want into it, whether it be strings, arrays, List of IEnumerables etc.

In this particular scenario, we have to create an ItemArray that the DataTable will accept.

This is what the syntax looks like:

New Object(){item1, item2...itemn}

The keyword Let is what let’s(pun most definitely intended) us create local variables.

(From row In dt_sampleData.AsEnumerable()
Where Convert.ToString(row("Rating")).Trim.ToLower.Equals("approved")
Let ra = New Object(){row("Title"),row("Color/B&W"), row("Genre"), row("Rating")}

The ItemArray has been defined, and all that is left is for us to add it our DataTable, which is performed using a Select keyword like so:

(From row In dt_sampleData.AsEnumerable()
Where Convert.ToString(row("Rating")).Trim.ToLower.Equals("approved")
Let ra = New Object(){row("Title"),row("Color/B&W"), row("Genre"), row("Rating")}
Select dt_WaistDeepResult.Rows.Add(ra)).CopyToDataTable()

Lets declare one more local variable and assign that to our final result. Lets conditionally modify the title to include the Lead Actor if the Language is English.

Yes, its nonsensical, but its being done to show the flexibility which can be attained through Query Syntax.

(From row In dt_sampleData.AsEnumerable()
Where Convert.ToString(row("Rating")).Trim.ToLower.Equals("approved")
Let modifiedTitle = If(Convert.ToString(row("Language")).Trim.ToLower.Equals("english"),
 row("Title").ToString+" "+row("Lead Actor").ToString, row("Title"))
Let ra = New Object(){modifiedTitle,row("Color/B&W"), row("Genre"), row("Rating")}
Select dt_WaistDeepResult.Rows.Add(ra)).CopyToDataTable()

I know what you are thinking.

Query was supposed to make everything readable, but all it has done so far is bring you one step closer to suffering from a stroke.

It will be a little difficult at first, but stick with it long enough and it will eventually make sense.

In Conclusion

I’m cutting this article short because I know all of this is a lot to take in.

What I’ve guided you through is just a glimpse of what is achievable through LINQ. Whether you achieve the solution using Lambda or Query is besides the point.

Your ability to craft solutions is what matters, and LINQ is just one of those tools which will help you get there.