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:
- Don’t want the bloat and overhead of Entity Framework,
- Want a fast ORM,
- Like the ability to use type safe linq and don’t what to hand code SQL statements,
- 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.