SisoDb v11.4.0 – with new named queries

So, the versions of SisoDb keeps pumping out and in this post I will cover a new feature introduced in v11.4.0. You can always have a look at the release notes to get full details of what has changed.

Refactoring friendly Named Queries

A named query in SisoDb is the invocation of a stored procedure returning JSON. As of v11.4.0 you can let SisoDb generate them as well as invoke them using lambda expressions. Lets have a look.

Normal Query
This is a basic query not using stored procedures. It can be consumed in two variations, via Session or via UseOnceTo.

using (var session = database.BeginSession())
{
    return session.Query<Customer>().Where(c => 
            c.CustomerNo >= customerNoFrom 
            && c.CustomerNo <= customerNoTo 
            && c.DeliveryAddress.Street == "The delivery street #544").ToArray();
}
return database.UseOnceTo().Query<Customer>().Where(c => 
        c.CustomerNo >= customerNoFrom 
        && c.CustomerNo <= customerNoTo 
        && c.DeliveryAddress.Street == "The delivery street #544").ToArray();

Named Query
A named query is represented by the INamedQuery interface. It’s very slim, and there’s one default implemantation of it, namely “NamedQuery”.

public interface INamedQuery
{
    string Name { get; }
    IEnumerable<IDacParameter> Parameters { get; }
    void Add(params IDacParameter[] parameters);
}

Consuming them means magic strings representing e.g properties of your models. This could of course be avoided if you implement/inherit any of these types and create a typed named query. But then you have a query that is detached from your model but implicitly dependent on any changes in it. Is there a better way? Sure!

Nicer Named Queries
First, we will let SisoDb create the stored procedure for us, using our model and lambda expressions.

using (var session = database.BeginSession())
{
    session.Advanced.UpsertNamedQuery<Customer>("CustomersViaSP", builder => 
        builder.Where(c =>
            c.CustomerNo >= customerNoFrom
            && c.CustomerNo <= customerNoTo
            && c.DeliveryAddress.Street == "The delivery street #544"));
}

This little snippet will create the stored procedure for us in the database. If the stored procedure exists, it will be overwritten. Now, lets consume it, and again using our model and lambda expressions:

using (var session = database.BeginSession())
{
    return session.Advanced.NamedQuery<Customer>("CustomersViaSP", c => 
        c.CustomerNo >= customerNoFrom
        && c.CustomerNo <= customerNoTo
        && c.DeliveryAddress.Street == "The delivery street #544").ToArray();
}

Does this have any impact on the performance of the query? Yes! The case below contains a rather small set of Customers. Only 5000 (read more about performance here) First, there’s two rows representing the manual query. Then there’s two rows representing the stored procedure. The first row in each pair takes a penalty hit, cause it’s the first time it’s executed. This is why your application should hold ONE LONG LIVED INSTANCE OF the ISisoDatabase.

The result: 0.0025s vs 0.0006s

That’s all about the new Named queries.

If any of the links above doesn’t work. The documentation is duplicated on GitHub.

//Daniel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s