StaTypPocoQueries.AsyncPocoDpy

AsyncPoco bindings for StaTypPocoQueries. It translates Linq Expressions to SQL 'where' clause. Makes it safer to work with AsyncPoco. Generates sql and parameters to be passed to Database class methods such as Query, Single, Delete etc.


Keywords
strong, strongly, type, typed, query, poco, petapoco, linq, expression, translator
License
Apache-2.0
Install
Install-Package StaTypPocoQueries.AsyncPocoDpy -Version 1.12.0

Documentation

Statically Typed Poco Queries

What is this?

It is a library providing ability to query "Poco family of ORMish things" using statically typed queries. Strictly speaking StaTypPocoQueries.Core is a translator of Linq Expressions into sql text query and its bound parameters. StaTypPocoQueries.AsyncPoco provides extensions to AsyncPoco AsyncPoco.Database class so that queries become available 'natively'. It is possible to use StaTypPocoQueries.Core to provide extensions to other Poco libraries. Use StaTypPocoQueries.PetaPoco to use it with PetaPoco. Original idea for the library: Tytusse (https://github.com/tytusse)

Given one has following entity defined:

[TableName("SomeEntity")]
[PrimaryKey("id")]
[ExplicitColumns] 
class SomeEntity {
    [Column] public int Id { get; set; }
    [Column] public string SomeColumn { get; set; }
}

one can write

db.FetchAsync<SomeEntity>(x => x.SomeColumn == "foo"); //db is of type AsyncPoco.Database

and call will be translated to

db.FetchAsync<SomeEntity>("where [SomeColumn] = @0", new []{"foo"}) 

Full Example

using AsyncPoco;
using StaTypPocoQueries.AsyncPoco;

[TableName("SomeEntity")]
[PrimaryKey("id")]
[ExplicitColumns] 
class SomeEntity {
    [Column] public int Id { get; set; }
    [Column] public string SomeColumn { get; set; }
    [Column] public int anInt { get; set; }
    [Column] public int? nullableInt { get; set; }
}

class YourDataAccessLayer {
	async Task<List<SomeEntity>> Something(Database db) { 
		return await db.FetchAsync<SomeEntity>(x => x.SomeColumn == "foo");
	}
}

How to install it?

Install it from nuget using Visual Studio's dialogs or from command line

Install-Package StaTypPocoQueries.Core
Install-Package StaTypPocoQueries.AsyncPoco

... or compile it from sources.

Is it stable?

I think so. There are plenty of tests for both the translator and for AsyncPoco wrapper using Sqlite and containerized Sql Server.

Features (which constructs are supported?)

NOTE: for brevity all examples below assume quoting of special names using Sql Server dialect: [something] is used to quote something

  • Database support
    Supported and tested are Sql Server and Sqlite. Supported but not tested are: PostgreSQL, Mysql and Oracle.

  • .NET version support (tested)

  • Language support
    Both C# and F# is supported. More specifically translation is possible for System.Linq.Expressions and Microsoft.FSharp.Quotations

  • Quoting of reserved names such as 'table'
    All column names are quoted to avoid usage of special names. Internally database dialect is inferred from database connection class and proper quoting mechanism is chosen. NOTE: all following examples assume square brackets as quoting characters but that is just for sake of example.

  • Parameters in queries can be constants, nullable variables, not nullable properties

  • Simple equals / not equals

    //following code...
    var aVar = 5;
    db.MethodNeedingQuery<SomeEntity>(x => x.anInt == aVar)
    
    //...is translated to:
    db.MethodNeedingQuery<SomeEntity>("[anInt] = @0", new [] {5})
  • Is greater, smaller, greater-or-equal, smaller-or-equal than

    //following code...
    var aVar = 5;
    db.MethodNeedingQuery<SomeEntity>(x => x.anInt >= aVar)
    
    //...is translated to:
    db.MethodNeedingQuery<SomeEntity>("[anInt] >= @0", new [] {5})
  • Is null / is not null

    //following code...
    string aVar = null;
    db.MethodNeedingQuery<SomeEntity>(x => x.SomeColumn == aVar)
    
    //...is translated to:
    db.MethodNeedingQuery<SomeEntity>("[SomeColumn] is null")
  • 'or' and 'and' junctions without superfluous square brackets

    //following code...
    db.MethodNeedingQuery<SomeEntity>(x => x.anInt == 3 || x.anInt == 4)
    
    //...is translated to:
    db.MethodNeedingQuery<SomeEntity>("[anInt] = @0 or [anInt] = @1", new [] {3,4})
    //following code...
    db.MethodNeedingQuery<SomeEntity>(x => (x.anInt == 6 || x.SomeColumn == "foo") &&  (x.anInt == 4 || x.SomeColumn == "bar") )
    
    //...is translated to:
    db.MethodNeedingQuery<SomeEntity>("([anInt] = @0 or [SomeColumn] = @1) and ([anInt] = @2 or [SomeColumn] = @3)", new [] {6,"foo",4,"bar"})
  • 'Nullable.HasValue and Nullable.Value'

    db.MethodNeedingQuery<SomeEntity>(x => !x.nullableInt.HasValue || x.nullableInt.Value == 5)
    
    //...is translated to:
    db.MethodNeedingQuery<SomeEntity>("[nullableInt] IS NULL OR [nullableInt] = @0", new []{5})
  • Array.Contains(item) translated into native array without expanding array into as many parameters as there is in collection. It is optional feature. Needs support from both *Poco library and underlying database client library.

      var neededStrs = new[] { "foo2", "foo1" };
      db.MethodNeedingQuery<SomeEntity>(x => neededStrs.Contains(x.AString));

    for more info: