Your favorite O/R Mapper?

Tell me which O/R Mapper is your favorite and why. I'm using Paul Wilson's O/R Mapper, and It's been great so far but my needs are growing as projects become more complex. Is there anything new going on in this arena, or is LLBLGen Pro and EntityBroker still the top two?

Re: Your favorite O/R Mapper?

Still he two top.

Re: Your favorite O/R Mapper?

does someone have a comperation matrix between the two ? :D

Re: Your favorite O/R Mapper?

Thona will flame me. I respect his knowledge and his product, but here's my two cents. First off, I don't have a comparison matrix. I still highly suggest LLBLgenpro though as it has a lower learning curve. I think thona's Entity Broker has more features, but it's also more difficult to use. And if you're like me, you've got to have solutions quickly... and using an O/R mapper needs to be a quick and easy process, with minimal learning time and maximum efficiency. I think that Frans Bouma's product is superior when the requirements listed above are high on your priority list. I feel as though Entity Broker would perhaps offer more advantages if you had the time to learn it, and deal with its idiosyncracies. The new entity broker might be awesome, but it's still in the early stages of development and therefore it's difficult for me to say. Frans is building a new LLBLGenpro as well (I assume to coincide with the release of asp.net 2.0). I feel as though llblgenpro will give you everything you need and is a major leap over writing your own data access layer. Plus you can go from relational model to compiled object model in less than 5 minutes. That's pretty cool. Cheers, -a

Re: Your favorite O/R Mapper?

I would say LLBLGen Pro and EB are the top two getting exposure on this board. Whether or not they would be considered the top two best is questionable. I believe LLBLGen uses DataSets and anything using DataSets in my book loses big marks. One problem I had with EB was that it lacked Oracle support. However I believe it supports that now. A couple to look into include DataObjects.Net and Genome.

Re: Your favorite O/R Mapper?

Almost all mappers mentioned in this forum are too complicated. By that I mean that the syntax and API are not clear or intuitive enough for me to be able to understand and use within an hour or so of experimentation. (especially DataObjects.Net and Genome). On the other end of the spectrum is the Wilson OR Mapper, which IMHO has the best interface for the "average" developer with beginner to intermediate OO knowledge - at a price of more advanced features such as a decent query engine and support for all but the simplest inheritance. (but maybe it'll evolve....) The big two mentioned above (EB and LL) do a good job of being easy (intuitive) to use and having powerful features, which is why they will remain popular. Another thing that is attractive about Wilson's is that it is the most "non-intrusive" or mapper I've see so far - no "PersistentObject" base classes. In fact the business objects do not need to have any reference the the mapper itself, which is how it should be. (Yes you need to use the ObjectHolder for lazy loading of to-one relations, but that's a questionable need in many cases.)

Re: Your favorite O/R Mapper?

Here are some CodeSmith templates. These may not qualify as O/R Mappers, but they are very helpful, and FREE :) Data Layer This little friend is very similar to a business object model defined in a Wrox's book, but with many nice enchancements. This script will generate standard CRUD Stored Procedures, plus several kind of lookup stored procedures based on defined foreign keys (Customer_GetByCompanyID, Customer_GetByTaxID, Cusotmer_Find, ect ect). It will also create C# Table Objects, Row Objects and Collection objects to hold everything. The objects will even have sub-collections. Example: Company collection can have a Employees collection in it, although you have to manually load. It comes with a simple transaction manager, and all your DB activity can be wrapped in a transaction. All your Table objects have neat methods like .Insert, .Update, .GetByXXX, .Find, and so one... Its really pretty good, but you kind of have to build your own Business Layer around the generated code if you really want to take the bull by the horns. The script has a few small bugs (I fixed them on the version I downloaded), and you may have to tweak some of the generated code a bit. Stored Proc Wraper with MSDAAB support This guy creates all your CRUD stored procs, plus a few simple FK lookup stored procs (not as many as the one above..bu it is still very nice. It then has a second script you run that creats C# wrapper code for the stored procs. It will actually create C# wrapper code for any stored proc you tell it to, not just the ons it generated. Its really pretty good. What it produces is good enough for most small applications, and if need to take things a step further you can handcode a small business object layer to sit on top of it.

