LINQ to DB

LINQ to DB is the fastest LINQ database access library offering a simple, light, fast, and type-safe layer between your POCOs and your database.

Architecturally it is one step above micro-ORMs like Dapper, Massive, or PetaPoco, in that you work with LINQ expressions, not with magic strings, while maintaining a thin abstraction layer between your code and the database. Your queries are checked by the C# compiler and allow for easy refactoring.

However, it's not as heavy as LINQ to SQL or Entity Framework. There is no change-tracking, so you have to manage that yourself, but on the positive side you get more control and faster access to your data.

In other words LINQ to DB is type-safe SQL.

LINQ to DB also very nice for F# developers (see Tests/FSharp and Source/LinqToDB.FSharp project for details).

Development version nugets feeds (how to use)

Standout Features

• Rich Querying API:
  • Explicit Join Syntax (In addition to standard LINQ join syntax)
  • CTE Support
  • Bulk Copy/Insert
  • Window/Analytic Functions
  • Merge API
• Extensibility:
  • Ability to Map Custom SQL to Static Functions

See Github.io documentation for more details.

Code examples and demos can be found here or in tests.

Release notes page.

Related Linq To DB and 3rd-party projects

• linq2db.EntityFrameworkCore (adds support for linq2db functionality in EF.Core projects)
• LinqToDB.Identity - ASP.NET Core Identity provider using Linq To DB
• LINQPad Driver
• DB2 iSeries Provider
• ASP.NET Core Template
• PostGIS extensions for linq2db

Notable open-source users:

• nopCommerce - popular open-source e-commerce solution
• OdataToEntity - library to create OData service from database context
• SunEngine - site, blog and forum engine

Unmantained projects:

• IdentityServer4.LinqToDB - IdentityServer4 persistence layer using Linq To DB

Configuring connection strings

Passing Into Constructor

You can simply pass connection string into DataConnection or DataContext constructor using DataOptions class.

Minimal configuration example:

var db = new DataConnection(
  new DataOptions()
    .UseSqlServer(@"Server=.\;Database=Northwind;Trusted_Connection=True;"));

Use connection configuration action to setup SqlClient-specific authentication token:

var options = new DataOptions()
  .UseSqlServer(connectionString, SqlServerVersion.v2017, SqlServerProvider.MicrosoftDataSqlClient)
  .UseBeforeConnectionOpened(cn =>
    {
        ((SqlConnection)cn).AccessToken = accessToken;
    });

// pass configured options to data context constructor
var dc = new DataContext(options);

[!TIP] There are a lot of configuration methods on DataOptions you can use.

[!TIP] It is recommended to create configured DataOptions instance once and use it everywhere. E.g. you can register it in your DI container.

Using Config File (.NET Framework)

In your web.config or app.config make sure you have a connection string (check this file for supported providers):

<connectionStrings>
  <add name="Northwind" 
    connectionString = "Server=.\;Database=Northwind;Trusted_Connection=True;" 
    providerName     = "SqlServer" />
</connectionStrings>

Using Connection String Settings Provider

Alternatively, you can implement custom settings provider with ILinqToDBSettings interface, for example:

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 MySettings : ILinqToDBSettings
{
    public IEnumerable<IDataProviderSettings> DataProviders
        => Enumerable.Empty<IDataProviderSettings>();

    public string DefaultConfiguration => "SqlServer";
    public string DefaultDataProvider  => "SqlServer";

    public IEnumerable<IConnectionStringSettings> ConnectionStrings
    {
        get
        {
            // note that you can return multiple ConnectionStringSettings instances here
            yield return
                new ConnectionStringSettings
                {
                    Name             = "Northwind",
                    ProviderName     = ProviderName.SqlServer,
                    ConnectionString =
                        @"Server=.\;Database=Northwind;Trusted_Connection=True;"
                };
        }
    }
}

And later just set on program startup before the first query is done (Startup.cs for example):

DataConnection.DefaultSettings = new MySettings();

Use with ASP.NET Core and Dependency Injection

See article.

Define POCO class

You can generate POCO classes from your database using linq2db.cli dotnet tool.

Alternatively, you can write them manually and map to database using mapping attributes or fluent mapping configuration. Also you can use POCO classes as-is without additional mappings if they use same naming for classes and properties as table and column names in database.

Configuration using mapping attributes

using System;
using LinqToDB.Mapping;

[Table("Products")]
public class Product
{
  [PrimaryKey, Identity]
  public int ProductID { get; set; }

  [Column("ProductName"), NotNull]
  public string Name { get; set; }

  [Column]
  public int VendorID { get; set; }

  [Association(ThisKey = nameof(VendorID), OtherKey=nameof(Vendor.ID))]
  public Vendor Vendor { get; set; }

  // ... other columns ...
}

This approach involves attributes on all properties that should be mapped. This way lets you to configure all possible things linq2db ever supports. There is one thing to mention: if you add at least one attribute into POCO, all other properties should also have attributes, otherwise they will be ignored:

using System;
using LinqToDB.Mapping;

[Table("Products")]
public class Product
{
  [PrimaryKey, Identity]
  public int ProductID { get; set; }

  // Property `Name` will be ignored as it lacks `Column` attibute.
  public string Name { get; set; }
}

Fluent Configuration

This method lets you configure your mapping dynamically at runtime. Furthermore, it lets you to have several different configurations if you need so. You will get all configuration abilities available with attribute configuration. These two approaches are interchangeable in their abilities. This kind of configuration is done through the class MappingSchema.

With Fluent approach you can configure only things that require it explicitly. All other properties will be inferred by linq2db:

// IMPORTANT: configure mapping schema instance only once
// and use it with all your connections that need those mappings
// Never create new mapping schema for each connection as
// it will seriously harm performance
var myFluentMappings = new MappingSchema();
var builder       = new FluentMappingBuilder(mappingSchema);

builder.Entity<Product>()
    .HasTableName("Products")
    .HasSchemaName("dbo")
    .HasIdentity(x => x.ProductID)
    .HasPrimaryKey(x => x.ProductID)
    .Ignore(x => x.SomeNonDbProperty)
    .Property(x => x.TimeStamp)
        .HasSkipOnInsert()
        .HasSkipOnUpdate()
    .Association(x => x.Vendor, x => x.VendorID, x => x.VendorID, canBeNull: false)
    ;

//... other mapping configurations