Best strategies when working with micro ORM?

Question

I started using PetaPOCO and Dapper and they both have their own limitations. But on the contrary, they are so lightning fast than Entity Framework that I tend to let go the limitations of it.

My question is: Is there any ORM which lets us define one-to-many, many-to-one and many-to-many relationships concretely? Both Dapper.Net and PetaPOCO they kind of implement hack-ish way to fake these relationship and moreover they don't even scale very well when you may have 5-6 joins. If there isn't a single micro ORM that can let us deal with it, then my 2nd question is should I let go the fact that these micro ORMs aren't that good in defining relationships and create a new POCO entity for every single type of query that I would be executing that includes these types of multi joins? Can this scale well?

I hope I am clear with my question. If not, let me know.

Oxime

I toyed with these micro ORMs myself and ended up walking away from the concept for this very reason. For simple unrelated POCOs, they may be splendidly wonderful, but handling relationships (and especially nested ones) is almost a fail point for them. If you haven't tried NHibernate, I'd recommend it as a performance middle ground between the micro ORMs and entity framework. Additionally, it's mature and supports almost every ORM feature you could think of. – Shellacking

Thanks Chris. Yes, I do know nHibernate and have used it a little. But I need to get raw speed using stored procedures. I am not looking for any advanced feature such as Caching etc. I just need an ORM that can call stored procedure and give me relationships to work with. But so far I haven't found any. Any recommendations? – Oxime

NHibernate can do just that. NHibernate calls them "Named Queries". I don't know of any micro ORM that can handle relationships like you're asking. It's primarily the reason I've stayed away from them myself. Sorry :( – Shellacking

hmm, alright. I will then explore NHibernate. – Oxime

i have used Dapper for a database with 50 tables . Using ORM in other languages for same schema was WAY easier. What needed 50 lines needed one. You dont need to keep identity maps complex join at code etc. I believe that for performance critical things you can use micro orm but for CRUD etc doesnt worth the torture/pain to maintain. – Inkling

My opinion is PetaPoco beats EF hands down as I believe Speed (of application) should always be the the most important factor. Overcoming the lack of support for joins is easy as noted above with Views or SPs mapped to flattened classes (on my development machine I just create tables out of the views/sps and then using a specific naming convention map those 'tables' to classes using the T4 that comes with PP). – Modestamodeste

Answer 1

I generally follow these steps.

1. I create my viewmodel in such a way that represents the exact data and format I want to display in a view.
2. I query straight from the database via PetaPoco on to my view models.

In my branch I have a

T SingleInto<T>(T instance, string sql, params object[] args);

method which takes an existing object and can map columns directly on to it matched by name. This works brilliantly for this scenario.

My branch can be found here if needed. https://github.com/schotime/petapoco/

Cantankerous

Thanks. This may be the best one for Asp.Net MVC but I guess for Asp.Net webforms I probably don't need that. Right? – Oxime

I wouldn't say that. Even in webforms I never used my domain models, but a DTO of sorts that I bound to controls, only pulling back data that I actually needed. – Cantankerous

Great! Your answer gave me a new way of thinking! Your branch will fit perfect for my situation too. Thanks. – Oxime

Answer 2

they don't even scale very well when you may have 5-6 joins

Yes, they don't, but that is a good thing, because when the system you will be building starts to get complex, you are free to do the joins you want, without performance penalties or headaches.

Yes, I miss when I don't needed to write all this JOINS with Linq2SQL, but then I created a simple tool to write the common joins so I get the basic SQL for any entity and then I can build from there.

Example:

[TableName("Product")]
[PrimaryKey("ProductID")]
[ExplicitColumns]
public class Product {
    [PetaPoco.Column("ProductID")]
    public int ProductID { get; set; }

    [PetaPoco.Column("Name")]
    [Display(Name = "Name")]
    [Required]
    [StringLength(50)]
    public String Name { get; set; }

            ...
            ...

    [PetaPoco.Column("ProductTypeID")]
    [Display(Name = "ProductType")]
    public int ProductTypeID { get; set; }

    [ResultColumn]
    public string ProductType { get; set; }

            ...
            ...


    public static Product SingleOrDefault(int id) {
        var sql = BaseQuery();
        sql.Append("WHERE Product.ProductID = @0", id);
        return DbHelper.CurrentDb().SingleOrDefault<Product>(sql);
    }
    public static PetaPoco.Sql BaseQuery(int TopN = 0) {
        var sql = PetaPoco.Sql.Builder;
        sql.AppendSelectTop(TopN);
        sql.Append("Product.*, ProductType.Name as ProductType");
        sql.Append("FROM Product");
        sql.Append("    INNER JOIN ProductType ON Product.ProductoTypeID = ProductType.ProductTypeID");
        return sql;
    }

Lucrece

Hi Eduardo, your answer is helpful but my point is do I have to keep creating these entities(projections)? Eg. I have product with category and that category in turn has department and that department has employees. How will you manage this with PetaPOCO in your real world project? Will you create a simple projection class that will handle all columns row wise rather than building a hierarchical relationship? Is that OK or is it considered to be a bad practise? – Oxime

Answer 3

Would QueryFirst help here? You get the speed of micro orms, with the added comfort of every-error-a-compile-time-error, plus intellisense both for your queries and their output. You define your joins in SQL as god intended. If typing out join conditions is really bugging you, DBForge might be the answer, and because you're working in SQL, these tools are compatible, and you're not locked in.

Streptococcus