noDB

• Download SampleApp_src
• Download SampleApp_exe

Introduction

I have been using RaptorDB in production for a long time, and recently as a what if, I asked if I could do away with using a database engine for a project, but still have the same functionality. This idea came about with RealNews which uses no database at all and works great even with a lot of feed items.

With what I will explain below, I was able to add this feature to my app and via a configuration setting, seamlessly switch between RaptorDB and dynamic LINQ to objects with the functionality of sorting and paging staying the same for the user.

Obviously, this is not a solution for every case, and makes sense if your data is limited in size and can fit in memory.

Some uses include:

• caching data within an app for fast no round trip access
• small/medium data sized zero install apps
• user defined query capabilities behind a web API

What is Needed

Say you have a list of objects:

var list = new List<SalesInvoice>();

in code you can already do a lot with LINQ like:

var results = list.FindAll(x => x.Serial<100);

which works great at compile time, but what if the filter is a dynamic string you get from the user in a browser and your code is on the server behind an API.

Enter System.LINQ.Dynamic which is a bit of code from Microsoft which allows you to use strings in the Where() function.

With this, now you can do:

var result = list.Where("name.Contains(\"Peter\") and serial<100");

which is awesome, but I really like to write SQL like queries and not C# specific ones:

var result = list.Where("name = \"Peter\" and serial<100");

so tinkering in the System.LINQ.Dynamic is needed which is not for the faint hearted.

How It Works

Now System.LINQ.Dynamic is a complex thing and not easy to wrap your head around, but by debugging, I was able to find the place where the equality checking happens, and insert the code needed.

The code checks the left hand side of an equal for a string type and then builds an Expression to handle the Contains() checking of the right hand side.

Also to allow searching Guid types, it first uses ToString() then uses Contains() so you can search Guid like strings.

The Problem with Contains()

First, I used Contains() but quickly found out that it is case sensitive which is not what a user expects, so I had to write a case insensitive version with IndexOf(), essentially the following C# code in Expression syntax:

var bool = str.IndexOf("val", StringComparison.OrdinalIgnoreCase) >= 0 ; // yes/no "val" in str

The Code

Below is the code added and changed in Dynamic.cs in System.LINQ.Dynamic. The throw exception case I commented happened for some reason, but skipping it works fine.

// changed
void CheckAndPromoteOperands(Type signatures, string opName, 
                             ref Expression left, ref Expression right, int errorPos)
{
    Expression[] args = new Expression[] { left, right };
    MethodBase method;
    if (FindMethod(signatures, "F", false, args, out method) != 1)
        ;//  throw IncompatibleOperandsError(opName, left, right, errorPos); // MG
    left = args[0];
    right = args[1];
}
// added
Expression Contains(Expression left, Expression right)
{
    // if left string -> generate contains method
    Expression[] args = new Expression[] { 
             right, Expression.Constant(StringComparison.OrdinalIgnoreCase) };
    if (_indexof == null)
    {
        // using indexof() instead of contains() which is case sensitive
        FindMethod(typeof(string), "IndexOf", false, args, out MethodBase mb);
        _indexof = mb;
    }
    Expression ex = Expression.Call(left, (MethodInfo)_indexof, args);
    return Expression.GreaterThanOrEqual(ex, Expression.Constant(0));
}
// added
Expression NotContains(Expression left, Expression right)
{
    // if left string -> generate method
    Expression[] args = new Expression[] { right, Expression.Constant
                                           (StringComparison.OrdinalIgnoreCase) };
    if (_indexof == null)
    {
        // using indexof() instead of contains() which is case sensitive
        FindMethod(typeof(string), "IndexOf", false, args, out MethodBase mb);
        _indexof = mb;
    }
    Expression ex = Expression.Call(left, (MethodInfo)_indexof, args);
    return Expression.LessThan(ex, Expression.Constant(0));
}
// changed
Expression GenerateEqual(Expression left, Expression right)
{
    // MG
    if(left.Type == typeof(Guid))
    {
        FindMethod(typeof(Guid), "ToString", false, new Expression[] { }, out MethodBase ts);
        Expression tse = Expression.Call(left, (MethodInfo)ts, null);

        return Contains(tse, right);
    }

    if (left.Type == typeof(string))
        return Contains(left, right);
    else
        return Expression.Equal(left, right);
}
// changed
Expression GenerateNotEqual(Expression left, Expression right)
{   // MG
    if (left.Type == typeof(Guid))
    {
        FindMethod(typeof(Guid), "ToString", false, new Expression[] { }, out MethodBase ts);
        Expression tse = Expression.Call(left, (MethodInfo)ts, null);

        return NotContains(tse, right);
    }

    if (left.Type == typeof(string))
        return NotContains(left, right);
    else
        return Expression.NotEqual(left, right);
}

