linq2db

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:

…

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:

…

In this example we configured only three properties and one association. We let Linq To DB to infer all other properties as columns with same name as property.

To use your MappingSchema instance you should pass it DataConnection or DataContext constructor:

var options = new DataOptions()
    .UseSqlServer(@"Server=.\;Database=Northwind;Trusted_Connection=True;")
    .UseMappingSchema(myFluentMappings);

var db = new DataConnection(option);

Inferred Configuration

This approach involves no attributes at all. In this case Linq To DB will use POCO's name as table name and property names as column names (with exact same casing, which could be important for case-sensitive databases). This might seem to be convenient, but there are some restrictions:

• Linq To DB will not infer primary key even if class has property called ID;
• it will not infer nullability of reference types if you don't use nullable reference types annotations;
• associations will not be automatically configured.

using System;

public class Product
{
  public int    ProductID { get; set; }

  public string Name      { get; set; }

  public int    VendorID  { get; set; }

  public Vendor Vendor    { get; set; }

  // ... other columns ...
}

This way Linq To DB will auto-configure Product class to map to Product table with fields ProductID, Name, and VendorID. POCO will not get ProductID property treated as primary key. And there will be no association with Vendor.

This approach is not generally recommended.

DataConnection class

At this point LINQ to DB doesn't know how to connect to our database or which POCOs go with what database. All this mapping is done through a DataConnection class:

public class DbNorthwind : LinqToDB.Data.DataConnection
{
  public DbNorthwind() : base("Northwind") { }

  public ITable<Product>  Product  => this.GetTable<Product>();
  public ITable<Category> Category => this.GetTable<Category>();

  // ... other tables ...
}

We call the base constructor with the "Northwind" parameter. This parameter (called configuration name) has to match the name="Northwind" we defined above as name of our connection string. We also added convenience properties for Product and Category mapping classes to write LINQ queries.

And now let's get some data:

using LinqToDB;

public static List<Product> GetProducts()
{
  using var db = new DbNorthwind();

  var query = from p in db.Product
                where p.ProductID > 25
                orderby p.Name descending
                select p;

  return query.ToList();
}

Make sure you always wrap your DataConnection class (in our case DbNorthwind) in a using statement. This is required for proper resource management, like releasing the database connections back into the pool (more details).

Queries

Selecting Columns

Most times we get the entire row from the database:

from p in db.Product
where p.ProductID == 5
select p;

However, sometimes getting all the fields is too wasteful so we want only certain fields, but still use our POCOs; something that is challenging for libraries that rely on object tracking, like LINQ to SQL.

from p in db.Product
orderby p.Name descending
select new Product
{
  Name = p.Name
};

Composing queries

Rather than concatenating strings we can 'compose' LINQ expressions. In the example below the final SQL will be different if onlyActive is true or false, or if searchFor is not null.

public static Product[] GetProducts(bool onlyActive, string searchFor)
{
  using var db = new DbNorthwind();
  var products = from p in db.Product 
                   select p;

  if (onlyActive)
  {
    products = from p in products 
               where !p.Discontinued 
               select p;
  }

  if (searchFor != null)
  {
    products = from p in products 
                 where p.Name.Contains(searchFor) 
                 select p;
  }

  return products.ToArray();
}

Paging

A lot of times we need to write code that returns only a subset of the entire dataset. We expand on the previous example to show what a product search function could look like.

Keep in mind that the code below will query the database twice. Once to find out the total number of records, something that is required by many paging controls, and once to return the actual data.

public static List<Product> Search(