#Welcome to SqlFu

SqlFu is a versatile micro-orm (like dapper.net, peta poco , massive etc) for .Net 4. SqlFu is Apache licensed. If you're wondering if there's a reason for yet another micro-orm read this

Why should you use it

The main USP (unique selling proposition - advantage) of SqlFu is Versatility. This is the reason I've developed it. I need it more flexibility and the micro-orm I was using (peta poco) didn't have it and if other micro-orms had it, they were too slow (FluentData).

I've designed SqlFu based on three equally important principles:

User Friendliness - Versatility - Performance

SqlFu supports

• SqlServer 2005+
• MySql
• Postgresql
• Oracle (partial, no paging)
• SqlServerCE 4 (new in SqlFu 1.1)
• Sqlite (new in SqlFu 1.1)

User Friendly

Intuitive usage and automatic multi poco mapping by convention, similar to EF. Multi poco mapping automatically works with pagination without any special setup.

var db= new DbAccess(connection,DbType.SqlServer);

//usual stuff
db.Get<Post>(id)

db.Query<Post>("select * from posts where id=@0",1);
db.Query<dynamic>("select * from posts where id=@0",1);

//you can pass ordinal params or anonymous objects
db.Query<Post>("select * from posts where id=@id",new{id=1});

db.ExecuteScalar<int>("select count(*) from posts")

//insert 
var p= new Post{ Id=1, Title="Test"};
db.Insert(p);
p.Title="changed title";
db.Update<Post>(p);

//paged queries , result contains Count and Items properties
var result=db.PagedQuery<Post>(0,5,"select * from post order by id desc");


//Multi poco mapping by convention similar to EF, no special setup

public class PostView
{
   public int Id {get;set}
   public string Title {get;set;}
   public IdName Author {get;set;} 
}

public class IdName
{
   public int Id {get;set;} // <- Author_Id
   public string Name {get;set;} // <- Author_Name
}

//'Author' is automatically instantiated and populated with data. The convention is to use [Property]_[Property]
var sql=@"
select p.Id, p.Title, p.AuthorId as Author_Id, u.Name as Author_Name 
from posts p inner join Users u on u.Id=p.AuthorId
where p.Id=@0";
var posts=db.Query<PostView>(sql,3);

//complex type mapping AND pagination with no special setup
result=db.PagedQuery<PostView>(0,10,sql,3)

Rules

• All the parameters in sql must be prefixed with '@' . The specific db provider will replace it with the proper prefix.
• Enums are automatically handled from int or string when querying. When insert/update they are treated as ints.
• Use the [InsertAsStringAttribute] to save it as string
• Multi poco mapping is done automatically if a column name has '_'.
• Any property/column which can't be mapped is ignored
• However an exception is thrown if you want to assign a value to an object type for example, or null to a non-nullable

Attributes

[Table("Posts", PrimaryKey="Id" /*default*/,Autogenerated=true /*default*/)]
public class Post
{
 public int Id {get;set;}
 [QueryOnly]
 public string ReadOnly {get;set;} //any property marked as QueryOnly will not be used for insert/update
 [InsertAsString]
 public MyEnum Type {get;set;}// any property with this attribute will be converted to string
}

Versatility

//custom sql for those special cases
db.WithSql("select * from posts").ExecuteQuery<MyType>(reader=>{ /* mapping by hand */  })

db.WithSql("select item from myTable where id=@0",3).ExecuteScalar<myStruct>(result=> /* conversion by hand */)

//want to always use that mapper for every query
PocoFactory.RegisterMapperFor<MyType>(reader=> {/* do mapping */});
db.Query<MyType>(sql,args) // will automatically use the registered mapper instead of the default one

//same with converters
PocoFactory.RegisterConverterFor<myStruct>(obj=>{ /* do conversion */}) 

//or for value objects
PocoFactory.RegisterConverterFor<EmailValueObject>(obj=> new EmailValueObject(obj.ToString()))
db.ExecuteScalar<Email>("select email from users where id=@0",8)

//execute some command processing before query
db.WithSql(sql,args).ApplyToCommand(cmd=> { /* modify DbCommand */}.Query<MyType>()

Multi Poco mapping

As shown above, the only thing you need to do is to name the column according tot the [Property]_[Property] format. This feature is designed to be used for populating View Models where every object is a Poco with a parameterless constructor.

However if needed you can customize the instantion of any type (except the main Poco itself). Let's suppose you have this setup

public class Address
{
  //no parameterless constructor
  public Address(int userId)
  {
      UserId=userId;
  }
   
 public int UserId {get;private set;}
 public string Street {get;set;}
 public string Country {get;set;}
}

public class ViewModel
{
 /* ... other properties... */
 public Address Address {get;set;}
}

db.Query<ViewModel>("select u.* , addr.Street as Address_Street, addr.Country as Address_Country from users u, addresses addr where u.Id=4")

Ok, maybe the sql itself isn't very correct, that's not the point. The point is you want to populate that ViewModel and those are the relevant columns. Since the Address object requires the userId, you can configure the DefaultComplexTypeMapper to use this for instantiating Address

  DefaultCompexTypeMapper.ToCreate<Address>(user=> new Address(user.Id));
  

The lambda is basically a Func<dynamic,T> and in this example the ViewModel is passed on as the dynamic argument.

What if you want to use your very own complex type mapping with any convention you like. It's a bit tricky but it's not hard. You just need to implement the IMapComplexType interface then assign it (you can also subclass the DefaultComplexTypeMapper class).

public class MyComplexMapper:IMapComplexType
{
 /* implementation */
}

PocoFactory.ComplexTypeMapper= new MyComplexMapper();

Note that the mapper should act as a singleton so it has to be thread safe. When implementing a complex mapper you have 2 ways to do it: in the MapType method or in the EmitMapping method. The first method is for normal people, the second involves emitting IL code with Reflection.Emit so it's aimed at the hardcore masochists. However, the second method is usually the most performant one. The SqlFu automapper will try to use the EmitMapping method first, but if it returns false, it will call the MapType method instead.

Hint: if you go for the first method and you want to set a property via reflection, use the SetValueFast extension method (around 5-7x faster than reflection) defined in CavemanTools (used by SqlFu) and the same for the getters.

Performance

Now that's an interesting topic. Initially I've run the dapper.net tests but those tests only measure the performance to retrieve on entity. Or a more common scenario is to retrieve more than one row usually with pagination involved. So, I've setup my own tests for this cases (you can find an extended version in the Benchmark project).

Let's see how SqlFu compaers to other micro - orms. You should take these numbers with a grain of salt since every micro/orm has different features which affects the outcome.

The numnbers are for 500 iterations with 10 iterations warm up on SqlServer 2008 Express

Simple query retrieve 7 rows

select * from posts where id>3

• SqlFu: 105 ms
• Dapper.Net: 100.8 ms
• PetaPoco: 103 ms
• ServiceStack.OrmLite: 149,88 ms
• FluentData: 491,99 ms

---

Another round

• SqlFu: 94,549 ms
• Dapper: 101,8998 ms
• Peta poco: 102,2589 ms
• OrmLite: 146,5227 ms
• FluentData: 416,2161 ms

Now, in fairness running the benchmark multiple times gave me different results for the top 3: it was either SqlFu, either Dapper.Net, either PetaPoco with a difference between 1-10ms. I say 5-10ms (for 500 queries) is not a difference which will matter in real world usage. The only consistence was that OrmLite was always place 4th and FluentData last.

Paged query to retrieve 5 rows

select * from posts where id>3 limit 0,5

Other micro orms don't support pagination directly or I haven't found the way to enable it.

• SqlFu: 188,474 ms
• PetaPoco: 189,1739 ms

Again, running it multiple times gave me different results, sometimes the difference was quite high (around 20-50ms) but there was no consistent winner. I call it a tie, both are equally fast

Multi poco mapping

Well, only another micro-orm supports the same conventional mapping as SqlFu so I've compared only those two. Dapper and PetaPoco both suport multi poco mapping, but it's a bit tricky (it's not straightforward) and it allows only up to 5 pocos to be involved.

SqlFu and FluentData don't have this limitation and the syntax is as it is with any query. ServiceStack.OrmLite considers complex mapping only from a json blob so it's not applicable here.

• SqlFu: 80,2782 ms
• FluentData: 306,2088 ms

Get by id

Finally the dapper .net benchmark results modified to include SqlFu. I've left the results as they were

hand coded took 94ms
Mapper Query (non-buffered) took 98ms
Mapper Query (buffered) took 101ms
Dynamic Mapper Query (buffered) took 101ms
Dynamic Mapper Query (non-buffered) took 102ms
SqlFu Query single   took 105ms
SqlFu Get took 106ms
SqlFu Fetch   took 106ms
PetaPoco (Fast) took 107ms
Dapper.Cotrib took 112ms
Dynamic Massive ORM Query took 115ms
PetaPoco (Normal) took 115ms
OrmLite QueryById took 119ms
BLToolkit took 152ms
OrmLite QuerySingle took 161ms
Linq 2 SQL Compiled took 182ms
Simple.Data took 183ms
SubSonic Coding Horror took 225ms
Entity framework CompiledQuery took 247ms
NHibernate SQL took 249ms
NHibernate Session.Get took 269ms
NHibernate HQL took 280ms
NHibernate Criteria took 348ms
Linq 2 SQL ExecuteQuery took 406ms
Entity framework ExecuteStoreQuery took 1179ms
Linq 2 SQL took 1186ms
NHibernate LINQ took 1322ms
Entity framework ESQL took 1342ms
Entity framework No Tracking took 1741ms
Entity framework took 1773ms
SubSonic ActiveRecord.SingleOrDefault took 7448ms