Re: Your favorite O/R Mapper?

::Here are some CodeSmith templates. These may not qualify as O/R Mappers, but they are ::very helpful, and FREE :) You fail also to mention that they are useless. They basically are not O/R mappers. It is not that they do not qualiy as O/R mappers. THey are - sorry - stupid inflexible DAL's. Missing are, among tons of other things: * Inheritance (i.e. the ability to work with OBJECTS, not structs). * Query subsystem. Sorry, "GetByCompanyId" is about as idiotic as it can get. How about "get me all customers having open invoices" They are a nice step forward, but anyone putting something like this into the foundation of his architecture should seriosuly get a beginner book into architecture of persistence layers - and read the first chapters on what a persistence layer should provide ans why.

Re: Your favorite O/R Mapper?

well OK....Thank you for the informative and diplomatic response.

Re: Your favorite O/R Mapper?

Contrary to Thona Theory (you know the one where Thona is right and nobody else knows what the hell they are doing), I believe these templates are a decent starting point. You see the great thing about CodeSmith, and unlike most O/R mappers, is that YOU, the developer, have control over the output of your templates and hence control over your own architecture. While I do believe that EntityBroker is a good product, I hate the thought of being locked into any particular architecture, any particular way of doing things, and I really hate the thought of being dependant on someone else to make a change to their product to better suit my needs. CodeSmith focuses on enabling as many code generation scenerios as possible and not on trying to dictate any particular architecture.

Re: Your favorite O/R Mapper?

Contrary to what you say, though, Codesmith makes a piss louse O/R mapper. And there is a reason. The problem with the CodeSmith code generator is that it can only look at the OBJECT. Not the object in context. Unless you proviude templates for the complete O/R mapper, yo ubasically end up with a bad coded and powerless mapper. The problem is two fold: * With Codesmith you basically geenerated the code at compile time. This sucks, as this code is not extensible ad runtime. The EntityBroker is compiling the final schema when you start the program, allowing things like dynamic extensions of the mapped classes. * Codesmith focuses on the class. THis is nice, but way not enough. You have to focus on the classes you map - in context of all other classes. So, unless someoner stands up and starts writing a CodeSmith based O/R mapper, Codesmith is not even in a leage to seriously talk about mapping objects. Anything that maps one class (no inheritance) to a database table is basically just a toy - stuff like inheritance etc. is what makes a mapper valuable. So eric, sorry. Codesmith is not better as a tool to write your own O/R mapper as VS.NET is. But it is simply not even the beginning of an O/R mapper in itself.

Re: Your favorite O/R Mapper?

I did not claim that CodeSmith was a full-blown O/R mapper. In fact, CodeSmith does not currently support any mapping capabilities by default (although it is 100% extensible and those features could easily be added on). You see not all people believe in Thona Theory, some people want simple CRUD wrappers to their databases and some people want really complex OO models driven from an O/R map. The great thing about CodeSmith is that it can do either. Also, unlike O/R mappers, CodeSmith is not limited to the DAL as it can generate code for whatever you can dream up (stored procedures, data access classes, business classes, presentation layer, documentation, etc). You did not seem to address the fact that people are effectively locked into Thona's way of doing things when they go with EntityBroker and at Thona's mercy to implement a feature (no offense, but that could be a while judging by the EntityBroker 2004 release). I personally would never live with those constraints no matter how good or bad your architecture is. In this industry things change quickly and I'm just not going to lock myself into some black box and hope for the best.

Re: Your favorite O/R Mapper?

