PetaPoco - What's new in v4.0

Wednesday, 1 June 2011

PetaPoco v4 is almost ready. Here's a write up on what's new and changed.

This post is about PetaPoco - "a tiny ORMish thing for your POCOs". Read more on the PetaPoco Project Page.

Replaced the Transaction property with a method

I knew that making PetaPoco's Transaction property a property was a mistake when I did it, but somehow it got through. The big problem with this is the debugger will attempt to evaluate it and inadvertently start new transactions - oops.

The Transaction property has been marked as obsolete in the github master branch for a while now, but if your just using the NuGet packages you'll find this property has gone missing and has been replaced with the GetTransaction() method.

You'll need to change:

using (var scope = db.Transaction)

to this:

using (var scope = db.GetTransaction())

For all the changes in the release, this tiny change is the reason for clocking the major version to 4 - since it breaks the API and I'm complying with semantic versioning.

Multi-Poco Queries

The major new feature in this version is support for Multi-Poco queries. I've written about this before (see here).

The functionality hasn't really changed except I've removed the <T1, T2, T3, T4, T5, TReturn> generic overloads. I've done this because .NET 3.5 only supports up to 4 argument parameters. If you're using .NET 4.0 and need the extra arguments it should be trivial to write these as extension methods (though I haven't actually tried to confirm this works).

Alternatively you can call MultiPocoQuery directly:

IEnumerable<TRet> MultiPocoQuery<TRet>(Type[] types, object cb, string sql, params object[] args)

This method accepts the POCO types as an array - rather than as generic arguments.

Support for IDbParameters as SQL arguments

PetaPoco now supports directly passing IDbParameter objects to a query. This is handy if PetaPoco doesn't correctly map a property.

For example the SQL Server driver doesn't handle assigning DbNull to a VarBinary column unless the parameter is configured with the correct type. To work around this you can now do this:

databaseQuery.Execute("insert into temp1 (t) values (@0)", 
                new SqlParameter() { SqlDbType = SqlDbType.VarBinary, Value = DbNull.Value });

One interesting side effect of this is that you can also return an IDbParameter from the PetaPoco.IMapper interface to globally override PetaPoco's default parameter mapping functionality.

(Thanks to Adam for this idea)

Ability to disable auto-select generation on per-query basis

PetaPoco does a reasonable job of guessing when it should automatically insert SELECT clauses - but it's not perfect and there are various statements that it doesn't work correctly with. Previously to work around this you had to turn off the EnableAutoSelect property, run your query and then set it back again (all the while handling exceptions).

Now, you can escape any SQL with a leading semicolon to indicate that select clauses should not be inserted. PetaPoco removes the semicolon before passing the query to the DB.


// Leading semicolon in query will be removed...
db.Query<mytype>(";WITH R as....");

T4 Template Improvement - Ability to plugin custom clean up functions

You can now replace the standard T4 template method that's used to cleanup table and column names. In your T4 template, before the call to LoadTables just set the global CleanUp property to a delegate:

CleanUp = (x) => { return MyCleanUpFunction(x); }

T4 Template Improvement - Ability to include views and filter by schema

The T4 template can now generate classes for either all schemas in the DB, or just a single schema. To only include tables in a particular schema, set the global SchemaName property before calling LoadTables. You might also like to qualify the generated classes with a class prefix:

SchemaName = "MySchema";
ClassPrefix = "myschema_";

If you want the primary schema and a specific other schema, or multiple schemas, just have multiple files with a different SchemaName setting in each.

You can now also get the T4 template to generate classes for views (in addition to tables):

IncludeViews = true;

These changes are pretty experimental, because I haven't had time to test on all the different database engines... if you find problems, please try to find a fix an let me know.

Improved multi-threaded support with ReaderWriterLockSlim

PetaPoco now uses ReaderWriterLockSlim for protecting access to shared data which should improve multi-threaded performance - such as in web apps.

Support for protected constructors and properties

PetaPoco can now access a POCO's private and protected members - this includes private constructors and property accessors.

New Page<>.Context Property

I've found a number of cases where I've been using the PetaPoco Page object as the strongly typed model object for MVC views and needed to pass one extra piece of data. Rather than use ViewData or declaring a new strong type just for that view, I've added a Context property to the Page object. Its defined as type Object and can be used to pass a little extra context to the view.

For example, in Showcase Lane I have a partial view that displays a page of site preview thumbnails. This works fine when I have a page of sites to show, but when the list is empty I want a context sensitive "blank slate" message to be displayed. This could be "You have no favourites", or "No More Sites", or "You haven't liked any sites", etc...

To handle this, in the controller I set the Context property to something that indicates the type of blank slate message to display if there's no data.

Bug Fixes

  • Unsigned integral types in MySQL now generated correctly
  • Fixed T4 template when a generated column property name conflicts with the generated class name
  • T4 template support for SQL Server Time type
  • Fixed support for SQL Server Geometry and Geography query parameters


All this is available in the github master branch now. NuGet package coming soon.

« Startups and .NET PetaPoco - Mapping One-to-Many and Many-to-One Relationships »


So pleased you've added the Type[] support in MultiPocoQuery() I didn't expect that. Now I can multi-poco as much as I want thanks :)

