Building Clean Repositories Using DbLinq and Unit Tests

DbLinq: A Beginner’s Guide to Fast .NET Data Access

What is DbLinq?

DbLinq is a lightweight LINQ-to-SQL provider for .NET that maps database tables to strongly typed classes, letting you write queries in C# (or VB.NET) using LINQ syntax. It aims to be fast, minimal, and easy to integrate into existing projects that need simple, direct access to relational data without the overhead of a full ORM.

Why choose DbLinq?

• Performance: Minimal abstraction means fewer runtime costs compared with heavier ORMs.
• Simplicity: Straightforward mapping and query patterns reduce cognitive overhead.
• Control: You work with SQL-like queries via LINQ while retaining closer control over generated SQL.
• Lightweight footprint: Ideal for small-to-medium projects or performance-critical paths.

Getting started — setup

1. Create or open a .NET project (recommended: .NET 6+).
2. Add the DbLinq package (or the project source) to your solution. If a NuGet package exists for your target framework, install it:

   Code
   Copy CodeCopied
   dotnet add package DbLinq 

3. Add a connection string to appsettings.json or your configuration source4. Create a data context class that inherits DbLinq’s base context (example names may vary by package):

csharp
Copy CodeCopied
public class AppDataContext : DbLinq.Data.DataContext
{
public AppDataContext(string connectionString) : base(connectionString) { }

    public DbLinq.Mapping.Table<User> Users => GetTable<User>();
    public DbLinq.Mapping.Table<Order> Orders => GetTable<Order>();
}

Define entity mappings

Annotate POCOs or use mapping attributes (API names depend on the DbLinq version). Example with attributes:

csharp
Copy CodeCopied
[Table(Name = “Users”)]
public class User
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int Id { get; set; }

    [Column(Name = “Username”)]
    public string Username { get; set; }

    [Column(Name = “Email”)]
    public string Email { get; set; }
}

If attribute-based mapping isn’t used, DbLinq typically supports fluent mapping or external mapping files—check the package docs.

Basic operations

1. Initialize context:

csharp
Copy CodeCopied
var ctx = new AppDataContext(connectionString);

2. Read (LINQ query):

csharp
Copy CodeCopied
var admins = ctx.Users                 .Where(u => u.Username.StartsWith(“admin”))
                .OrderBy(u => u.Username)
                .ToList();

3. Insert:

csharp
Copy CodeCopied
var user = new User { Username = “jdoe”, Email = “[email protected]” };
ctx.Users.InsertOnSubmit(user);
ctx.SubmitChanges();

4. Update:

csharp
Copy CodeCopied
var u = ctx.Users.First(x => x.Id == 42);
u.Email = “[email protected]”;
ctx.SubmitChanges();

5. Delete:

csharp
Copy CodeCopied
var toDelete = ctx.Users.First(x => x.Id == 99);
ctx.Users.DeleteOnSubmit(toDelete);
ctx.SubmitChanges();

Transactions and batching