I have to agree with Eric, I don't think I saw anywhere where he reported CodeSmith to be a full blown O/R mapper. That's the beauty with CodeSmith - it's simply an enabler of a solution. It, by no means, provides a full end-to-end solution for O/R mapping, but it provides a *great* environment to build your own. And, as Eric mentions, the one big problem with O/R mappers I've seen out there is that they are a big black box. You point it somewhere, define your relationships, and press the button. You have no say over the code it generates. Perhaps LLBLGen and EB are different. I'll admit, I have no experience with either. But, I know I didn't like the J2EE world and entity beans for that very reason: The specs left that open to the application server vendors, and you were basically bound to the IDE that generated the O/R code. I never like when VS.NET generates code for me; didn't like a few years ago, still don't like it now. VS.NET has gotten better about that, but why would I move back to that with a EB or LLBLGen based solution? -Jason

Re: Your favorite O/R Mapper?

I think you missed the _ENTIRE_ point of CodeSmith. It's entire premise is that it is 100% extensisble with the templating framework. _YOU_ have to provide the templates to generate _YOUR_ O/R Mapper the way _YOU_ want it. _YOU_ have to create the templates, but then _YOU_ control how simple or complex they are. The Templates are only as good as thier author. I don't have any experience with your product so I can't speak to it, but I do have experience with LLBGen (not pro), and CodeSmith. I stopped looking when I found CodeSmith since it allows me to do things my way.

Re: Your favorite O/R Mapper?

bwha-wha-what ?!?!?! > THey are - sorry - stupid inflexible DAL's. If you write a DAL template, you get a DAL class...if you write a full set of architecture templates (with biz obj) you get full architecture > * Inheritance (i.e. the ability to work with OBJECTS, not structs). The templates my company uses provide us a with a common (publishable) data structures, full DAL (and sprocs) supporting all table relations and 99% of query needs based on keys, indexes, and metadata, and a full business object layer including complete internal and relational validation. Almost all of our code is generated into "actively generated" base classes and anything custom we place in the derived objects. >* Query subsystem. Sorry, "GetByCompanyId" is about as idiotic as it can get. How about "get me all customers having open invoices" Problem with a system designed to focus on "get me all customers having open invoices" is that the bulk of all data calls are by key or relation...but because those custom queries are sometimes needed our templates generate our query sprocs with the ability to accept additional query arguments... > The problem is two fold: > * With Codesmith you basically geenerated the code at compile time. This sucks, as this code is not extensible ad runtime. The EntityBroker is compiling the final schema when you start the program, allowing things like dynamic extensions of the mapped classes. I consider "generated at compile time" an advantage...runtime O/R mapping = reflection = sloooowwww. I have yet to find an O/R mapper that can perform anywhere near as well as pure clean tightly compiled code. Code generation gives me the development advantage of not having to code mundane tasks with the performance of strongly typed tightly compiled code. No contest IMHO. > * Codesmith focuses on the class. THis is nice, but way not enough. You have to focus on the classes you map - in context of all other classes. When using a database schema as input, much of the notion of context is contained within the data relations and table relations. If that does not provide enough contextual or business definition you can always use an XML schema to drive your templates and therefore *completely* define your object space before gen'ing. And as you define new business rules or relations you can modify your xml and your templates to understand the new business concepts. You own the schema so it can be as advanced or as simple as you'd like. We tried (unfortunately) a couple runtime O/R mappers on a project for one of our larger clients....HUGE mistake. Performance was shameful and scalability was out the window. We have since engineered a full set of architecture templates that generate 90% of all the code that we produce. Now, our time-to-market is of course way faster, and our application's performance is amazingly good. As we improve our architecture, we improve our templates, and instantly those changes are reflected in all of our objects (well, ok, not instantly, it takes 2 maybe 3 seconds to completely regenerate everything). We've been able to make our object model so easy to use as developers that new employees are up to speed and coding within a day. In fact I'm not sure our junior developers are even aware of the data model or how the business objects get and persist their data...they just do. Company company = new Company(companyId); and company.Save(); are pretty damn simple...no hand coding for any of it. Also, unless I missed something when using them, runtime O/R mappers also = dynamic sql. shame shame shame. If I ever caught one of our developers using dynamic sql on a performance critical (heck on any) project I would puke first and slap them across the back of the head. Any halfway respectable sql architect will absolutely demand stored procedures be used for both performance and security.

Re: Your favorite O/R Mapper?

I think code generation is great -- at least if the only option is doing it all manually! :) Seriously, O/R mappers may not be for everybody, but lets at least get the facts right. Most good O/R mappers have ways to avoid reflection and perform very comparably. I know tests with mine perform better than stored procs and datasets in some cases, because I load objects internally with a datareader which is much faster than datasets. True, if you want to load 1000's of records then the typeless dataset will be some faster, but that's a questionable design usually, and it isn't due to stored procs vs dynamic sql. Finally, while O/R mappers may prefer dynamic sql, since its much more flexible and just as performant with modern databases, most good ones can use stored procs too! I've worked on systems in my stored proc days where now I know the stored procs were actually slower since they had to internally create dynamic sql to be very flexible, and they had the same security "issues" due to their needs to work with tables directly. I'll admit this "issue" is important to many, but you can use stored procs with mappers, and you certainly can close most security "issues" even with dynamic sql if you try to. Anyhow, if code gen works for you then great -- seriously its better than all manually. Oh, and after reading Eric's posts, I just want to add that I agree its great to not be tied in to any black box -- but the question is do you have time to do it all yourself, when you create the templates, and when you have to update and regen for changes. If you have the experience and time to build great templates then it may be justified, but if you lack the rampup time or proper experience then a black box may instead. Again, its not that one is better than the other -- it depends -- lets just be factual here.

Re: Your favorite O/R Mapper?

I mean not to discount all O/R mappers as a solution. I speak based soley on the ones I have used. Personally, I just have trouble accepting that determining anything at runtime that could have been determined at compile time can perform as well. Obviously, if those things are left for runtime that work will have to be done then and that represents extra work for the application. If the advantages of runtime mapping are a requirement, the runtime O/R mapping is obviously a great solution. But I've seen too many developers incur the cost of runtime mapping for no other reason that develop-time convenience. It's great that Wilson uses readers internally, but you imply that code developed against sprocs doesn't - simply not true. Our architecture uses datareaders exclusively, both for performance and because the nature of our applications requires very large sets of data, which would kill memory usage on an app server in a dataset world. As far as dynamic sql goes, yes its true that database developers have greatly improved sql compilers so dynamic sql isn't nearly the hit it used to be. But again doing work at runtime that could have been done before doesn't make sense if the work doesn't change. A compiled stored procedure (albeit one that does not build up dynamic sql internally) will simply perform better than dynamic sql because the query plan is already calculated. It seems to me that the problem with an all-dynamic-sql solution is that "dynamic" simply isnt needed in most interactions. As I said before, the majority of queries will be based on keys, indexes, etc - for which dynamic sql is not needed. In the few cases where dynamic sql for query flexibility is needed we use it. But most O/R mappers dont provide us the flexibility to determine when to use that option - its *all* dynamic sql, and for no other reason than developer convenience. As for "the time to do it all yourself", I've perused the CodeSmith forums before and seen many public domain template sets providing full architectures. And if a more custom architecture is needed, neither those templates nor O/R mappers will provide a solution anyway. But template-based code generation provides the platform and flexibility to build your own generatable [is that a word? ;)] architecture.

Re: Your favorite O/R Mapper?

