PetaPoco - Experimental Multi-Poco Queries
Saturday, 14 May 2011
PetaPoco now has experimental support for mapping result sets onto more than one POCO type, giving yet another way to handle SQL joins.
This post is about PetaPoco - "a tiny ORMish thing for your POCOs". Read more on the PetaPoco Project Page.
Firstly, credit needs go to Sam Saffron's Dapper project for the idea behind this. PetaPoco's multi-poco query support is very similar to what Dapper calls multi-mapping however PetaPoco's implementation is considerably different, the way the split point between columns works is different and it can also automatically guess and assign the object relationships between POCOs.
Background
The idea behind multi-poco queries is to make an SQL JOIN query and have the columns from each returned table automatically mapped to POCOs representing those tables. In other words, instead of one row being mapped to one POCO, the first N columns are mapped to one POCO, the next N columns to another etc...
Usage
Here's a simple example of a multi-poco query:
var sql = PetaPoco.Sql.Builder
.Append("SELECT articles.*, authors.*")
.Append("FROM articles")
.Append("LEFT JOIN users ON articles.user_id = users.user_id");
var result = db.Query<article, user, article>( (a,u)=>{a.user=u; return a }, sql);
Some notes:
- The SQL query is returning the columns from two tables.
- The first two generic type parameters to the Query method specify the POCO types that will hold the data for each row.
- The third generic type parameter is the type of items in the returned collection - typically the same as the first table type, but could be something else.
- The Query method takes as it's first parameter a callback delegate that can be used to wire up the relationship between the two objects.
So in this example, we're returning an IEnumerable<article> where each article object has a reference to the associated user through it's user property.
PetaPoco supports up to 5 POCO types and there are also Fetch and Query variations.
Choosing the Split Points
The trickest part of the multi-poco support is deciding where the result sets should be split - ie: which columns map to which POCO's. The approach taken by PetaPoco relies on convention and is pretty simple, but different (and IMO better) than Dapper's.
The columns returned must be in the same order as the generic type parameters used in the Query<> method call. ie: the first N columns map to T1, the next N to T2 etc... Columns are mapped by starting at the left most column using the first POCO, finding a split point and mapping subsequent columns onto the next POCO type.
If a column name has already been mapped onto the current POCO type it's assumed to be a split point. Imagine this set of columns:
article_id, title, content, user_id, user_id, name
and these POCOs:
class article
{
long article_id { get; set; }
string title { get; set; }
string content { get; set; }
long user_id { get; set; }
}
class user
{
long user_id { get; set; }
string name { get; set; }
}
and a query like this:
db.Query<article, user, article>( ... )
The field of interest here is the user_id. When mapping this result set, the first user_id column will be mapped to the article POCO. On seeing the second user_id column PetaPoco will realize it's already mapped mapped articles property and start mapping onto the user POCO.
The final way a split point is determined is when a column doesn't exist in the current POCO type but does in the next. Note that if a column doesn't exist in the current POCO type, nor in the next POCO type it's ignored.
Auto Joining POCO's
PetaPoco can also guess the property relationships and automatically assign the object references on the returned objects.
So instead of this:
var result = db.Query<article, user, article>( (a,u)=>{a.user=u; return a }, sql);
You can just write this:
var result = db.Query<article, user>(sql);
Note two things about this:
- The third return type parameter isn't required. The returned collection will always be of type
T1. - The callback method to setup the object relationships isn't required.
Obviously there's a bit of guessing by PetaPoco to make this work, but it's a common enough case that I think it's worth it. For it to work, T2 through T5 must have one property of the same type on one of the types to it's left. In other words:
T1must have one property of typeT2T1orT2must have one property of typeT3T1orT2orT3must have a property of typeT4- etc...
Also, the properties are searched from right to left. So if T2 and T3 both have a property of type T4, T3's property will be used.
Conclusion and Availability
You might need to read this post a few times to make sense of this new feature, but once you get used to it I'm sure you'll find it a useful addition.
Currently this is only available in a new GitHub branch - MultiPoco. It's totally experimental and hardly been tested, but I'm interested in feedback - please let me know how it works for you.
15 Comments
Leave a comment
Very nice, will try it ASAP. PetaPoco is becoming the no-compromise miniORM of choice.
Interesting!
I haven't looked at the code yet, but here are my initial thoughts:
For example:
class article { long article_id { get; set; } string title { get; set; } string content { get; set; } long creation_user_id { get; set; } long update_user_id { get; set; } }
class user { long user_id { get; set; } string name { get; set; } }
Maybe "CreationUser" / "UpdateUser" could be mapped to the table alias required in the sql code (or the specified alias could be used to derive the property names for the User objects )
Been playing with this feature the last day or two, it works well for me. I had a problem getting a rich poco (I mean a poco containing other pocos eg; article.author) from Database.SingleOrDefault() because the T1...T5 overloads don't exist, but I found Query().SingleOrDefault() works just the same :
var newArticle = db.Query<Article, Author>( "SELECT * FROM article left outer join author on author.id = article.author_id where article.id=@0", 1).SingleOrDefault();
Also, I can envisage needing more than the 5 pocos in a domain entity. Hopefully this is not outside the scope of PetaPoco. I'll look at the code and see how I can move it to using a list of types so there is no limit. Thanks for creating PetaPoco and adding features so quickly this is great work
Well pleased. I was concerned that having nested pocos that represent columns on the same table as the main poco would be a problem, but they way FindSplitPoint() works caters perfectly for it because if the field name is not present in the main poco it moves onto the next poco. I might need to be explicit with the order that the columns are brought back (ie; not select * from X) but that's all I can think of
ps: In the above scenario multi-poco support for insert/update would be useful ie; where the nested pocos I want to insert/update all represent columns on the main table. I don't mean updating multiple tables at once, I mean where I my main poco separates many columns into categorized nested pocos eg; User.AccountInfo.DateAccountCreated, User.ContactDetails.AddressLine1
The example above shows a nice Many-to-One relationship. Imagine we return 10 articles that are all authored by the same user. What will the end result be? There will be 10 articles of course. But what about their "Author" property? Will it point to the same User object instance or will every article point to a different instance but with the same data?
And also: What about One-to-Many relations? In sense of returning articles and their comments? Each row would return article and comment POCOs at the same time. Articles will be repeated over several rows because each will have one comment. How should we handle this situation? Articles have a "List<Comment>" property.
It's obvious that One-to-One relations are easily done with MultiPoco queries. It's definitely not straight forward to see a scenario with at least one side (or both) being a "Many" relation.
Cool, I was just debating between Dapper or PetaPoco, preferring PetaPoco for it's easy templated POCO classes but not wanting to go without the obviously very useful multi-map feature. Thank you for taking time to add it, makes it easy to stick with PetaPoco.
Heh, I am noticing that you always start the post with complex code that then get's easier as you explain more features. I like it very much and I'm going to use PetaPoco on a real project starting next week.
Thanks!
I think I saw on one of your post that if you see typo's to kindly let you know.
The snippet of code under usage has semi-colons, which I think should not be there.
.Append("SELECT articles., authors."); .Append("FROM articles");
I just started looking into this and would like to get away from using EF 4.1. I do like it so far and used the nuget to get it.
Excellent job!
Can PetaPoco handle nested objects and inheritance with one query to db? Massive Micro-ORM has the "splitOn" property, but data columns has to come back in particular order (if order of columns change, it'll break).
For example:
public class Order { public int OrderID {get;set;} public string Desc {get;set;} public List<OrderDetail> OrderDetails {get;set;} public Customer CustomerInfo {get;set;} }
public class OrderDetail { public int OrderID {get;set;} public decimal Amount {get;set;} public string ProductCode {get;set;} }
public class Customer : CustomerBase { public int CustID {get;set;} public string FullName {get;set;}
CreatedDate = DateTime.Now; }
public class CustomerBase { public string Race {get;set;} public string Sex {get;set;} public datetime CreatedDate {get;set;} }
Why only 5 POCO? This means in reality that you can only link 3 acoual one-to-one relations. I need more!? =D
Hi, This whole system lacks complex mapping capabilities and will create more and more hassle trying to load data from database when your object structure gets a little complex. When you have an object that has 8 sub objects and then has 2 lists and each object might have some other sub object, that's when this ORM can do nothing!!
@Alex: Of course! PetaPoco was never intended to be a complex ORM, hence the term micro-ORM. I would never recommend using PetaPoco for a system as complex as what you've described.
@Markus: actually you can link as many as you like, but there just aren't explicit overloaded generic methods for that. You need to use
public IEnumerable<TRet> Query<TRet>(Type[] types, object cb, string sql, params object[] args)which accepts the types as an array, rather than as generic params.@Brad How do you structure the cb for the types array? It doesn't accept the same one as the function with type parameters.