1 June 2011 09:21 PM

Any ETA on support for LINQ queries?

2 June 2011 07:09 AM

@Andrés - yep, ETA = never. SubSonic's dreadful LINQ performance was the original motivation for PetaPoco, so to put it in would be to go against the whole point of PetaPoco. Sorry.

2 June 2011 01:20 PM

We're loving us some PetaPoco.

I'm using it in an HD content delivery application that serves over one million requests per day, slowly replacing SubSonic.

Thanks for continuing to improve it!!!

7 June 2011 11:00 PM

And how about caching like it was made in Dapper ? ( Concurrent dictionary)

10 June 2011 04:58 PM

@Kamil, not exactly sure what you're asking... PetaPoco already caches all the object factories it creates.

13 June 2011 01:50 AM


LINQ performance don't need to be dreadful! It could translate LINQ expressions to PetaPoco queries and then the only overhead would be the actual translation, not the execution of the LINQ itself. I think that LINQ makes it soooo more readable and strong-typed (not that this is much important in the days of TDD)...

Anyway, the code is there, if anyone feels interested in this the let he/her write it!!! I think your PetaPoco to be extraordinary.

19 July 2011 07:01 PM

Hi, Great product, although I'm having a bit of trouble with it handling enums:

Many thanks,


27 July 2011 08:19 PM

@Filipe: The problem with LINQ is the translation itself is very slow and without pre-compiling queries (which is messy) happens everytime the query runs.

@Steve: Stack Overflow question asked. I'll think about adding support for this in the next major version.

27 July 2011 11:21 PM

I am planning to use PetaPoco for an upcoming project. I haven't seen it mentioned yet, but support for multiple result sets would be excellent. It's part of the performance equation. Sending multiple queries to the database in one shot when applicable would reduce hits to the database.

Keep in mind that some might be simple single-entity queries whereas others might be joins (multi-poco.)

Nice work!

1 August 2011 09:41 AM
Mehmet Cakoglu

Hi, really good product. Thanks for your efforts :)

But, we need a documentation. I can't find everything in a central location.

Do you plan to create detailed documantation/Wiki for "PetaPoco" Project?

Thanks again :)

23 November 2011 04:21 PM

Question regarding the PrimaryKey attribute: does it only allow for int or longs? I have a table (legacy) that uses a string as the primary key, and PetaPoco throws an excetion looking for column 'ID' on an update() operation.

15 December 2011 11:23 PM

One suggestion, I don't think you should compete with Massive with line numbers and number of cs files. It's a single file but with 2000+ lines long, with new features adding in, I can see it can be getting bigger and bigger. Please, make it a project and break the classes into separate files and without this limitation, the whole structure can be much better without so many switch statements.

I don't think any one minds adding an extra dll dependency - and Dapper's nuget package already is a dll. Also I think you can request a resharper lic from Jetbrain for OS dev. Rescan your code, you'll be astonished how clever a modern tool can be.

20 January 2012 10:04 PM

Hi! please help me! Question: The stored procedure has an [ReturnValue] parameter How can I use PETAPOCO to call the stored procedure and get the returnValue?

the procedure like this: create procedure TestSP AS BEGIN RETURN 1 END

26 April 2012 07:48 AM

Hi! Do you know when does the Peta Poco supports generating the mapping files from the SQL Anywhere 12 database?

22 April 2013 08:54 PM

Leave a comment

Name (required)
Email (required, not shown, for gravatar)
Website (optional)
Your Message
Leave these blank: