Are you tired of writing database access code by hand, but don't want to deal with the bloat of Microsoft's Entity Framework or mess around with complex config files for some of the other ORM solutions out there? I've been using CodeSmith's PLINQO (Professional LINQ to Objects) for over four months now, and I'm quite impressed by it.

LINQ to SQL

My background and experience of being both a DBA and a developer makes me rather hard to please. As a DBA, I'm dismissive and skeptical of magical frameworks that claim to remove the pain of wiring up calls and access to the database within applications. As a developer, even though I love writing stored procedures (or sprocs) and optimizing the performance of data access in general, I'd rather gouge my eyes out with a rusty spoon than have to wire up any more sprocs or database calls in code.

Consequently, when LINQ to SQL was released, it caught my attention because of its ability to project the output of SELECT statements or stored procedures into collections of (mostly) simple objects - or POCOs (plain-old CLR objects). Object-Relational-Mappers (ORMs) are nothing new to the development or database world, but unlike something like NHibernate, which takes what I call the “java approach” (dumbing everything down to the lowest common denominator), LINQ to SQL had intimate understanding of the database under foot and struck me as being a bit smarter than some of the other ORMs out there.

Sadly, it didn't take me too long to find some of the “dumb” mistakes in LINQ to SQL that I've found in many other ORMs, such as data-type coercion problems. But I've gotten around those problems through the use of stored procedures. And, with LINQ to SQL, I'm still able to write sprocs in T-SQL (when needed), finesse them exactly as needed, but never have to write any mapping or plumbing code in my app. Consequently, if it weren't for some bigger, or more problematic, issues with LINQ to SQL, I'd be sold.

The only problem is, Microsoft won't ever address those issues because it wants me to switch to the Entity Framework. But I'm still of the opinion that the Entity Framework is an overly-complex and massively bloated solution for the vast majority of projects.

Bigger Problems with LINQ to SQL

As for the bigger problems that I have with LINQ to SQL, for me those ultimately stem from the way that it “magically” tries to track changes to your objects while they're in memory. Overall, this functionality is fairly cool: When you load in, say, an employee object from the database and then let someone in HR modify that employee's phone-number or salary, those changes are transparently noted by the object itself . When you tell the object’s Data Context to .SubmitChanges() it will wire up a T-SQL statement needed to change those properties as needed.

But when your data context goes out of scope, things get complicated. For example, if you've got a Silverlight application or you're using Windows Communication Foundation (WCF) you'll be marshalling your objects across boundaries, and as you do so, your data context goes out of scope. Or, if you're using LINQ to SQL in an ASP.NET application - and push your objects into the cache, then as soon as your data context goes out of scope (i.e., at the end of the request), then your object loses contact with the data context.

When a LINQ to SQL object goes out of scope there aren't any errors or anything ugly, so you're free to use it in read-only situations without any issue. It's just that when you let users or your application modify that object's data you're in trouble. Getting that object back into the system becomes an amazing pain that requires lots of work-arounds (mostly in the form of ugly hacks).

Ultimately, it was this exact issue that brought me to PLINQO— because CodeSmith touts PLINQO as a solution that inherently implements methods that let you cleanly-and-easily .Detach() and .Attach() your objects as needed. Since my applications rely heavily upon caching, this feature was a major selling point. Happily though, it was just one of many benefits that I've really grown to love since taking the plunge with PLINQO.

PLINQO to the Rescue

PLINQO isn't free. It's just one of the Data Access 'Framework' options that ship with CodeSmith. I paid $199 out of pocket for the personal edition of CodeSmith Professional - which is $100 cheaper than the non-personal edition. And, while you can get PLINQO with the Standard Editions of CodeSmith (which are a lot cheaper), I'd recommend getting the Pro Edition if you're serious about PLINQO (or any data-access framework for that matter), because only the Pro editions support something that CodeSmith calls Merging. (Ultimately, Merging is a fantastic use of CodeSmith logic and functionality along with the use of partial classes that lets you pick up underlying database changes which won't overwrite any explicit mapping or configuration details you've specified in your CodeSmith Project. It's QUITE amazing to see in action and well worth the extra cost.)

Where PLINQO starts to quickly and easily pay for itself is that it will generate your .dbml file for you. You just add a .csp (CodeSmith Project) file to your project, specify which templates you want to use (PLINQO, for example), then give those templates a database connection, and you can wire up an entire .dbml file in a few minutes along with have all of your entities generated as well. Filters and options let you weed out certain object types or even specific tables - meaning that with a bit of practice you can wire up only the objects that you want.

There IS a learning curve involved to using PLINQO, but, happily, CodeSmith has two great introductory videos that will get you quickly spun up on the basics. These videos are listed in the PLINQO section of the site's Frameworks page, and they do a much better job of explaining the benefits of PLINQO and showcasing how easy it is to use than I can. So go check it out if you're interested.

Paying for an ORM?

Having to pay for an ORM solutions sounds like a drag to many developers, especially because there are a number of open-source solutions out there or because LINQ to SQL and the Entity Framework are “free.” But I'm convinced that paying for PLINQO is actually a huge win. Because when you buy PLINQO, what you're actually buying is a very powerful, and very mature, code-generation tool that just happens to be able to spit-out PLINQO. (It also spits out NHibernate, Rocky Lhotka's CSLA, Wilson's ORMapper, and other ORM solutions.)

But, as the saying goes: “good developers write good code, great developers write code that writes good code. “ With CodeSmith, you're putting yourself into the saddle of being able to write code that writes code. Yes, there's a lot to learn, but CodeSmith knows that - and part of their ability to stay alive as a business depends upon them being able to get you over that hurdle, so they've got plenty of tutorials and guidance to get you to the point where you're able to start using code generation more efficiently. In my estimation, this has been one of the biggest side benefits of my decision to switch to PLINQO above and beyond all of the tangible benefits I've picked up with PLINQO itself.

Some Rough Edges

In my estimation, CodeSmith has done a great job in delineating the core features and capabilities that developers want and need when working with LINQ or PLINQO. But there are some rough edges or areas for improvement.

For example, in my current project (which is primarily an ASP.NET MVC application - but which will eventually allow an iPhone application to interact with my data objects), I don't want to couple my data-access code into my actual presentation tiers. Instead, I want a Project or namespace for my Models. Nothing in PLINQO stops me from doing that. But, by the same token, any objects that I expose via PLINQO are all public.

At the very least, I'd like to make calls to my sprocs Internal, such that consumers of my data tier could never directly invoke sprocs, becaue I only want consumers to access objects via Repositories (where caching and other considerations are all handled). Likewise, I'd like the option to make my objects (or tables) internal as well. That would let me build out a data tier that used PLINQO for internal communication with the database but which exposed POCOs out to consumers in perfect fashion. I'm actually at that exact stage in my application at the moment, but my Data tier exposes all sorts of raw PLINQO objects and calls in addition to what my repositories offer.

Consequently, even though I think that some of the options available for PLINQO template generation are a bit overwhelming when you first get started with PLINQO, after you've been around the block a few times with PLINQO, you quickly realize that there just aren't enough options, knobs, and levers. Now, to their credit, CodeSmith has released the source code and all default templates used in generating PLINQO (or other) solutions. So, if you really need to customize something, you can. In fact, that's pretty-much the paradigm with CodeSmith; it ships with gobs of different kinds of templates and you either love them as they are, or go in and modify (or create) them until you do love them.

Only, with PLINQO, there's less of an actual reliance upon template files (as there is with other template solutions). Instead, there's a Visual Studio Project used to generate your .dbml and object files. And while the source code that ships with Code Smith Pro lets you go in and edit the actual project used for code generation, I'd much prefer it if the generator made MORE use of template files instead of putting pretty much everything together in code because, that way, it would be tons easier for me to, for example, change all of my sprocs or entities to internal instead of public. I'd just find that part of the template, and change it for the project in question, regenerate, and be done.

Saving Time and Effort

In summary, PLINQO is a great. My only complaint with it is that it's a bit harder to customize than many of the other CodeSmith template solutions out there. Hopefully, as PLINQO matures, a rewrite of PLINQO's generation routines will break some of the code in the DBML generator out into template files (for different object types) that are more readily disposed to being customized - as is the case with other CodeSmith solutions and frameworks. As it currently stands though, I'll just wade through the source code a bit, find where it's specifying the accessibility level and hack it to internal as needed. I've made a couple of minor changes in that way already and doing so has really helped ensure that I'm getting the exact code that I want. I just wish there was less wading through code and more template files to deal with when it comes to PLINQO.

But, when all is said and done, I paid out of pocket for CodeSmith Pro and got PLINQO. And the $199 that I paid was paltry in comparison to the benefits I've derived. PLINQO has been a huge win in that it has literally taken weeks of (ugly, painful, wire-up/plumbing) code off of my plate, which has been well worth the learning curve. Especially since the side benefit is that I now have an insanely powerful templating solution that I've started leveraging for other needs.