Providers Overview

WitDatabase offers three distinct APIs for data access, each designed for different use cases and developer preferences. This guide helps you choose the right approach for your project.

Three Ways to Access WitDatabase

[[Svg Src="./witdatabase-full-architecture.svg" Alt="witdatabase-full-architecture"]]

Quick Comparison

When to Use Each Provider

Core API

Choose Core API when you need:

• Maximum possible performance
• Simple key-value storage
• Custom serialization format
• Direct control over transactions
• Caching layer implementation
• Document storage (JSON, MessagePack, Protobuf)

Typical use cases:

// Session storage
db.Put(sessionKey, sessionData);
var session = db.Get(sessionKey);

// Caching
db.Put(cacheKey, cachedResponse);

// Custom document store
db.Put(

        
        

            
            Loading...
        
quot;user:{userId}".ToBytes(), JsonSerializer.SerializeToUtf8Bytes(user));

Package:

dotnet add package OutWit.Database.Core

ADO.NET Provider

Choose ADO.NET when you need:

• Full SQL capabilities
• Familiar DbConnection/DbCommand pattern
• Complex queries (JOINs, aggregations, CTEs)
• Direct control over SQL
• Integration with existing ADO.NET code
• Report generation
• Bulk data operations

Typical use cases:

// Complex reporting query
using var cmd = connection.CreateCommand();
cmd.CommandText = """
    SELECT 
        c.Name AS Category,
        COUNT(*) AS ProductCount,
        AVG(p.Price) AS AvgPrice
    FROM Products p
    JOIN Categories c ON p.CategoryId = c.Id
    GROUP BY c.Id
    HAVING COUNT(*) > 5
    ORDER BY AvgPrice DESC
    """;

// Bulk insert with transaction
using var transaction = connection.BeginTransaction();
// ... insert thousands of records efficiently
transaction.Commit();

Package:

dotnet add package OutWit.Database.AdoNet

Entity Framework Core Provider

Choose EF Core when you need:

• Object-relational mapping (ORM)
• LINQ queries with IntelliSense
• Automatic change tracking
• Navigation properties and lazy loading
• Database migrations
• Integration with ASP.NET Core
• Rapid application development
• Strong typing and compile-time checks

Typical use cases:

// Business logic with relationships
var orders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .Where(o => o.Status == OrderStatus.Pending)
    .OrderByDescending(o => o.CreatedAt)
    .Take(10)
    .ToListAsync();

// Automatic change tracking
var user = await context.Users.FindAsync(userId);
user.LastLoginAt = DateTime.UtcNow;
await context.SaveChangesAsync();  // UPDATE generated automatically

Package:

dotnet add package OutWit.Database.EntityFramework

Decision Flowchart

[[Svg Src="./witdatabase-api-decision-tree.svg" Alt="witdatabase-api-decision-tree"]]

Additional considerations:

Mixing Providers

You can use multiple providers in the same application. They all work with the same underlying database file.

Example: EF Core + ADO.NET

Use EF Core for business logic and ADO.NET for complex reports:

public class OrderService
{
    private readonly AppDbContext _context;
    private readonly WitDbConnection _connection;

    public OrderService(AppDbContext context, IConfiguration config)
    {
        _context = context;
        _connection = new WitDbConnection(config.GetConnectionString("Default"));
        _connection.Open();
    }

    // EF Core for business operations
    public async Task<Order> CreateOrderAsync(OrderDto dto)
    {
        var order = new Order { /* ... */ };
        _context.Orders.Add(order);
        await _context.SaveChangesAsync();
        return order;
    }

    // ADO.NET for complex reporting
    public async Task<SalesReport> GetSalesReportAsync(DateTime from, DateTime to)
    {
        using var cmd = _connection.CreateCommand();
        cmd.CommandText = """
            WITH MonthlySales AS (
                SELECT 
                    STRFTIME('%Y-%m', OrderDate) AS Month,
                    SUM(Total) AS Revenue,
                    COUNT(*) AS OrderCount
                FROM Orders
                WHERE OrderDate BETWEEN @from AND @to
                GROUP BY STRFTIME('%Y-%m', OrderDate)
            )
            SELECT 
                Month,
                Revenue,
                OrderCount,
                Revenue - LAG(Revenue) OVER (ORDER BY Month) AS Growth
            FROM MonthlySales
            ORDER BY Month
            """;
        cmd.Parameters.AddWithValue("@from", from);
        cmd.Parameters.AddWithValue("@to", to);

        // ... read and return report
    }
}

Example: EF Core + Core API

Use EF Core for structured data and Core API for caching:

public class ProductService
{
    private readonly AppDbContext _context;
    private readonly WitDatabase _cache;

    public ProductService(AppDbContext context)
    {
        _context = context;
        _cache = new WitDatabaseBuilder()
            .WithMemoryStorage()
            .WithBTree()
            .Build();
    }

    public async Task<Product?> GetProductAsync(int id)
    {
        // Check cache first
        var cacheKey = Encoding.UTF8.GetBytes(

        
        

            
            Loading...
        
quot;product:{id}");
        var cached = _cache.Get(cacheKey);
        
        if (cached != null)
            return JsonSerializer.Deserialize<Product>(cached);

        // Cache miss — load from database
        var product = await _context.Products.FindAsync(id);
        
        if (product != null)
        {
            _cache.Put(cacheKey, JsonSerializer.SerializeToUtf8Bytes(product));
        }

        return product;
    }
}

Performance Characteristics

Relative performance for common operations (lower is better):

[[Svg Src="./witdatabase-api-performance.svg" Alt="witdatabase-api-performance"]]

Note: EF Core overhead comes from change tracking, query translation, and object materialization. For most applications, this overhead is negligible compared to business logic execution time.

Feature Matrix

Summary