"yes its true that database developers have greatly improved sql compilers so dynamic sql isn't nearly the hit it used to be." If your queries need work in your ORM, then profile them. If you see non-sargeable clauses, modify your generator. If you know how to write a performant query, you should be able to write a query generator just as performant, without all the work. Plus, you don't have to depend on devs who may or may not know how to write a good query. Devs who think FK's are slow, nulls are just a nuiscance, "*" is a shortcut, and haven't even ever heard of a sargeable clause. "But again doing work at runtime that could have been done before doesn't make sense if the work doesn't change. A compiled stored procedure (albeit one that does not build up dynamic sql internally) will simply perform better than dynamic sql because the query plan is already calculated." 1: You can always cache your queries if your profiling tells you that's a performance bottleneck. I'd be very surprised if it were though. 2: Note true in MSSQL2K, but you didn't specify your db. "neither those templates nor O/R mappers will provide a solution anyway." An ORM is just a way to map your objects to a relational backend. That your Domain Logic would even care wether you use a templated ActiveRecord, or an ORM says there's something wrong IMO.

Re: Your favorite O/R Mapper?

I too once had trouble "accepting" the performance of mappers -- until I saw it for myself. That forced me to do new research -- and what I had been taught was often no longer true. So I no longer see much, if any, "cost" associated with runtime mapping vs. other methods. Also, note that there has been a lot of discussion on these forums and in the various blogs about the old belief that stored procs perform better due to pre-compilation and caching -- all I can say, since I'm not the expert on that, is that its not so cut-and-dry anymore ! I also don't want to imply there is no place a store proc won't perform better, but its rare. Where I do NOT agree is that most applications don't need a "dynamic" flexible query engine. Most every "real" application I've ever been involved with demanded that the users be able to search and sort on just about any combination imaginable, as well as limit returned fields. Its exactly that type of terrible to write and maintain set of stored procs, and all the layered interaction in the application that work with those stored procs, that made me love mappers. Granted, generating it all solves that too, but I've yet to see anyone successfully generate a solution that actually had as much flexibility as my "real" applications have demanded. Sorry if I implied you didn't use readers -- my intent was simply to show that there are cases where mappers actually outperform what is very commonly used in the .NET world.

Re: Your favorite O/R Mapper?

I disagree. Those particular templates might not be of use to everyone, but thats the great thing about CodeSmith, you can write the templates that are of use. I have given 3 user group presentations with a coworker of mine on CodeSmith and have recieved awesome feedback from each one. CodeSmith gives the developer the power to decide how to implement features and ideas. When you use an O/R Mapper you don't necessarily have that same functionality. In my experience O/R Mappers attempt to be generic enough to solve the world's data problems. Some of them have more success than others. I will fully concede that CodeSmith isn't an O/R Mapper. That also doesn't mean O/R Mapper is the ONLY solution to every problem. Some of the things we talk about in our presentation is the power that CodeSmith affords you... CodeSmith allows developers complete control over ever facet of their architecture, from Sql to Presentation...I don't know of many pure O/R Mappers that can say this. CodeSmith provides a great mechanism for implementing and adhering to standards. CodeSmith can be used for repetive tasks beyond just writing code. Take for example a script a coworker of mine wrote to output a batch file for executing isqlw statements for batch execution of ddl scripts. CodeSmith is very ASP.NET and ASP like. If you have worked in ASP.NET or ASP for even 5 minutes, you will be able to pick up the syntax very easily. Once we had worked out the design of our classes we were able to implement them as CodeSmith templates in about 2 hours time. CodeSmith usage and adoption can be very gradual, we started out using it just for strongly typed collections. Once we saw the power of template based code generation we started applying it to more and more of our archictecture. I hear people say template generation takes too long because you still have to do the work, I would counter that statement is somewhat false. With CodeSmith I can create 1 perfect data class, 1 perfect edit page, 1 perfect save stored procedure. Chances are my code will be very repetitious and allow me to build this common code into a template. We have built our entire framework around CodeSmith and the environment that Eric has supplied with it. Heck we even have a pretty "magic button" GUI that executes all of our templates based on the database tables we check to generate. Here are the templates we used in our presentation, they are 100% identitical to the templates we use at work, but with some of the namespaces changed. http://www.objectfoundry.com/templates.zip I have everthing from Sql to UI in there. They might not be too useful to everyone else because they are based around our architecture and our framework, but they could be used for ideas on how to begin your own architecture.