Monday, July 27, 2015

Getting started with the SQL Server Compact and other providers for Entity Framework 7 beta 6

In connection with the recent release of Entity Framework 7 (EF7) beta 6 on NuGet, I have contributed two SQL Server Compact providers that work with this beta release (in close collaboration with and with great support from the EF7 team.)

Notice that the general “EntityFramework” package for EF7 is no longer available. Going forward you always install the desired provider packages as needed. For example EntityFramework.SqlServer

The SQL Server Compact providers

The SQL Server Compact providers are fully featured providers that support all required Core APIs (maybe with the exception of provider specific metadata). This includes support for returning IDENTITY values after INSERTs, Migrations, database object creation, and modelling via both the fluent API and Data Annotations. It also includes support the for Reverse-Engineer command, allowing you to create an EF7 model from an existing database.

For a walkthrough of how to get started with the SQL Server Compact providers, see the wiki article here. Initial Entity Framework 7 documentation is available here (a work in progress). As always feedback is very much appreciated, suggest you use the Github issue tracker for bug reports, feature suggestions or even Pull Request discussions and submissions!

Other providers

With the current EF7 beta, the following providers are available:

EntityFramework.SqlServer Provider for SQL Server/Azure SQL Database - supplied by Microsoft. This provider will be able to take advantage of the .NET Core support for System.Data.SqlClient on Unix and Mac. Currently only SQL Server 2012 and later is supported, but support for SQL Server 2008 is planned

EntityFramework.Sqlite Provider for SQLite, mainly for devices (tablet and phone) - supplied by Microsoft. Will also work with Xamarin (Andriod and IOs)

EntityFramework.InMemory In-memory provider for use with testing, supplied by Microsoft

All the providers above are hosted here on Github.

EntityFramework.Npgsql Provider for PostgressSQL - also works on Mono. Supplied by Shay Rojansky and others (expected to be released with beta7) – Github

EntityFramework.SqlServerCompact40 Provider for SQL Server Compact 4.0, supplied by yours truly – Github

EntityFramework.SqlServerCompact35 Provider for SQL Server Compact 3.5 SP2, also supplied by yours truly – Github

The current plan is to release EF7 at the same time as ASP.NET 5, which is currently scheduled (no guarantees) to beginning of next year.

Monday, July 13, 2015

SQL Server Compact ADO.NET data access performance – part 3: SELECTs

In the third instalment in this series, I will demonstrate how to implement SELECTs using the two available APIs in the SQL Server Compact ADO.NET provider. Keep in mind that the “alternative” method of SELECTing requires a corresponding index, so it will on work for simple scenarios where you are looking up on the primary key, for example – luckily, this is quite a common scenario.

I will implement the following interface method, which finds a cacheelement by it’s ID:

CacheElement FindElementByKey(Guid key);

Let’s first look at the implementation of the classic T-SQL SELECT based method:

public CacheElement FindElementByKey(Guid key)
{
    using (var command = _connection.CreateCommand())
    {
        command.Parameters.Add("@key", SqlDbType.UniqueIdentifier).Value = key;
        command.CommandText = @"SELECT [Tag], [Value], [CreatedAt], [ExpirationAt]
            FROM CacheElement
            WHERE [Key] = @key;";
        using (var reader = command.ExecuteReader())
        {
            if (reader.Read())
            {
                var result = new CacheElement();
                result.Key = key;
                result.Tag = reader.GetValue(1) == DBNull.Value ? null : reader.GetString(1);
                result.Value = (byte[])reader[1];
                result.CreatedAt = reader.GetDateTime(2);
                result.ExpirationAt = reader.GetDateTime(3);
                return result;
            }
            else
            {
                return null;
            }
        }
    }
}

This implementation uses a standard T-SQL based parameterized SELECT query. Nothing new here.

Now let us have a closer look at the implementation using TableDirect mode:

public CacheElement FindElementByKey(Guid key)
{
    using (var command = _connection.CreateCommand())
    {
        command.CommandType = CommandType.TableDirect;
        command.CommandText = "CacheElement";
        command.IndexName = "PK_CacheElement";

        using (var reader = command.ExecuteReader())
        {
            reader.Seek(DbSeekOptions.FirstEqual, key);
            if (reader.Read())
            {
                var element = new CacheElement();
                element.Key = key;
                element.Tag = reader.GetValue(1) == DBNull.Value ? null : reader.GetString(1);
                element.Value = (byte[])reader.GetValue(2);
                element.CreatedAt = reader.GetDateTime(3);
                element.ExpirationAt = reader.GetDateTime(4);
                return element;
            }
        }
        return null;
    }
}

Notice the required differences: The CommandType is set to TableDirect, the CommandText is set to the name of the table we want to query, and the IndexName property is set to the name of the index to be used. The Seek command will force a call to read to find the first matching column matching the key value. Notice that the key parameter is an params array, allowing you to use multi-column indexes.

In terms of performance, the classic SELECT appears to be 3 times slower than the TableDirect Seek, but of course your mileage may vary.

You can download the sample code from here.