RepoDb: .Net Lightweight ORM Library Extreme Performance

What is RepoDb?

A dynamic, lightweight, and fast repository-based .Net ORM Library.

Package: https://www.nuget.org/packages/RepoDb
Project: https://github.com/RepoDb/RepoDb
Documentation: https://repodb.readthedocs.io/en/latest/

RepoDb v1.5.2 Result:

Individual Fetches:

Set Fetches:

RepoDb Performance

Last July 2018, I have posted an initial thread for RepoDb at Reddit and claiming that our library is the fastest one. The thread can be found here (https://www.reddit.com/r/csharp/comments/8y5pm3/repodb_a_very_fast_lightweight_orm_and_has_the/).

Many redditors commented and exchanged words with us about the library, specially with its performance, stability, purpose, features, syntax, differentiator etc etc.

We know that our IL is very fast, and that's true that RepoDb was the fastest in a big-mapping objects (of like 1 million rows). However, the community suggested to test the library using the existing performance bench(ers) that is commonly used by the community to actually test the performance of the ORM library.

With this, we used the benchmarker tool of FransBouma to test how's the performance of our library when compared to other.

Initial Performance Test Result with FransBouma's Tool

It was a shame to us claiming that RepoDb was the fastest ORM .Net Library. It was personally my fault for not executing the proper benchmarking when it comes to performance before it to everyone.

The reason why RepoDb was slow in the result above, was because the performance benchmark tool are using the iterative ways to compare the performance of every ORM. During our development, we never considered this approach.

The version of RepoDb by this time was v1.2.0.

Improving the Performance of IL

First, we analyze the cause of the performance flaws, whether it is the IL or the actual reflection procedure we had. In the beginning, I saw that I did not cache the IL statically, though I am caching it in as per-call basis.

With this, we first cache the IL statically by adding this logic.

First logic:

After logic:

Code Level:

Created a new class named DelegateCache.

public static class DelegateCache

{

    ...

}

Actual class can be found here  (https://github.com/RepoDb/RepoDb/blob/master/RepoDb/RepoDb/Reflection/DelegateCache.cs).

In our class DataReaderConverter, we used the the newly created class above to get the corresponding delegate for our data reader's (it is a pre-compiled IL-written delegate).

The approach above significantly improve the performance of RepoDb, however, we still have the flaws when it comes to memory usage. We are aware that we are heavy with the C# reflection.

Improving the Performance of Reflection

Secondly, we targetted to cache the reflected objects. We aim to make sure that we only call the typeof(Entity).GetProperties() once all throughout the lifetime of the library, per class level.

What we did is we introduce a class named PropertyCache to cache the call per class. Secondly, we added class named ClassExpression to pre-compile the GetProperties() operation via Expression Lambda so the next time we call it, it is already compiled.

    public static class PropertyCache
    {
        public static IEnumerable<ClassProperty> Get<TEntity>(Command command = Command.None)
            where TEntity : class
        {
            ...
        }
    }

We also created a class named ClassProperty that will contain a PropertyInfo object and necessary properties and methods to cache the definition of the property. We implemented the IEquatable<ClassProperty> to make sure that the collection objects can maximize the performance of the comparison.

    public class ClassProperty : IEquatable<ClassProperty>
    {
        ...
    }

Here is our way on simply caching the definition at instance level.

Notice the checking of m_isPrimaryAttributeWasSet variable, if this is set to true already, this means that the call into this method is done already, even the result to the m_primaryAttribute property is null.

We did the same to other definition methods all throughout the class. The actual class can be found here (https://github.com/RepoDb/RepoDb/blob/master/RepoDb/RepoDb/ClassProperty.cs).

And since we know that the call to GetProperties() will only happen once per class after we defined the PropertyCache class, then we are sure that the memory will be minimize here, as we have already removed the recurrent operation on this reflection approach.

We are all set already with the implementation above, however, this was still not enough until we cache the actual activity of the caller (actual project that references RepoDb). With this, we came up an idea to cache the command text.

Caching the CommandTexts

As we know that caching the outside calls would improve a lot the performance of the library as it would actually bypass all the operations we have mentioned above (earlier on this blog).

With this, we first implemented the requests classes as you see below.

• QueryRequest for Query
• InsertRequest for Insert
• DeleteRequest for Delete
• UpdateRequest for Update
• etc

The classes can be found here (https://github.com/RepoDb/RepoDb/tree/master/RepoDb/RepoDb/Requests).

In every class defined above, it accepts all the parameters the outside calls has in placed. This is to make sure that we are using the passed-values as a key to the uniqueness of the command texts that we are going to cache.

Identifying the Differences of the Parameter Values

We used to override the GetHashCode(), Equals() and implemented the IEquatable<T> interface to override the equality comparer of the following classes.

• All Request Classses
• ClassProperty
• QueryField
• Field
• Parameter
• QueryGroup

It enable us to identify and define the correct equality of the object (internally to RepoDb only).

Inside the library, we forced the equality, let's say the FieldA with name equals to "Name" is equal to the instance of FieldB with name equals "Name" and so forth. The logic is very simple with below's code.

    public override int GetHashCode()

    {

return Name.GetHashCode();

    }

    public override bool Equals(object obj)

    {

return GetHashCode() == obj?.GetHashCode();

    }

    public bool Equals(Field other)

    {

return GetHashCode() == other?.GetHashCode();

    }

    public static bool operator ==(Field objA, Field objB)

    {

if (ReferenceEquals(null, objA))

{

return ReferenceEquals(null, objB);

}

return objA?.GetHashCode() == objB?.GetHashCode();

    }

    public static bool operator !=(Field objA, Field objB)

    {

return (objA == objB) == false;

    }

The actual class can be found here (https://github.com/RepoDb/RepoDb/blob/master/RepoDb/RepoDb/Field.cs).

Caching Process for CommandText

Lastly, we introduced a class named CommandTextCache that holds the cached command text of the caller. See below the implementation of one of the method.

internal static class CommandTextCache

{

    private static readonly ConcurrentDictionary<BaseRequest, string> m_cache = new ConcurrentDictionary<BaseRequest, string>();

    public static string GetBatchQueryText<TEntity>(BatchQueryRequest request)

            where TEntity : class

    {

        var commandText = (string)null;

        if (m_cache.TryGetValue(request, out commandText) == false)

        {

            commandText = <codes to get the BatchQuery command text>;

            m_cache.TryAdd(request, commandText);

        }

        return commandText;

    }

}

The actual class implementation can be found here (https://github.com/RepoDb/RepoDb/blob/master/RepoDb/RepoDb/CommandTextCache.cs).

Let us say, somebody tried to call the repository's Query method as below.

using (var repository = new DbRepository<SqlConnection>(connectionString))
{

    repository.Query<Person>(new { Id = 10220 });
}

The suppose command text is below.

SELECT [Id], [Name], [Address], [DateOfBirth], [DateInsertedUtc], [LastUpdatedUtc] FROM [dbo].[Person];

Inside RepoDb, the method repository Query method has a created a new QueryRequest object with the parameters defined by the caller. In this case is (new { Id = 10220 }).

Then we simply call the CommandText.GetQueryText(queryRequest) get the cached command text.

RepoDb Final Results

There is 2 way of calling the operations in RepoDb, persistent connection and with non-persistent connection. There is 2 way as well on how to do the query, object-based and raw-sql based.

The result below is only for RawSql approach as we have never injected the Object-Based approach. (Note: RawSql is always faster than the Object-Based). This result was personally executed by FransBouma on their Test Environment (with Release binaries version).

Individual fetches:

Set fetches:

The results can be found here (https://github.com/RepoDb/RawDataAccessBencher/tree/master/Results).

The version of RepoDb by this time is v1.5.3.

Kindly share your thoughts, comments and inputs, do not forget to tag me if you would like an immediate response. Thank you for reading this blog!

Execute SQL Server Scripts in C#

With this tutorial we will guide you how to execute SQL Server scripts from C#.Net. This topic is more about SMO or SQL Server Management Objects.

For your reference regarding SMO, please visit Microsoft documentation.

What is SQL Server Management Object?

The SQL Server Management Object is a set of API developed by Microsoft so that any object manipulations in SQL Server can also be done in the client. This allow other developer to develop more a dynamic Query or Class generator architecture.

Let us start with the set of procedure below.

First, in your C# project, add a reference to the list of DLL below.

• Microsoft.SqlServer.Management.Sdk.Sfc
• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.SmoExtended
• Microsoft.SqlServer.SqlEnum

Folder Location: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\

We will most likely only use 2 binary there (just add other for your future development).

For you to make your SQL scripts runnable in the client, you should test it first in the SQL Management Studio whether there are no syntax error existed. If you feel that the script is right then you're ready to go with the client manipulation.

Stored Procedure

Suppose you have a database named Northwind and you have a table named User (userid, name, email, createddate); then we will create a sample stored procedure for that table.

With our sample table User, we will create a script to get the current user based on the UserID parameter. See below our sample script.

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[GetUser_sp]

(

      @UserID BIGINT

)

AS

BEGIN

      SELECT [UserID]

      , [Name]

      , [Email]

      , [CreatedDate]

      FROM [dbo].[User]

      WHERE ([UserID] = @UserID);

END

Ensure that the script above is running successfully in the SQL Management Studio. If you found any problem or error (script problem or syntax error) then fix it before executing it in the client.

.NET SMO Execution

We will now guide you how to execute it in the client. Now, go back to our C# project solution and do some code snippet.

First add a reference to the namespaces Microsoft.SqlServer.Management.Smo and Microsoft.SqlServer.Management.Common to your class above. See below.

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

Then, create a SqlConnection object that connects to your database. See below.

using (var connection = new SqlConnection(this.ConnectionString))

{

   

}

Inside the using block, create a Server and Database object. See below our codes.

using (var connection = new SqlConnection(this.ConnectionString))

{

    var server = new Server(new ServerConnection(connection));

    var database = server.Databases[connection.Database];

}

In the Server object ConnectionContext property, we need to set the property AutoDisconnectMode to NoAutoDisconnect for it to not disconnect when there are existing pooling operation in the database.

After that, call the Connect method to connect on the server and then call the ExecuteNonQuery method passing the string of our SQL Scripts. Please make sure to disconnect the connection once executed.

Now, our new code is below.

using (var connection = new SqlConnection(this.ConnectionString))

{

    var server = new Server(new ServerConnection(connection));

    var database = server.Databases[connection.Database];

    server.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect;

    server.ConnectionContext.Connect();

    server.ConnectionContext.ExecuteNonQuery("SQL SCRIPTS HERE");

    server.ConnectionContext.Disconnect();

}

Note: If you are running the 4.0 version of .NET and if you however encountered an exception regarding version compatibility. You need to support the 2.0 version of .NET during start up. To do this, you have to modify some settings from your config file. See below.

<?xml version="1.0"?>

<configuration>

  <startup useLegacyV2RuntimeActivationPolicy="true">

    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>

  </startup>

</configuration>

The useLegacyV2RuntimeActivationPolicy will do the trick.

That's all. Have a happy coding.

Creating an Attribute in C#

In C#, attribute is one of the common declarative technique that most of us is using into our application. Usually, we use the attribute to tag or flag a class, method or property in the form of object base recognition. It also simplify our determination in every object.

Furthermore, in C# there are lots of pre-defined attribute that we can embed into our application. One common sample is SerializableAttribute which flag/tag our class to participate in serialization/deserialization process. If however you would like to extend the attribute functionality and define your own, you can extend/inherit the attribute class from System namespace.

Please visit C# Flags Attribute blog before you continue. The reason is to let you explore the enumeration named Privilege.

Now, we are expecting that you already explore the Privilege enumeration form mentioned  blog above.

Let say for example you would like to define a scenario that gives your code-level to determine whether a class (or a stub object) can be deleted from database. See below our sample custom attribute.

public class SecurityAccess : Attribute

{

    public SecurityAccess()

    {

    }

    public SecurityAccess(Privilege privilege)

        : this()

    {

        this.Privilege = privilege;

    }

    public Privilege Privilege

    {

        get;

        set;

    }

}

What the class SecurityAccess is doing above is just simply handling the value for the Privilege enumeration. This property defines whether the class that declare this attribute has an enough level of privilege before doing necessary action in the database.

How to use custom attribute?

Now, in this section, we will going to guide you how to use the attribute we created above.

Suppose we have two class that inherits from one object. Let's call them Animal, and the two other class in Cat and Dog. See below implementation.

public class Animal

{

    public bool IsDeletable

    {

        get;

        protected set;

    }

}

public class Cat : Animal

{

    public Cat()

    {

    }

}

public class Dog : Animal

{

    public Dog()

    {

    }

}

The Dog and Cat is an Animal base on our implementation. With the use of our custom attribute named SecurityPrivilege, we can declare each privilege in every class. Suppose we would like different breed of Cat cannot be deleted from our database, then we can declare our custom attribute like below.

[SecurityAccess(Privilege = Privilege.Read | Privilege.Write | Privilege.Create)]

public class Cat : Animal

{

    public Cat()

    {

    }

}

And the Dog breed is deletable. See below.

[SecurityAccess(Privilege = Privilege.Read | Privilege.Write | Privilege.Create | Privilege.Delete)]

public class Dog : Animal

{

    public Dog()

    {

    }

}

How to access an attribute value declared in the class/method and other object?

Now, in this section, we will going to guide you how to access the value of the attribute per class level.

In C#, the only way to get the attribute value is to use Reflection. First, we need to get the current type of the object, from there check if there are declared custom attributes and determine the attribute type. If its match the attribute we're looking for, then that is the custom attribute we had created. See below the process.

protected T GetCustomAttribute<T>(object @object)

{

    if (!object.ReferenceEquals(@object, null))

    {

        var type = @object.GetType();

        var attrs = type.GetCustomAttributes(false);

        if (attrs.Length > 0)

        {

            foreach (var attr in attrs)

            {

                if (attr is T)

                {

                    return (T)(object)attr;

                }

            }

        }

    }

    return default(T);

}

What the code is doing above is to simply return the embedded attribute in the object you passed in the parameter named @object. If there is no attribute found, then it will return null. Best to place this code in the Animal base class so both derive class can use it.

And now, in the construction of Dog and Cat class, you should call the method directly from there.

public Dog()

{

    var attr = this.GetCustomAttribute<SecurityAccess>(this);

    base.IsDeletable = (attr.Privilege & Privilege.Delete) == Privilege.Delete;

}

public Cat()

{

    var attr = this.GetCustomAttribute<SecurityAccess>(this);

    base.IsDeletable = (attr.Privilege & Privilege.Delete) == Privilege.Delete;

}

Now, in the base class IsDeletable property, we then set it depends on the privilege level we declared on the class.

Please note that we can override the attribute value declared in the base class into derive class. So if you want that the Siamese Cat breed be deletable, you can declare your own SecurityAccess attribute in Siamese class.

That's all about this blog. Please follow us so you will get more interesting topics soon.

Please visit Microsoft documentation for further details.