Click here to Skip to main content
14,422,439 members

noDB

Rate this:
5.00 (10 votes)
Please Sign up or sign in to vote.
5.00 (10 votes)
11 Jan 2020CPOL
Using list of objects, LINQ and SQL like string query instead of a database with fastJSON serializer

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

Image 1

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

History

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mehdi Gholam
Architect -
United Kingdom United Kingdom
Mehdi first started programming when he was 8 on BBC+128k machine in 6512 processor language, after various hardware and software changes he eventually came across .net and c# which he has been using since v1.0.
He is formally educated as a system analyst Industrial engineer, but his programming passion continues.

* Mehdi is the 5th person to get 6 out of 7 Platinum's on Code-Project (13th Jan'12)
* Mehdi is the 3rd person to get 7 out of 7 Platinum's on Code-Project (26th Aug'16)

Comments and Discussions

 
QuestionUsed dynamic linq a lot on one project Pin
Sacha Barber6-Jan-20 11:54
mvaSacha Barber6-Jan-20 11:54 
QuestionNice demonstration of dynamic Linq Pin
Marc Clifton3-Jan-20 3:21
communityengineerMarc Clifton3-Jan-20 3:21 
AnswerRe: Nice demonstration of dynamic Linq Pin
Mehdi Gholam3-Jan-20 3:36
MemberMehdi Gholam3-Jan-20 3:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 2 Jan 2020

Tagged as

Stats

5.9K views
209 downloads
12 bookmarked