PetaPoco.SqlKata

PetaPoco is a handy micro-ORM, but the SQL builder that comes with it is extremely limited. This library lets you use SqlKata as a replacement query builder.

Usage

Basic

The simplest way to use this library is just to use a SqlKata Query object in place of a PetaPoco Sql object. There are extension methods on IDatabase which allow this for most of the basic query methods.

public class MyTable
{
    property int ID { get; set; }
    property string Name { get; set; }
    property string Category { get; set; }
}

using (var db = new PetaPoco.Database(...))
{
    var query = new Query("MyTable").Select("ID", "Name", "Category").Where("Category", "foo");
    var records = db.Fetch<MyTable>(query);
}

Note that there are no extension methods for Single() or SingleOrDefault(). This is because one of the overloads available in PetaPoco takes an object parameter, and this overload will resolve before an extension method with a more specific parameter type. The workaround is to use db.Query().Single().

While SqlKata does requires a table name in order to render a query natively, this library can autogenerate the SELECT and FROM clauses from the target class, just like the PetaPoco EnableAutoSelect feature.

// This is equivalent to the example above.
// SELECT and FROM clauses will be autogenerated.
using (var db = new PetaPoco.Database(...))
{
    var query = new Query().Where("Category", "foo");
    var records = db.Fetch<MyTable>(query);
}

Advanced

There are other types of methods provided in this library which give you more control over how the SQL is generated.

ToSql()

// Build any SqlKata query
var query = new Query("MyTable")
    .Select("Field1", "Field2")
    .Where("Foo", "Bar");

// Turn it into a Sql object
var sql = query.ToSql();

Custom Compilers

Transforming a SqlKata Query into a SQL string (and then into a Sql object) requires a compiler. SqlKata comes with compilers for SQL Server, Postgres, MySql, Firebird, Oracle, and SQLite. For many simple queries, the generated SQL looks the same regardless of which compiler you use, but for certain queries the compiler will produce SQL tailored for that specific database. The compilers also know which characters to use to escape identifiers.

By default, ToSql() uses the SQL Server compiler. If you want to use a different compiler, there are a few different ways you can do so.

// Specify the compiler for one SQL statement
var sql = query.ToSql(CompilerType.MySql);

// Specify a custom compiler for one SQL statement
var sql = query.ToSql<MyCompiler>();
// or
var sql = query.ToSql(new MyCompiler());

// Change the default compiler for all SQL statements
SqlKataExtensions.DefaultCompilerType = CompilerType.Postgres;

// Provide a custom compiler for all SQL statements
SqlKataExtensions.CustomCompiler = new MyCompiler();

Note that the IDatabase extension methods (see Basic, above) automatically pick a compiler based on the database's Provider. They do not use the DefaultCompilerType property. But you can still pass in a custom compiler if you want, or you can register a custom compiler to be used with a given Provider.

var query = new Query("MyTable")
    .Select("Field1", "Field2")
    .Where("Foo", "Bar");

using (var db = new PetaPoco.Database(...))
{
    var records = db.Query<MyTable>(query, new MyCompiler());
    
    // or register MyCompiler as the default for your Provider
    DefaultCompilers.RegisterFor(db.Provider, new MyCompiler());
    var records = db.Query<MyTable>(query);  // same result as above
}

Generate from POCO

Since part of the benefit of PetaPoco is that it understands information embedded in a POCO, there are also two extension methods to help do the same thing.

public class MyTable
{
    property int ID { get; set; }
    [Column("NAME_FIELD")]
    property string Name { get; set; }
}

// These will all add the FROM and are equivalent to new Query("MyTable").
// If the class has a TableName property, that will be used instead.
var query = new Query().ForType<MyTable>();
var query = new Query().ForType(typeof(MyTable));
var query = new Query().ForObject(new MyTable());

// These will all add both the SELECT and the FROM:
// SELECT [ID], [NAME_FIELD] FROM [MyTable]
var query = new Query().GenerateSelect<MyTable>();  
var query = new Query().GenerateSelect(typeof(MyTable));
var query = new Query().GenerateSelect(new MyTable());

These methods need to have a PetaPoco IMapper in order to map class and property names to table and field names. By default, they use a ConventionMapper. They also have overloads that let you pass in your own IMapper instance, or you can assign a mapper to SqlKataExtensions.DefaultMapper to be used for all queries.

The IDatabase extension methods (see Basic, above) use PetaPoco to select the correct mapper based on the target type and the database's DefaultMapper. They do not use the SqlKataExtensions.DefaultMapper property.

Update, Delete, etc.

If you construct a SqlKata query with AsUpdate(), AsDelete(), or AsInsert() and want to use it with PetaPoco, you have to always use db.Execute(query) to do so. Calling a method like db.Update(query) will fail.

The main reason for this is that this library doesn't provide IDatabase extension methods corresponding to Update(), Delete(), and Insert(). So if you call those PetaPoco methods, PetaPoco will assume that you're passing in a poco object to be operated on.

If you were to do db.Update(query.AsSql()), you'd run into a further problem. PetaPoco assumes that any SQL you pass in is what comes after UPDATE [tablename] and will automatically add that clause to the query. Since the SqlKata-generated SQL already includes that, the massaged query that PetaPoco passes to the database will be malformed.