Cool Features

LINQ allows you to page data:

list.Where("serial<100").Skip(100).Take(10);

and it also allows you to sort data:

list.Where("serial<100").Orderby("name"); // ascending
list.Where("serial<100").Orderby("name desc"); // descending

as you can see, the property names are not case sensitive, which makes using it a joy.

Also, you can use:

• SQL like and or
• c# style && ||
• not equal != <>
• parenthesis
• properties in properties with dots

(name = "peter" && address = "hill") or (serial<100 and date.year=2000)

name != "peter" and address <> "hill"

The Sample App

To show the features, I created a sample app that generates 100,000 SalesInvoice objects with Faker.dll and allows you to query and show the results in a grid.

You can choose sample queries from the menu and tweak and change it with the text box. To run the query, just press enter while typing.

Other Possible Features

• In RaptorDB, columns which are full text searchable are broken up into words so you can do:

  name = "alice  bob" means (name contains "alice" and name contains "bob")
  name = "alice +bob" means (name contains "alice" or  name contains "bob")
  name = "alice -bob" means (name contains "alice" and name does not contain "bob")

• You can also use any other serializer like fastBinaryJSON which is faster loading and saving, or if your object structure is really simple things like protobuf or even fastCSV.
  • on a list with 13,586 items I tested, fastCSV took 68ms to load opposed to fastJSON's 230ms
• Handling adding and removing items in multi user environments, i.e., locking the list on access.

Appendix - Update 1

A problem I encountered was if you had null values in the properties you are querying for then the LINQ will just crash, to overcome this I changed the Contains() and NotContains() functions to check for this:

Expression NotContains(Expression left, Expression right)
{
    // if left string -> generate method
    // FIX : if right has spaces -> generate multiple "and indexof()"
    Expression[] args = new Expression[] { right, Expression.Constant(StringComparison.OrdinalIgnoreCase) };
    if (_indexof == null)
    {
        // using indexof() instead of contains() which is case sensitive
        FindMethod(typeof(string), "IndexOf", false, args, out MethodBase mb);
        _indexof = mb;
    }
    // null checking left value
    Expression nn = Expression.Equal(Expression.Constant(null), left);
    Expression ex = Expression.Call(left, (MethodInfo)_indexof, args);
    return Expression.OrElse(nn, Expression.LessThan(ex, Expression.Constant(0)));
}

// MG
Expression Contains(Expression left, Expression right)
{
    // if left string -> generate method
    // FIX : if right has spaces -> generate multiple "and indexof()"
    Expression[] args = new Expression[] { right, Expression.Constant(StringComparison.OrdinalIgnoreCase) };
    if (_indexof == null)
    {
        // using indexof() instead of contains() which is case sensitive
        FindMethod(typeof(string), "IndexOf", false, args, out MethodBase mb);
        _indexof = mb;
    }
    // null checking left value
    Expression nn = Expression.NotEqual(Expression.Constant(null), left);
    Expression ex = Expression.Call(left, (MethodInfo)_indexof, args);
    return Expression.AndAlso(nn, Expression.GreaterThanOrEqual(ex, Expression.Constant(0)));
}

Previous Releases

• Download source - 59.3 KB
• Download EXE - 59 KB

History

• Initial release : 3^rd January, 2020
• Update 1 : 11th January 2020
  • null checking values