moonstorm / fastcrud

fast .NET ORM for strongly typed people

You hate verbatim SQL queries with zero type safety for your code but you love their speed? Dapper.FastCrud is a fast orm built around essential features of the C# 6 / VB 14 that have finally raised the simplicity of raw SQL constructs to acceptable maintenance levels. These features leave no chance to mistypings or problems arising from db entity refactorings.

What to expect when working with Dapper.FastCrud in the DAL?

Type safety, clean code, less prone to errors, more peace of mind, while still being close to the metal. Here's a sample:

// Create paramters for the query
var queryParams = new 
{
    FirstName = "John",
    Street = "Creek Street"
};

// Get persons using the above created query parameters
var persons = dbConnection.Find<Person>(statement => statement
   .WithAlias("person")
   .Include<Address>(join =>
        join.InnerJoin()
            .WithAlias("address"))
   .Where($@"
        {nameof(Person.FirstName):of person} = {nameof(queryParams.FirstName):P} 
        AND {nameof(Address.Street):of address} = {nameof(queryParams.Street):P}")
   .OrderBy($"{nameof(Person.LastName):of person} DESC")  
   .Skip(10)
   .Top(20)
   .WithParameters(queryParams);

Features:

• Support for LocalDb, Ms Sql Server, MySql, SqLite, PostgreSql and SAP/Sybase SQL Anywhere.
• Entities having composite primary keys are supported, however note that the CRUD operations only support UNIQUE primary keys.
• Multiple entity mappings are supported, useful for partial queries in large denormalized tables and data migrations between different database types.
• All the CRUD methods accept a transaction, a command timeout, and a custom entity mapping.
• Fast pre-computed entity queries for simple CRUD operations.
• Compatible with component model data annotations.
• Opt-in relationships. As of 3.0, self referenced entities and multiple joins to the same target are also supported via aliases.
• A set of "formattables" are also included, which can be used even if you don't need the CRUD features of this library but you want to take advantage of the DB mappings.
• A generic T4 template for C# is also provided for convenience in the NuGet package Dapper.FastCrud.ModelGenerator.
• The following mapping styles are supported:
  • Database first (limited to SQL Server)
  • Code first, using model data annotations (preferred)
  • Fluent registration for POCO objects
  • Semi-POCO using metadata objects

Active Versions

• 3.3
• Test coverage: 76%

Check the release notes for the main library as well as the model generator for information about the recent changes.

WIKI

The wiki is a great place for learning more about this library.

Speed

Let's have a look at some popular ORMs out there and benchmark their speed:

• Dapper.SimpleCRUD v2.3.0
• DapperExtensions v1.7.0
• Entity Framework Core v7.0.3

Automatic Benchmark Report (Last Run: Friday, May 31, 2024)

Library	Operation	Op Count	Time (ms)	Time/op (μs)
Dapper	insert	10000	1,333.28	133.33
Fast Crud	insert	10000	1,396.15	139.62
Dapper Extensions	insert	10000	1,513.42	151.34
Simple Crud	insert	10000	1,490.36	149.04
Entity Framework - single op/call	insert	10000	15,056.93	1,505.69
Dapper	update	10000	1,290.22	129.02
Fast Crud	update	10000	1,314.45	131.45
Dapper Extensions	update	10000	1,541.99	154.20
Simple Crud	update	10000	1,381.38	138.14
Entity Framework - single op/call	update	10000	27,445.40	2,744.54
Dapper	delete	10000	1,209.75	120.98
Fast Crud	delete	10000	1,234.92	123.49
Dapper Extensions	delete	10000	1,327.86	132.79
Simple Crud	delete	10000	1,306.30	130.63
Entity Framework - single op/call	delete	10000	2,097.67	209.77
Dapper	select by id	10000	569.29	56.93
Fast Crud	select by id	10000	570.54	56.35
Dapper Extensions	select by id	10000	1,941.33	194.13
Simple Crud	select by id	10000	600.45	60.04
Entity Framework	select by id	10000	902.95	90.30
Dapper	select all	10000	532.22	53.22
Fast Crud	select all	10000	574.99	57.50
Dapper Extensions	select all	10000	3,012.01	301.20
Simple Crud	select all	10000	611.34	61.13
Entity Framework	select all	10000	1,465.61	146.56

Dapper is included for reference. All the libs involved get their own internal cache cleared before each run, the benchmark database is re-created, data file gets pre-allocated, and the statistics are turned off. The tests are following the same steps and are running in the same environment on the same number and size of records.

You can find more details about how to run the benchmarks yourself in the wiki.

Install the main library and the model generator via NuGet and enjoy!

fastcrud's Issues

After Update to 2.2.0

I update to from 2.1.2.to 2.2.0. then all Extensions marked as obsolate. What's wrong?

SetDialogue on the entity does not work for insert

HI
If i try e.g. try to change the dialect to Sqlite of an entity (i.e. not clone) and:
OrmConfiguration.GetDefaultEntityMapping().SetDialect(dialect);
and then:
connection.Insert(entity);
The dialog does not change and it tries to use MsSql dialect.

But if i:
transaction.Connection.Insert(entity, statement => statement.WithEntityMappingOverride(newMapping));

it works.

Update, Get etc. all apear to work, but that could be because the query is the same.

OrmConfiguration.GetDefaultEntityMapping().SetDialect(dialect); Set the dialog for all queries using that entity?

BR Rik

Update fails for entities with DatabaseGeneratedOption.Computed attribute

OrmConventions.cs:
Missing call to propertyMapping.ExcludeFromInserts().ExcludeFromUpdates(); for Computed option as well, otherwise the Update, Insert commands fail.

if (databaseGeneratedAttributes.Any(dbGenerated => dbGenerated.DatabaseGeneratedOption == DatabaseGeneratedOption.Computed))
{
     propertyMapping.SetDatabaseGenerated();
}
if (databaseGeneratedAttributes.Any(dbGenerated => dbGenerated.DatabaseGeneratedOption == DatabaseGeneratedOption.Identity))
{
      propertyMapping.SetDatabaseGenerated();
      propertyMapping.ExcludeFromInserts().ExcludeFromUpdates();
}

Mapping a property without column name throws ArgumentNullException

When you map the property to the column with same name using SetProperty with one param like this:

OrmConfiguration.RegisterEntity<Category>()
               .SetTableName("Category")
               .SetProperty(c => c.Code, prop => prop.SetPrimaryKey(true))
               .SetProperty(c => c.Description); 

Problem located in the EntityMapping.cs, line 197:

at Dapper.FastCrud.Validations.Requires.NotNull[T](T value, String parameterName)
   at Dapper.FastCrud.Mappings.EntityMapping`1.SetProperty[TProperty](Expression`1 property, Action`1 propertySetupFct)    

Paging and Update

Hi,
i really liked this library on top of dapper among other options.
I have seen mapping instruction which allows to update specific properties. is it possible to update properties from anonymous object or provide a list of properties at runtime?
.Update({Id= 1, Name= "ABC"});
.Update(user, statement => statement.onlyfields(u => u.Name));

this approach will be simple as OrmLite approach but that library is no more free to use.

For Paging, i have not seen any helper .Paging(1, 10, sql)

I have a plan to use this library but my project update same entity in different ways and having custom mappings for every case is not a case so is it possible to add this as feature in future if it makes sense?

Sqlite raise an error when we call LastInsertRowId after Insert

I get an error when i call the LastInsertRowId from SQLiteConnection after your insert methode.
"System.InvalidOperationException: Database connection not valid for getting last insert rowid."

My code :
public T Insert(T item) where T : IPropertyId
{
using (var dbConnection = SimpleDbConnection())
{
dbConnection.Insert(item);
item.Id = dbConnection.LastInsertRowId;
return item;
}
}

Do you close the connection when we call your Insert method ?

Log generated sql query

It's not really an issue, it's just a possible enhancement. When i find some time, i'll see if i can do it on a fork and create a pull request for it. What i want to do is the following:
In a certain situation, i would like to log the generated sql query to the console.
When i have difficulties with dataqueries, i can setup sql profiler on MSSQL to see what sql query Fastcrud has generated, but it is not always possible to setup sql profiler(example sqlite). It would be great if we could have a way to see which sql query is send to Dapper to be executed.

Get with Include fails with multiple related entities

I'm having trouble getting your example code to work from the wiki under the 'Joins' section. I've created the relevant database objects, etc. I have a single workstation that has multiple employee's. However, when executing the following code:

con.Get(new Workstation {WorkstationId = workStationId },
                    statement => statement.Include<Employee>()));

An exception is thrown:

Sequence contains more than one element

Stack trace if useful:

   at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)
   at Dapper.FastCrud.SqlStatements.RelationshipSqlStatements`1.SelectById(IDbConnection connection, TEntity keyEntity, AggregatedSqlStatementOptions`1 statementOptions)
   at DapperCore.Repository.Repository.GetWorkstation(Int32 workStationId) in C:\W\DapperCore\DapperCore\src\DapperCore\Repository\Repository.cs:line 28
   at DapperConsole.Program.Main(String[] args) in C:\W\DapperCore\DapperCore\src\DapperConsole\Program.cs:line 23

It doesn't seem to be able to handle the fact that the Workstation has many Employee objects associated with it.

If I remove the Employee's from the database so that there is only a one-to-one relationship between the two. The code above works fine and I get only a single Employeeobject in the collection.

Insert should really return an id.

Hi

Very nice package. Great work! One issue. Insert should return an id. Pref a generic if possible.
Check: Dapper.Contrib Dapper.SimpleCRUD.

Seams to be a common requirement.

Great work.

MSSQL insert issues when triggers are enabled

I am guessing the same will happen with updates and deletes.

This ORM for node js ran into the same problem with mssql which i saw upon googling.

sequelize/sequelize#3284

The exact error is -
The target table 'Contact' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Allow for manual entity mappings

Currently the library uses only auto-generated mappings based on attribute decorated entities. There is no way to currently override these defaults or provide manual mappings.

Selecting certain columns

Have you thought about selecting certain columns only in select / get queries?

I was thinking of having an attribute which eliminates certain columns from a get query. The use case i am trying to solve is to eliminate large fields which will slow down select queries. So the query is 10x faster if the big ntext of nvarchar(max) field can be ignored.

But when you are getting a single object you can include that field as part of the get.

What would you recommend be the best way to accomplish that?

DOTNET Core support

Is there any support for .NET Core ?

sequence contains no elements

Hello,

I've encounter a 'sequence contains no elements' exception when trying to Update an entity. Any help would be appreciated.
using (var connection = GetOpenConnection()) { connection.Update(hmsJobEntity); }

`public class HmsJob
{
[System.ComponentModel.DataAnnotations.Key]
public int Id { get; set; }
public string Name { get; set; }
public virtual ICollection HmsJobSetting { get; set; }

}`

`public partial class HmsJobSetting
{
[System.ComponentModel.DataAnnotations.Key]
public int Id { get; set; }

    [System.ComponentModel.DataAnnotations.Schema.ForeignKey("HmsJob")]
    public int HmsJobId { get; set; }
    public string Name { get; set; }
    public string Value { get; set; }
    public string Comments { get; set; }
    
    public virtual HmsJob HmsJob { get; set; }
}`

Multiple active Sql dialects

Allow for multiple dialects per entity domain set.

Compatibility with .Net 4.6.1?

Hi,

Am having an issues with conflicts between StringInterpolationBridge and the actual available classes in .Net framework 4.6.1

Error CS0433: The type 'FormattableString' exists in both 'StringInterpolationBridge, Version=0.9.1.0, Culture=neutral, PublicKeyToken=null' and 'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' (174, 27)

Given that Dapper.FastCrud is dependant on StringInterpolationBridge I cant remove it.

Any idea what I can do? I would prefer not to downgrade to 4.5.2 unless absolutely required.

Regards,

Insert doesnt work with sqlite

Hello,

I use your extension with sqlite.
I have an issue when the insert method is call : no such function: SCOPE_IDENTITY.

The connection :
SQLiteConnection($"Data Source={DbFile};Version=3;");

The table is :
@"create table Fournisseur
(
Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
Nom varchar(100) not null,
Pays varchar(100) not null
)"

Entity :

[Table("Fournisseur")]
public class Fournisseur
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [ExcelColumn("Nom")] //maps the "Name" property to the "Company Title" column
    public string Nom { get; set; }
    [ExcelColumn("Pays")] //maps the "State" property to the "Providence" column
    public string Pays { get; set; }
  }

Then
var fournisseur = new Fournisseur() { Nom = "AllInBIke", Pays = "France" };
db.Insert(fournisseur);

I have missing something ?

.NET Core platform

I am working on a project in ASP.NET Core. Can you make a package without the dependency on StringInterpolationBridge because it is not compatible with .NET Core 5.0 and makes the whole Dapper.FastCRUD package unusable when trying to play around with it in e.g. ASP.NET Core ?

Object / Sqlvariant type not being added in scripts

I have a property

public object FIELD_VALUE { get; set; }

which maps to a DB column FIELD_NAME which is of type sql_variant not null.

That column gets ignored when the insert or update statement is generated.
Works fine in select statements.

Async methods?

I did not see async methods in FastCRUD. Do you have those overloads? Any plans to add them?
Given that FastCRUD's focus is on speed.
Providing async overloads would help achieve more efficient use of server resources.

Enitites keys

Hi
Thanks for the great package.
Are you exposing you methods to get teh key names and values in any way.
I.e. can i call a class / method to get the primary key from your package. Or only by reflections?

var keys = OrmConfiguration.GetDefaultEntityMapping().GetProperties(PropertyMappingOptions.KeyProperty);
var allKeyValues= keys.Select(key => entity.GetType().GetProperty(key.PropertyName).GetValue(entity));

Q: How can i work with relationship

How i get complex models or how can i work with virtual model propertiies. There is no documentation about it or i cant find something.

SQLite composite key not working

I see that entity having composite key for a SQLite database does not work.
There is a exception throw in the code.
Can you let me know how can I test my code with the SQLite and if there is a better alternative to do this

https://github.com/MoonStorm/Dapper.FastCRUD/blob/fb37c4a4796bc4482661b7feb9dd5802c955af79/Dapper.FastCrud/SqlBuilders/SqLiteBuilder.cs#L16

vb.net

Hello,

please can you tell me an example in vb.net.

• dbConnection.Find(
  whereClause:$"{nameof(Entity.FirstName)}=@FirstNameParam",
  orderClause:$"{nameof(Entity.LastName)} DESC",
  skipRowsCount:10, limitRowsCount:20,
  queryParameters: new {FirstNameParam: "John"});

Im new in C# and don't undertand this. Btw: This example dosen't work in Net 4.5 for me.

Partial entity updates

It would be nice to have a way of updating portions of an entity, in case the entity gets partially updated from a DTO.

Having problem when querying / inserting using MariaDB.

I got this exception:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[ID],[CompanyID],[FirstName],[LastName] FROM [Persons] WHERE [ID]=4' at line 1

I tried latest nuget package MySql.ConnectorNET.Data by Oracle (v. 6.8.3.2) and latest 6.9.9.

Any suggestions ?

Should Find return null or empty IEnumerable on no result.

The Find method is similar to EF Where method. Which always returns an empty IEnumerable (not nulll) when there are no results that match that query.

But here in FastCrud null is returned which means on every Find we have to do a null check. Should we be returning IEnumerable with no elements instead?

How to Get Data From multiple tables using join conditions

PostgreSqlBuilder should use " as tableAndColumnDelimiter.

MySql: Building queries using limit clause

Hi,

How can I construct my queries with the statement builder to use the LIMIT clause?
E.g.

connection.Find<Entity>( st => st.Limit( 100 ) ); // select * from entity limit 100

I've been through the code and wiki, but I can't find a solution. What am I missing here?
Thanks.

Mysql: Entity __ has database generated fields that don't contain a primary key.

Hello, I was attempting to use your repository with a Mysql database. I have an entity which has a composite primary key of which none of the keys are database generated. There is however a ModifiedTimestamp, which is calculated on insert and update. Sample Here:

    public class AggregatedRow
    {
        [Key]
        public int Id1{ get; set; }
        [Key]
        public string Id2{ get; set; }
        [Key]
        public string Id3 { get; set; }
        public int AggregateValue { get; set; }
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public System.DateTime ModifiedTimestamp { get; set; }
    }

Which is intended to correlate to the following table:

CREATE TABLE `AggregatedRow` (
  `Id1` int(11) NOT NULL,
  `Id2` varchar(255) NOT NULL,
  `Id3` varchar(55) NOT NULL,
  `AggregateValue` int(11) NOT NULL,
  `ModifiedTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`FileCollectionCycleId`,`StatusName`,`EventTimestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

When I attempt to insert into this table with FastCRUD, I get:

Entity 'AggregatedRow' has database generated fields that don't contain a primary key.

Which looks like it is getting thrown from MySqlBuilder.ConstructFullInsertStatementInternal. It looks like my entity has a value of 0 for: InsertKeyDatabaseGeneratedProperties. Looking at the following code, it looks like those checks could be against KeyProperties == 0 instead. I'd make the pull request myself, but I'm not entirely sure if there's something I am missing.

If there's anything I can do to help, please let me know.

Also, thanks for the great work on this repo!

Sql builder

it would be useful for the library to provide help with generating manual SQL queries. At the very least it should provide help with the generation of table names and list of properties mapped to database column.

Alias support should also be included.

StrongName

Hello, how are you?
First congratulations on the excellent work.

You can add the library one strongname?
How do I sign my library, I can not use his because it is not signed and is thus triggered an exception.

Thank you.

More of a question, but OrmConfiguration is static, so this means you can't have more than one database being used at one time?

My needs (which does not seem uncommon) require me to talk to different databases, identical in schema, but having different schema names. This is a web service so could be many different threaded users. OrmConfiguration is static, and its the only way I know of to change the schema used on queries.

It seems like a common trend for these microOrms to use static facilities in such a way that makes them useless in non-trivial multi-tenant environments. I'm hoping this is not the case, I like your project.

Unable to get the first n rows or the top 1 row - IRangedConditionalResultsSqlStatementSetter

Currently if i want to get just the first row, based on any where clause. I send skip = 0 and top/limitResults = 1.
But Skip throws an exception that skip should be > 0 instead of >=0.

/// <summary>
        /// Skips the initial set of results.
        /// </summary>
        public TStatementOptionsBuilder Skip(long skipRecordsCount)
        {
            Requires.Argument(skipRecordsCount > 0, nameof(skipRecordsCount), "The number of records to skip must be a positive value");

            this.SkipResults = skipRecordsCount;
            return this.Builder;
        }

Next, if i want to get first n rows then i have to set Top( n), that it using MsSqlbuilder causes a sql exception due to the fact that it prints out a fetch n without an offset.

Customization questions

+1 for an awesome dapper extension. The code is pretty intense, complex and elegant at the same time. I'm still having a hard time trying to read it and understand the mappings, interfaces, builders, options, etc., hence these questions...

1. Our tables have a column to indicate if a row is virtually deleted. Is there anyway to configure the "select by id" to query by id and also to pull back rows that aren't virtually deleted? Pretty much the where clause will ALWAYS check for non-virtually deleted rows (see below). Can this be done with a custom entity mapping during the select call? Or the only way is to use the Find() extension method?

select ... 
from tbl 
where id = @id and isvirtuallydeleted = 0

2. Our tables have a column to prevent concurrency update/delete. Is there anyway to configure the "update by id" to update that entity by its id and also by it's original concurrency value? Again with the where clause ALWAYS checking for the original concurrency value on an update/delete statement. Can custom entity mapping solve this? Or BulkUpdate()/BulkDelete() is the answer?

update tbl 
set name = @name, concurrencyflag = @concurrencyflag 
where id = @id and concurrencyflag = @originalflag and isvirtuallydeleted = 0

I've read through the other dapper extensions code and they don't support these two scenarios "by id" but there is a work around for the 1st one just like how I have described to use Find().

OrmConfiguration.GetDefaultEntityMapping(Type)

It is necessary to have OrmConfiguration.GetDefaultEntityMapping(Type).

DateTime columns get from database have Kind property Unspecified.

I setup a simple unit test that adds a row to the database and the date that is added to the database has Kind property set when inserting it as Utc by default. As its added using DateTime.UtcNow.

When i get the same object back from Database using dapper / dapper fastcrud the same field has the correct datetime but the Kind property is Unspecified - when it should be Utc.

I realize that this could be something that maybe need to be put in dapper. Is there any overrides we can do to fastcrud library that can set the kind property to the datetimes retrieved?

Sql statement builder interfaces not taking null values

The new sql builders interfaces do not take null values.

Not sure if i am missing anything but this will end up being very verbose if i have to check whether skip that is passed to me is NULL or not and then call the .Skip() method vs me just being able to pass the values to the old sql generator.

And so and so forth with each of the inputs - order, take, where, transaction etc.

Docs - Home - reformatting

wrapped the home examples in code blocks as the angle brackets weren't visible (ie dbConnection.BulkDelete();)

Added headings.

I couldn't figure out how to submit wiki updates as a patch, sorry.

Home.md.txt

MsSqlBuilder.ConstructFullInsertStatement does not respect databaseColumnName in output clause.

How to map a C# enum to a PostgreSQL enum type?

I have a C# class Sample, one of whose properties is an enum, TargetType. I have a corresponding table samples defined in a PostgreSQL database, along with a matching enum type, targettypes.

With Dapper.FastCRUD, I can retrieve records from the table successfully. However, I get an error during insertion:

Npgsql.PostgresException (0x80004005): 42804: column "target_type" is of type targettype but expression is of type integer

How do I tell Dapper.FastCRUD to map the C# enum TargetType to PostgreSQL targettype for inserts?

In my C# code, I've defined:

// Map the C# enum 'TargetType' to PostgreSQL enum 'targettype'
// Allows Npgsql to insert and retrieve the fields with correct type-casting / boxing
Npgsql.NpgsqlConnection.MapEnumGlobally<TargetType>("public.targettype");  

Dapper.FastCrud.OrmConfiguration.DefaultDialect = Dapper.FastCrud.SqlDialect.PostgreSql;

When using only Npgsql (and no ORM), I can successfully insert as follows:

using (var writer = conn.BeginBinaryImport(sql))
{
    foreach (Sample sample in entities)
    {
        writer.StartRow();
        ...
        writer.Write(sample.TargetType); // <--- mapped to column 'target_type'
        ...
    }
}

Entityframework code first entities

Hi - I have a project with entity framework code first entities, generated from an existing database by reverse engineering a model, want to start using the same entities to use a few queries to go through FastCrud. Not sure if you have tried that.

What changes do you think i would need to make to the entities, to make it work with both?
Would the navigational properties (the public virtual ICollection properties for foreign keys) cause any issues with fastcrud?

Dapper.Query<T> has no mappings

When I try to use the Query/QueryMultiple-Method of Dapper alongside with FastCrud the column mappings are not working. If I use SqlMapper.SetTypeMap to set up the mappng for Dapper, the column mappings for FastCrud are not working anymore and the Find-Method returns unpopulated entities.

How do I get around the issue?

`Id` property as key by default

Btw. love what you are doing and thanks for the benchmarks. I had started using SimpleCRUD. When i stumbled upon this.

My question is would those entities directly work or do i need to regenerate them using the t4 templates you have provided?

In SimpleCRUD just having an Id named filed was enough. Do i have to decorate all Ids with
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

Thanks,
Mohit

Sql dialogue alternative

Hi

I feel that the Sql Dialect is a problem.
If using a repository pattern, the base class uses a generic in regard to the entity, and probabley even the connectin is a generic.
So It is typically only when a method is called in through the repository to a database for an entity that the databse dialect is known. That is, if you have a flexible repository pattern.

What i am getting at, is it i very difficult to set the dialect without a helper method always being called in the repository methods, or always passing the mapping to the FastCRUD extension methods.

Is there really no way the the dialogue can be set a more automatic way, for example using the dbconnection type name?

Thanks for the great package :-)

IStandardSqlStatementOptionsBuilder there is no way to set the where clause

I want to do a batch delete.

But there is no way to set the where clause in IStandardSqlStatementOptionsSetter.
The ISqlStatementOptionsGetter has a way to read the where.

Am i missing anything? I have not really gone into the sql generation logic much in this library yet.

GetAsync() and Include

There seems to be an issue with the GetAsync() method. I'm writing a small library to maintain the database of my private mail server and tried to query a SQLite database. The "MailAccount" class contains a property to the "Domain" the mail account belongs to. Furthermore a property of type long annotated with the ForeignKey attribute points to the "Domain" property.

The following line works as expected:
MailAccount account = connection.Get(new MailAccount { Id = id }, statements =>statements.Include<Domain>(x => x.LeftOuterJoin()));

The asynchronous equivalent does not:
MailAccount account = await connection.GetAsync<MailAccount>(new MailAccount { Id = id }, statements => statements.Include<Domain>(x => x.LeftOuterJoin()));

Is there something I should additionally consider when using the asynchronous API functions?

Column Renaming Does Not Work When Read

Hello Again

I have a piece of code like this:

[Table("[Report.Schedule]")]
public partial class Report_Schedule
{
   // ... other columns
    [Column("SCHEDULE"), Required]
    public string SCHEDULE_DETAIL { get; set; }
}

So the column SCHEDULE would save, but the property SCHEDULE_DETAIL would never load the data from database. If however, I change the property name back to "SCHEDULE", it would read just fine.

Here is the Column create SQL:

[SCHEDULE] [nvarchar](max) NOT NULL,

MSSQL Offset & Fetch without orderby

There is a issue. I must add order by for offset and fetch.

SqlBuilders/MsSqlBuilder.cs
There is fix

    public override string ConstructFullBatchSelectStatement(
            FormattableString whereClause = null,
            FormattableString orderClause = $"1",
            int? skipRowsCount = null,
            int? limitRowsCount = null,
            object queryParameters = null)