Saturday, December 29, 2007

Interested in POCO LINQ?

I've been investigating using LINQ to SQL with a POCO approach. (POCO = Plain Old CLR Objects.) My ideal solution would be something like this:
  1. Entity classes are simple enough to be hand-written (no need to generate them with SQL Metal or VS 2008)
  2. Mapping is easy (no need to manually map every property via attributes or external XML)
  3. Lazy-loading works
  4. Bi-directional relationships work (update one end, and the other is synchronized automatically)
The last two points are the most problematical. In LINQ to SQL lazy loading requires the use of EntitySet and EntityRef. However, it's not easy to use those classes in hand-written code. In fact, it is generally recommended to dispense with these classes if you want to "go POCO". But that deprives you of lazy loading. You also lose out on Linq’s support for bi-directional relationships, because it is also based on EntitySet and EntityRef.

I want a solution that supports all four goals outlined above. [Update: here it is]

Question: If I can find one, would you be interested in it?

If enough people say yes (either in comments below or via email), I’ll try to post something within the next few weeks.

I understand that knowledgable people have said this can't be done. I respectfully disagree ;-) Based on work so far, I’m satisfied that it can be done.

Update: The idea would be that you'd hand-write .NET classes, according to a few simple conventions. Do that and you'd automatically get working bidirectional relationships and lazy loading, plus automatic runtime generation of the correct mappings. (You'd never use SQL Metal or the Visual LINQ designer.)

You could also generate the database if you want. (I.e. the usual model is generate-code-from-database; this would allow (but not require) generate-database-from-code). If you use TDD, you could create and test the objects using TDD (running in-memory tests) and then generate the database.

Other simple little benefits would be: e.g. could have one file per class (which you can't with MS's generators); could put comments on the properties of those classes (which you can't with MS's generators, as far as I can tell), could put your own attributes on the entity/domain object classes, e.g. for attribute-based validation frameworks (again, you can't with MS's generators), entity classes compact enough to actually read them ;-)

Footnote: In this post I’m talking specifically about LINQ to SQL. I know that there are (or soon will be) several fine independent ORMs that use the LINQ language features to create their queries, but execute those queries with their own backend. A number of these products support POCOs but, for my current project, I need a solution for the full Microsoft "stack" - including LINQ to SQL.


Anonymous Anonymous said...

I'm interested in knowing the names of "several fine independent ORMs that use the LINQ language features to create their queries, but execute those queries with their own backend"


Sat Dec 29, 05:15:00 AM PST  
Blogger John Rusk said...

I wrote that comment without having a particular list in mind :-) I know Frans Bouma has announced that he's working on LINQ support in LLBLGen Pro, although I haven't looked at that product for several years, so I don't know any details. The nHibernate guys have played round with LINQ but last I checked I got the impression that their LINQ stuff was not production-level code. I can also think of at least one other product which I expect will support LINQ, although when I checked just now I couldn't find any mention of LINQ on the vendor's site. In any case, I'd expect most ORM vendors to be seriously considering LINQ front ends for their products. As for whether any are out yet, or merely coming "soon" (as I suggested above) I don't know, sorry.

Any ORM vendors reading this, feel free to reply!

Sat Dec 29, 12:15:00 PM PST  
Blogger Ben said...

I have used a POCO to DB "tool" before with moderate success.

DataObjects.NET -

It does not use LINQ.

In the end I did not end up using it in my project. (It was too easy to overwrite your database. And the out-of-box experience ended up being bad.)

It was in interesting experience though.

Tue Jan 08, 02:22:00 PM PST  
Anonymous praveen said...

how to create different entitity classes[.cs] instead of single dbml massive file. i want one .cs file for each entity.
is it possible with linq to sql.
please help me out.


Wed Jan 16, 05:34:00 AM PST  
Blogger John Rusk said...


The solution I propose is not publically available. In the meantime, you could try generating LINQ classes with CodeSmith or MyGeneration.

Wed Jan 16, 09:38:00 PM PST  
Blogger Nigel Sampson said...

I believe the DataContext currently has a CreateDatabase method on it (I'm at work without VS2008 to confirm this).

If you can find a good way to get lazy loading and bi-directional relationships working without there collections it would be great however it's not a sticking point for me.

Thu Feb 07, 06:43:00 AM PST  
Anonymous peter said...

One thing to note - the auto-generated classes are partial classes, so it's possible to extend them in your own code files. I find that it works pretty well to include a separate Class_Extras.cs for any entities that need special logic or non-db properties.

Thu Feb 07, 07:39:00 AM PST  
Anonymous David Nelson said...

I would definitely be interested in such a solution, and would even be interested in assisting in its development.

Tue Feb 12, 11:27:00 AM PST  
Blogger John Rusk said...

Thanks David. Hopefully we'll find a few more people who are also interested - not necessarily to assist with the developent (as you have so kindly offered to do, thanks!) but at least to provide a potential user base. (I don't want to invest a lot of time in something that virtually no-one wants to use... ) I am hoping to recieve a few more replies in this thread to help me better gauge the level of interested in the idea.

Tue Feb 12, 11:44:00 AM PST  
Blogger paul said...

I would be very interested in this also!

Sat Feb 23, 04:14:00 PM PST  
Blogger Benjamin Eidelman said...


just in reply to ". I wish that they would make it easy for you to write code that would allow you easily customize the code generator so that you could tweak how the code was generated." and without advertising intentions :)!-

I wanted to tell you that we uploaded a project to CodePlex that can be used as a replacement to the default code generator custom tool for dbmls (MSLinqToSQLGenerator), without loosing the ORDesigner UI

Our implementation in the first realease only generate disconnected "pure" POCOs, but it could serve as a jump-start to create your own code generator (of course it's GPL!)


Wed Feb 27, 11:29:00 AM PST  
Blogger John Rusk said...

Interesting. Thanks Benjamin.

Wed Feb 27, 09:54:00 PM PST  
Blogger John Rusk said...

Update: After a long delay (for reasons beyond my control I'm afraid) I've finally released the first version of this code, here

Tue Jul 15, 03:07:00 AM PDT  

Links to this post:

Create a Link

<< Home