All in one searching for dummies & advanced users with Expression Trees, Dynamic Linq and Funcy Linq Magic#

Currently having fun with #DynamicLinq #LambdaParsing #ExpressionTrees and more… – I was blown away by the power of Expressions and Dynamic Linq (parsing) – thus I wanted to share this code & ideas already. It’s far from finished and still needs to be fleshed out, to make it user friendly (frontend) – but at least the ideas, capabilities and tech for the backend are already present to make a flexible solution that caters both to users that want a quick search, and users that will want a (typed?) refined searchall in one searchbox.

17022049_10155053097151091_4004970426825369471_n

Why again? I’m building a rich query back end for both “dummy match all searches” as well as really specialized queries for when people are a bit more advanced and want to tap into some sql-like query strings in the inputbox.. So, yes, it needs to support one inputbox, the parser will figure out which #searchstrategy to use..

I was having loads of fun with #ExpressionTrees #Func and #DynamicLinq #DotNet #Programming #CSharp and I wanted to share some of the code & NuGet packages I used for this solution so far. All suggestions & critique are welcome.

1. Supporting Linq to Entities searches with Expressions…

It might sound easier than it is, but Linq to Entities (Linq with Entity Framework) doesn’t translate everything to SQL as you’d think… To support this I found the LinqKit project, which gives me the power to use Expression Trees inside any Linq to Entities query. Mostly I’m using a predicate based model as Func<Entity, bool>, but here’s my actual

actual implementation as reference:

// other using statements
using LinqKit;

// Search method accepting Expressions of Func<Person, bool> that can be evaluated
// read more about "AsExpandable" at LinqKit: https://github.com/scottksmith95/LINQKit
public IEnumerable<Person> Search(params Expression<Func<Person, bool>>[] predicates{
    var query = _dataContext.People.AsExpandable();
 
    foreach (var predicate in predicates) {
        query = query.Where(person => predicate.Invoke(person));
    }

    return query.AsEnumerable();
}

2. Support Text-Based Expression Trees to actual Expression Trees in code

The next step was to support a text-based version of such an Expression, for example, if a user wants to search on a person’s “FirstName” and only those that contain “oen” inside the “FirstName”. Then we’d end up with a text-based expression that looks a bit like this: “FirstName.Contains(“oen”)”; or if we’d want the “FirstName” to start with “Lil” we’d end up with code that looks a bit like this: “FirstName.StartsWith(“Lil”)“.

To convert those text-based C# like code / Expressions I used the System.Linq.Dynamic project – I’ll let the code below speak for itself (it uses the above example to execute the Expression it creates), but be sure to visit https://github.com/kahanu/System.Linq.Dynamic for more information on the project.

public IEnumerable<Person> Search(string query{
    // parsing the "query", 
    //   e.g.: "(FirstName.Trim() + LastName.Trim()).ToLower().Contains(\"lil\".Trim().ToLower())"
    var expression = System.Linq.Dynamic.DynamicExpression.ParseLambda<Person, bool>(query);
    // passing the parsed expression to the Search method (see the code in point 1)
    return Search(expression);
}

Here are some of the queries that are possible using this approach…

// Using the base search method in point 1
var personId = 33311;
var searchResults = personRepository.Search(
    p => p.Id == personId
);
Console.WriteLine($"Searching on personId '{personId}':");
foreach (var person in searchResults) {
    Console.WriteLine($" - {person}");
}

// Search method with an text-based expression
var expression = "(FirstName + LastName).Contains(\"oen\")";
searchResults = personRepository.Search(
    expression
);
Console.WriteLine($"Searching with expression '{expression}':");
foreach (var person in searchResults) {
    Console.WriteLine($" - {person}");
}

// Idea for the base "dummy" Search method strategy
// uses dynamic linq query syntax, but we'll need to differentiate it inside the api...
var searchInput = "Annelies";
expression = $"(FirstName.Trim() + LastName.Trim() + Id.ToString().Trim()).ToLower().Contains(\"{searchInput}\".Trim().ToLower())";
searchResults = personRepository.Search(
    expression
);
Console.WriteLine($"Searching with expression '{expression}':");
foreach (var person in searchResults) {
    Console.WriteLine($" - {person}");
}

3. Strategy & approach… it’s just a test / POC at the moment!

I was blown away by the power of Expressions and Dynamic Linq (parsing) – thus I wanted to share this code & ideas already. It’s far from finished and still needs to be fleshed out, to make it user friendly (frontend) – but at least the ideas, capabilities and tech for the backend are already present to make a flexible solution that caters both to users that want a quick search, and users that will want a (typed?) refined searchall in one searchbox.

17022049_10155053097151091_4004970426825369471_n

 

COM & Interop Hell: To Excel or to EPPLUS?

business-intelligence-that-rescues-you-from-excell-hellScrolling through a lot of questions on StackOverflow, CodeProject and other sites I often come across questions like how do I create an ExcelSheet, or how to close Excel after saving a document… Most of these questions stem from the problems of working with the COM/Interop model of EXCEL. It’s dirty, it’s messy and resource intensive.

But there is an alternative: a project called EPPLUS, which has several benefits.

  • You don’t need a local Excel copy installed on the machine you’re running your code. Great for server work, or when you have users that only have Office365 or no locally installed Excel on their machine!
  • It uses a simplified API model without compromising powerful features as it can: from basic reading/writing to cells and ranges, coloring, adding worksheets, saving to adding complex object, images and a lot more… The official website sums up that it supports:
    • Cell Ranges, Cell styling (Border, Color, Fill, Font, Number, Alignments)
    • Charts, Pivot tables, tables
    • Pictures, Shapes
    • Comments
    • Protection, Encryption
    • Data validation
    • Conditional formatting
    • VBA
    • Formula calculation
    • Many more…
  • Did I already mention simplified API, what I really mean is you don’t have to bother interfacing with the EXCEL COM API, and manage the different lifecycles, or write your own custom wrapper… In EPPLUS all objects that need to be closed and use resources that need to be cleaned up implement IDisposable, and a ssuch you can write code inside a using block, e.g.:
  • Free, open source & it’s actively maintained, the last commit on time of writing was 2017 February 17th on their codeplex repository.

At least give it a try (nugetcodeplex) and look into it. It’ll save you headaches from COMplicated API calls to the Excel Interop ;-)…- pun inteded.

Data Driven Tests with xUnit.net

Writing S.O.L.I.D. code doesn’t only apply to your domain model, UI code etc.. – but in fact also your test code!

In xUnit writing tests usually includes writing a bunch of methods with the attribute [Fact]. Yet when one wants to test the same Fact (or test) on a set of data, it can get messy… And thus, during my travels through automated testing I used to create a test and within the test a foreach loop over a given set of data, but that was before I discovered [Theories] in xUnit!

Facts are tests which are always true. They test invariant conditions.
Theories are tests which are only true for a particular set of data.

I was confused by those definitions at first. But, to me, it boils down to this: one can group a certain set of data and run it through the same test.

So where’s the benefit then, as I used to to do it with an inline foreach loop… Ok, so, theories have the added benefit that you can upfront declare, reuse and extend that data – and when the tests run, the testrunner will explicitely show results for each data item that was run through the test. This makes it easy to track which “case” (data) made the test fail – in stead of relying on an inline foreach-loop inside your traditional [Fact] test. Which results in (re)usable tests and reusable parts of code in your tests and easier trackability as mentioned before.

Last but not least, you can load the data inline, from Excel, from a Member (e.g. a method or property)…

For a more in depth overview, check this blogpost by Pankaj K: http://ikeptwalking.com/writing-data-driven-tests-using-xunit/.