Providers Overview - WitDatabase

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

Quick Comparison

Aspect	Core API	ADO.NET	Entity Framework Core
Abstraction Level	Low	Medium	High
Query Language	None (Key-Value)	SQL	LINQ + SQL
Learning Curve	Medium	Low	Medium
Performance	Maximum	Excellent	Very Good
Type Safety	Manual	Manual	Automatic
Schema Management	Manual	Manual	Migrations
Change Tracking	No	No	Yes
Relationships	Manual	Manual (JOINs)	Navigation Properties
Best For	Caching, K/V stores	SQL queries, reports	Business applications

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:

csharp

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

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

// Custom document store
db.Put($"user:{userId}".ToBytes(), JsonSerializer.SerializeToUtf8Bytes(user));

Package:

bash

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:

csharp

// 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:

bash

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:

csharp

// 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:

bash

dotnet add package OutWit.Database.EntityFramework

Decision Flowchart

Additional considerations:

If you...	Consider...
Are building a new application	EF Core — faster development
Have existing ADO.NET code	ADO.NET — minimal changes
Need maximum performance	Core API or ADO.NET
Want compile-time query validation	EF Core — LINQ
Need complex raw SQL	ADO.NET — full control
Are building a caching layer	Core API — minimal overhead
Need database migrations	EF Core — built-in
Work with legacy schemas	ADO.NET — no conventions

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:

csharp

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:

csharp

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($"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):

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

Feature	Core API	ADO.NET	EF Core
Data Access
Key-Value operations	✅	❌	❌
SQL queries	❌	✅	✅
LINQ queries	❌	❌	✅
Stored procedures	❌	❌	❌
Transactions
Basic transactions	✅	✅	✅
Savepoints	✅	✅	✅
Isolation levels	✅	✅	✅
Distributed transactions	❌	❌	❌
Schema
Auto-create tables	❌	❌	✅
Migrations	❌	❌	✅
Reverse engineering	❌	❌	❌
Advanced
Connection pooling	N/A	✅	✅
Async support	✅	✅	✅
Blazor WASM	✅	❌	❌
Batch operations	✅	✅	✅ (EF 7+)
Bulk update/delete	✅	✅	✅ (EF 7+)

Summary

Provider	One-Line Summary
Core API	Maximum performance, full control, no SQL — for caching and key-value storage
ADO.NET	Full SQL power with familiar API — for queries, reports, and existing codebases
EF Core	Modern ORM with LINQ — for business applications and rapid development