Linq2db with Fluent Mapping Part 1

By | September 13, 2018

Linq2Db With Fluent Mapping

This article is about using linq2db with fluent mapping. You can find Linq2db https://github.com/linq2db/linq2db.  Linq2Db is an ORM for C#.  The provided readme gives a great introduction.  If you:

  1. Don’t want the bloat and overhead of Entity Framework,
  2. Want a fast ORM,
  3. Like the ability to use type safe linq and don’t what to hand code SQL statements,
  4. Your happy with the small trade-off in performance to get number 3 (over a micro ORM like say Dapper)

then Linq2DB is the one for you.

But it seems that many use Linq2DB in database first fashion with attributes.  It appears that the creators originally designed Linq2Db  this way and there is a T4 template available to generate all of your POCOs complete with attributes from an existing database.  You can try this for yourself using the existing documentation.  However, while for many it is not a great deal, for a lot of people, including myself, like to separate the ORM mapping from the POCO objects.  There is support for this using linq2db with fluent mapping that appears to have evolved over time but it is not well documented, but I am hoping to change that here.

An Example Using Linq2DB With Fluent Mapping

I am going to develop an example using .Net Core and PostgreSQL and take you with me.  It will be a time tracking browser based application which is based on a simplified system I have developed for myself.

My Approach

Because Linq2Db is meant to be lightweight it doesn’t have things like Code-First or automatic db migrations.  Because of this my approach is to use FluentMigrator, developing the migrations in parallel with the POCO’s and linq2db with fluent mapping.  This isn’t code-first, but is a happy work around.  It also provides us with the ability to version control the database.

Quick jumps:

Lets Start

So we create our solution.  We want a library for the models (our POCOs), utility classes and the database migrations.  We then want a web application project.  I have a utility extension method called ToDB() that simply takes a the pascal case to underscores and lower-case.  This works better with some of the databases servers.  If you don’t make the field names lower-case with FluentMigrator,  FluentMigrateor creates the database fields as quoted.  This is important because with postgresql, if the fields are created quoted you always have to reference them quoted.

Create The First POCO

Let’s start in our models library project with a base model class:

public class BaseModel
{
    public long Id { get; set; }
}

and our client class.

public class Client : BaseModel
{
    public string Name { get; set; }
    public string PhoneNo { get; set; }
    public bool IsActive { get; set; }
}

Create the Migration

I will not go in to setting up fluent migrator too much here but this will be our migration up code which I will put into our DBMigration library project.  Don’t forget to add the FluentMigrator nuget package to the project and add the dependencies for the models library as well as the Utils library

using FluentMigrator;
using TimeTrackerLite.Models;
using TimeTrackerLite.Utils;

namespace TimeTrackerLite.DBMigration
{
    public class Migration001IinitDB : AutoReversingMigration
    {
        public override void Up()
        {
            CreateClientTable();
        }

        private void CreateClientTable()
        {
            Create
                .Table("Client")
                .WithColumn(nameof(Client.Id).ToDB()).AsInt64().NotNullable()
                    .PrimaryKey().Identity()
                .WithColumn(nameof(Client.Name).ToDB()).AsString(50).NotNullable()
                .WithColumn(nameof(Client.PhoneNo).ToDB()).AsString(25).NotNullable()
                .WithColumn(nameof(Client.IsActive).ToDB()).AsBoolean();
        }
    }
}

The Mapping

Now to set up the mapping.  We will put that in a separate class using the fluent mapping, keeping our POCO nice and clean.  Add the Linq2db nuget package to the  .net core web application.  This what the mapping class looks like.

using LinqToDB.Mapping;
using TimeTrackerLite.Models;
using TimeTrackerLite.Utils;

namespace TimeTrackerLite.Data
{
    public static class MappingLinq2Db
    {
        static MappingSchema _schema = null;

        public static MappingSchema Do()
        {
            if(_schema == null)
            {
                _schema = MappingSchema.Default;
                var mapper = _schema.GetFluentMappingBuilder();

                mapper
                    .Entity<Client>()
                    .HasTableName(nameof(Client).ToDB())
                    .Property(x => x.Id).HasColumnName(nameof(Client.Id).ToDB())
                        .IsIdentity().IsPrimaryKey()
                    .Property(x => x.Name).HasColumnName(nameof(Client.Name).ToDB())
                    .Property(x => x.PhoneNo).HasColumnName(nameof(Client.PhoneNo).ToDB())
                    .Property(x => x.IsActive).HasColumnName(nameof(Client.IsActive).ToDB());

            }
            return _schema;
        }
        
    }
}

Infrastructure

Now that we have our model and migration we need to set-up our data connection settings and auto migration code.  We will do that as per the recommendations on the linq2db github site for .net core and the fluent migration site and the advice regarding secrets.  Add the FluentMigrator, FluentMigrator.Runner and the postgresql Npgsql (or whatever database provider) nuget packages.   This is what For setting up the connection stuff I use the following classes:

namespace TimeTrackerLite.Data
{
    public class DBSettings
    {
        public string Server { get; set; }
        public string Database { get; set; }
        public int? Port { get; set; }
        public string DBUserName { get; set; }
        public string DBPassword { get; set; }
    }


}

This is a class that I use to store the configuration of the database for the application. You will see the use of this class in loading the settings later.  Then we have some helper extensions to convert these database settings to/from a database connection string.

using Npgsql;

namespace TimeTrackerLite.Data
{
    public static class DBSettingsConnectionStringPgsql
    {
        public static DBSettings ToPgsqlDBSettings(this string connString)
        {
            var builder = new NpgsqlConnectionStringBuilder(connString);
            return new DBSettings()
            {
                Server = builder.Host,
                Database = builder.Database,
                Port = builder.Port,
                DBUserName = builder.Username,
                DBPassword = builder.Password
            };
        }

        public static string ToPgsqlConnectionString(this DBSettings dbSettings)
        {
            var builder = new NpgsqlConnectionStringBuilder
            {
                Username = dbSettings.DBUserName,
                Host = dbSettings.Server,
                Password = dbSettings.DBPassword,
                Database = dbSettings.Database,
                CommandTimeout = 200,
                ConnectionIdleLifetime = 30,
                Pooling = true,
                KeepAlive = 30,
                TcpKeepAlive = true
            };
            if (dbSettings.Port != null)
                builder.Port = dbSettings.Port.Value;

            return builder.ToString();
        }
    }
}

Then our IConnectionStringSettings implementations as recommended by the Linq2DB readme.

using LinqToDB;
using LinqToDB.Configuration;
using System.Collections.Generic;
using System.Linq;

namespace TimeTrackerLite.Data
{
    public class ConnectionStringSettings : IConnectionStringSettings
    {
        public string ConnectionString { get; set; }
        public string Name { get; set; }
        public string ProviderName { get; set; }
        public bool IsGlobal => false;
    }

    public class DbConnectionSettings : ILinqToDBSettings
    {
        DBSettings _dbSettings;
        public DbConnectionSettings(DBSettings dbSettings)
        {
            _dbSettings = dbSettings;
        }

        public IEnumerable DataProviders => Enumerable.Empty();

        public string DefaultConfiguration => ProviderName.PostgreSQL;
        public string DefaultDataProvider => ProviderName.PostgreSQL;

        public IEnumerable ConnectionStrings
        {
            get
            {
                yield return
                    new ConnectionStringSettings
                    {
                        Name = "AppData",
                        ProviderName = ProviderName.PostgreSQL,
                        ConnectionString = _dbSettings.ToDBConnectionString()
                    };
            }
        }
    }
}

Modify the project wizard generated startup.cs file in the TimeTrackerLite .net core web application pages project:

using FluentMigrator.Runner;
using LinqToDB.Data;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using TimeTrackerLite.Data;

namespace TimeTrackerLite
{
    public class Startup
    {
        public Startup(IHostingEnvironment env, IConfiguration configuration)
        {
            Configuration = configuration;
            DBSettings = new DBSettings();
            if (env.IsDevelopment())
                Configuration.GetSection("DBSettingsDev").Bind(DBSettings);
            else
                Configuration.GetSection("DBSettings").Bind(DBSettings);
        }

        public IConfiguration Configuration { get; }
        public DBSettings DBSettings { get; private set; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            //Set up the fluent migrator for the dependency injector
            services.AddFluentMigratorCore()
            .ConfigureRunner(rb => rb
            .AddPostgres()
            // Set the connection string
            .WithGlobalConnectionString(DBSettings.ToDBConnectionString())
            // Define the assembly containing the migrations
            .ScanIn(typeof(Startup).Assembly).For.Migrations());

            services.AddMvc();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env, IMigrationRunner dbmigrator)
        {
            ConfigureDB(env);
            dbmigrator.MigrateUp();
            if (env.IsDevelopment())
            {
                app.UseBrowserLink();
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseExceptionHandler("/Error");
            }

            app.UseStaticFiles();

            app.UseMvc();
        }

        private void ConfigureDB(IHostingEnvironment env)
        {
            DataConnection.DefaultSettings = new DbConnectionSettings(DBSettings);
        }
    }
}

So now we have our basic infrastructure.  This gives you are primer to Linq2Db.  In the next article I’ll add add the crud pages, a task class and create an association. Until then,  happy coding.  You will find the full source on github.

My C# Journey

By | August 6, 2018

It has been a little while since I posted.  I though I’d present an overview on my programming c# journey over the last couple of years.  I have enjoyed using C# and use it most of the time now.  I have used it in a couple of paid commissioned projects and use it for my own projects I hope to sell.

As with anything new, progress seems slow and painful.  It can be fustrating when you are starting out with a new language, platform and environment, how slow it seems to accomplish anything because of the learning curve.  This is true of my c# journey.  This is complicated when you try out new tools and libraries for the various platforms and frameworks.

The paid commissioned projects along my c# journey I have done have been WPF projects.  Other stuff I have worked on is a Xamarin android project – a simple timer for starters.  I have other Xamarin projects on the go as well.  I have used sqlite and postgresql for the data storage.  I am also currently cutting my teeth on a web application using .net core razor pages.

Also, in this c# journey, some libraries I have used or played with which I will write about over time are MVVMLight, ReactiveX, ReactiveUI, AutoFac, XUnit, LiteDB, FluentValidation, FluentMigration, Dapper, Dapper Extensions, Dapper FastCrud, BrightstarDB and Linq2DB.

My favourite MVVM framework is ReactiveUI.  I like the reactive ui framework itself, but it is the reactive programming theory I like.  I will write about this later also.

My favourite database ORM library so far in the c# journey is Linq2DB.  It doesn’t have the overhead of entity framework, but allows you to use Linq.  I have only just begun to use it.  Documentation and information is hard to find but worth the dig.  I will write about what I have learnt with some how-tos in the future.

Category: C#

My Programming Career Crossroads: Switching From Delphi To C#

By | April 1, 2016

Delphi To C#

I find I am at a crossroads.  I have over 20 years of Delphi development experience.  Despite Embarcadero’s best effort, Delphi is slipping as far as popularity and to stay in the game I need to diversify.  I have been looking at C#.  It certainly has a number of things going for it.  So I have started moving from Delphi to C#. Learning a new language can be a bit daunting though when you have been stuck to one for so long.  But persistence and determination will pay off.

Been exploring for a couple of months and like what I see:

  • C# is object oriented.
  • C# has all of the reflection and run time type information and more that Delphi has.
  • Data binding in C# is far superior and easier to use, although I am still back with Delphi XE4 with that.  There is no having to create special adapters and treat them like a dataset.
  • The philosophy behind C# appears in some ways familiar to a Delphi programmer (not surprising give the history behind it).  For example the way the interfaces are declared and implemented in classes.
  • C# has drag and drop UI design capabilities that are not a lot unlike that of Delphi.
  • The community edition of visual studio only a download away it.  This is very attractive for a single professional developer.
  • Very large on line community presence of C# and .net .
  • Extensive documentation at least on line.
  • The availability of the Mono project for development of Linux and Mac OSX applications.
  • Along with Mono is the availability of Xamarin for mobile applications.
  • There is a significant amount of third party and open source libraries and frameworks.
  • The ability to implement design patterns are well supported.

The more I play with C# the more comfortable I am with.  I’ll talk a bit more of my learning and transition from Delphi to C# as time goes on in the hope someone may find it helpful and interesting.