SqlKata.QueryMan.AspNetCore 2.0.3

QueryMan

Simple and very concrete, fluent query manager based on SQLKata and Dapper.

Features

• Fluent queries (you have more freedom than with Linq, HQL or QueryOver)
• Save/Update/Delete entities
• Transactions
• Pagination

Installation

Using dotnet cli

$ dotnet add package SqlKata.QueryMan

Using Nuget Package Manager

PM> Install-Package SqlKata.QueryMan

If you need transaction filter

$ dotnet add package SqlKata.QueryMan.AspNetCore

or

PM> Install-Package SqlKata.QueryMan.AspNetCore

How to use

Init QueryMan (freeman way):

using QueryMan;
using SqlKata.Compilers;
using System.Data.SQLite;

// Method 1
using (var Db = new QueryRunner(new SQLiteConnection(ConnectionString), new SqliteCompiler()))
{
    // Man, do you queries here
}

// Method 2
Db = new QueryRunner(new SQLiteConnection(ConnectionString), new SqliteCompiler());

// Man, do you queries here

Db.Dispose();

Please remember to always Dispose your QueryRunner instance when you finish.

Init QueryMan (asp.net core):

using QueryMan;
using QueryMan.AspNetCore;
using QueryMan.AspNetCore.Identity;
using SqlKata.Compilers;
using System.Data.SQLite;

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        services.ConfigureQueryMan(new QueryManConfig()
        {
            Connection = (s) => new SQLiteConnection(s.GetRequiredService<IConfiguration>().GetConnectionString("DefaultConnection")),
            Compiler = (s) => new SqliteCompiler(),
        });

        // Identity
        services.AddQueryManIdentity();

        // Optionally you can globally enable database transactions against every http request.
        // As alternative use [TransactionActionFilter] attribute against your controllers or actions.
        // It will create a new database transaction before every request 
        // and perform a commit when the action (request) executed without erros, otherwise it will rollback all db changes.
        services.AddControllers(o => o.Filters.Add<TransactionActionFilter>());
    }
}

// Your controller
public class CustomerController : ControllerBase
{
    private readonly QueryRunner _db;

    public PageBController(QueryRunner db)
    {
        _db = db;
    }

    [HttpGet]
    public Customer Get(string id)
    {
        return _db.Get<Customer>(id);
    }

    [HttpPost]
    [TransactionActionFilter] // It will execute sql queries inside the transaction.
    public void Put(Customer customer)
    {
        _db.SaveOrUpdate(customer);
    }
}

The preffered way to have an entity against your datatable:

[Table("customers")]
public class Customer
{
    protected Customer() // Used by ObservableProxy to create a proxy object and filling with data from database.
    {

    }

    public Customer(string name) // Used by developers to create a new object before it will be saved to the db. See how the `mandatory Name` is implemented by `protected set`.
    {
        SetName(name);
    }

    public virtual void SetName(string name)
    {
        if (string.IsNullOrEmpty(name))
            throw new ArgumentNullException(nameof(name));
        
        Name = name;
    }

    [Key] // Primary key column
    public virtual string Id { get; protected set; } // "virtual" allows proxy to override properties, otherwise this property won't be treated as a database column and won't pull or persist any data when you pull data as proxies. 

    [Column("CustomerName")] // Custom column in the database (by defaut it will use a property name).
    public virtual string Name { get; protected set; }

    [Ignore] // This column will be ignored
    public virtual string CustomName { get; set; }

    // This column will be ignored (no 'virtual' keyword)
    public string Initials { get; set; }
}

You can also have multiple [Key] properties is needed.

Save to db:

var customer = new Customer("Customer 1");

Db.BeginTransaction();

await Db.SaveOrUpdateAsync(customer);

Db.Commit();

customer = await Db.GetAsync<Customer>(customer.Id);

Assert.Equal("Customer 1", customer.Name);

Get by Id:

var customer = await Db.GetAsync<Customer>(id);

Get from db:

Customer customer = null;
(string CustomerId, string CustomerName) model = default;

var query = Db.Query<Customer>()
    .Select(() => model.CustomerId, () => customer.Id)
    .Select(() => model.CustomerName, () => customer.Name)
    ;

var result = await Db.ToListNoProxy<(string CustomerId, string CustomerName)>(query);

AssertHelper.CollectionContainsAll(result,
    item => item.CustomerName == "Customer 1"
);

Get from db with GroupBy:

Contact contact = null;
Customer customer = null;
(string CustomerName, int ContactCount) model = default;

var query = Db.Query(() => customer)
    .LeftJoin(() => contact, () => contact.CustomerId, () => customer.Id)
    .Select(() => model.CustomerName, () => customer.Name)
    .SelectCount(() => model.ContactCount, () => contact.Id)
    .GroupBy()
    ;

var result = await Db.ToListNoProxy<(string CustomerName, int ContactCount)>(query);

AssertHelper.CollectionContainsAll(result,
    item => item.CustomerName == "Customer 1a" && item.ContactCount == 1,
    item => item.CustomerName == "Customer 2" && item.ContactCount == 0
);

Batch Update:

Customer customer = null;

var query = Db.SelectAll(() => customer);

var result = await Db.ToList<Customer>(query);

foreach (var (c, i) in result.Select((c, i) => (c, i)))
{
    c.SetName($"Customer No {i+1}");
}

Db.BeginTransaction();

await Db.SaveOrUpdateAsync(result);

Db.Commit();

Nuget

SqlKata.QueryMan

How to contribute

If you have any issues please provide us with Unit Test Example.

Please create an issue ticket to become a contributer.

Donations

Donate with nano.

Thank you!