Project Description
Supports POCO-like entities for LINQ-to-SQL, with bi-directional relationships and lazy loading. No visual designer or SQLMetal required.

Background

It is often desirable to use LINQ-to-SQL with Plain Old CLR Objects (POCO's). You may want to use POCO's for any of the following reasons:
  • You want to write your objects first (TDD style) before you create the database
  • You don't want to be dependent on Microsoft's code generators (the Visual Designer or SQLMetal)
  • You want entity classes that are concise enough to read ;-)
  • You want to put comments or custom attributes on the properties of your entity classes (neither of which is possible with generators, because there are no "partial properties")

Benefits

It has always been possible to use POCO's with LINQ-to-SQL, but you have generally had to give up lazy loading and bi-directional relationships. For example, if you manipulate either of customer.Orders or order.Customer, then the other was updated automatically if you used generated LINQ-to-SQL entities, but not if you used POCOs.

With Close2POCO, you don't have to give up anything. You get entities that are functionally identical to the generated ones, but they are concise, readable and hand-coded. You don't even need to set up an external mapping file - just write the entities in C# and Close2POCO will figure out the mapping for you.

Approach

Close2POCO is deliberately not a 100% POCO solution. Instead, it aims to be close enough (hence the name). In particular, it supports concise, hand-written entities. The constraints it imposes are designed to be as minimal as possible. For instance, while you must inherit from a particular interface, the interface has no members - it's just a marker. Likewise, while you must still use EntitySet and EntityRef, you can keep them private to your class and just expose POCO types in the public members.

Example

Here is a minimal pair of Close2Poco classes.

    public class Customer: IClose2PocoEntity
    {
        public Customer()
        {
            this.Create(ref _orders);
        }

        private EntitySet<Order> _orders;

        public string CustomerID { get; set; }

        public string CompanyName { get; set; }     

        public IList<Order> Orders                                             
        {
            get { return _orders;  }
            set { this.SetValue(ref _orders, value); } 
        }
    }

public class Order : IClose2PocoEntity
{
        private EntityRef<Customer> _customer;

        [IsDbGenerated]
        public int OrderID { get; set; }

        public DateTime OrderDate { get; set; }

        public string CustomerID { get; private set; }  

        public Customer Customer
        {
            get { return _customer.Entity;  }
            set { this.SetValue(ref _customer, value); }
        }
    }



We had to do several things:
  1. Derive both classes from IClose2PocoEntity (which is just an empty marker interface).
  2. Call this.SetValue when setting EntitySets and EntityRefs (SetValue is an extension method on IClose2PocoEntity)
  3. Call this.Create to create our EntitySets (another extension method)
  4. Mark the DB-generated ID value with [IsDbGenerated]

That's all. In particular, note what we did NOT have to do:
  1. Implement any particular getters or setters for the other properties(*)
  2. Use any designer or generator
  3. Specify any mapping information. There are no mapping attributes and there is no external mapping file. Following convention over configuration, Close2Poco maps each property to the database column of the same name. You only need to specify mapping information if you want to use a column with a different name (not shown above)
  4. Write any logic to support lazy loading (that is taken care of by EntitySet and EntityRef)
  5. Write any logic to support bi-directional relationships (that is taken care of by Close2Poco)

To re-iterate, these concise classes are functionally equivalent those generated by the LINQ-to-SQL designer(*), but they are order(s) of mangnitude more concise.

Taking it futher

Generating the database

If you want to work in the traditional database-first model, then you are done at this point. However, if you want to actually write the objects first, then generate the database from the objects, you can do so. The project source code contains a demo project which does exactly that. See the source for details.

Notes that if you want to generate the DB, you have to put attributes onto string fields, since without the attributes Close2Poco cannot guess the right lengths. For future releases, I'm thinking of letting you simply re-use any other custom attributes which you might be putting on there anyway (e.g. if you use a validation framework that has a string length attribute, then you can use that one instead of the one from Close2Poco).

Getting rid of the extension methods

I'm not a big fan of using extension methods in this way, but it does help to keep the demo simple. In production, I would move the calls to my layer supertype class, and write them there as ordinary static method calls. E.g. entity class calls base.SetValue, which in turn calls the Close2Poco SetValue, passing "this" as the first parameter.

Limitations

Due to time constraints, the initial release contains a number of limitations. All are relatively easy to address in future releases. The initial limitations are:
  • Table names are assumed to match class names (an attribute is planned to override this default)
  • Inheritance of mapped classes is not supported (i.e. you cannot inherit one mapped class from another - at least, not yet... :-)
  • Primary keys are assumed to be the entity name followed by "ID". An attribute is planned to override this default.
  • Composite keys are not supported yet
  • For foreign keys, the foriegn key field must have the same name as the entity field, but with ID on the end. E.g. if Order.Customer is backed by an EntityRef, then Order.CustomerID must exist and must be named CustomerID. A number of approaches are possible to solve this. I haven't quite decided which one to implement yet.

Thanks

Thanks to the people who expressed interest in this idea following my blog post some months ago. Thanks to my employer, Optimation Ltd, for their support.

(*) The examples on this page do not support INotifiyPropertyChanged. Supporting that interface, as the generated classes do, allows the LINQ-to-SQl change tracker to work more efficiently. Support for INotifyPropertyChanged can be easily added using ActiveSharp

Last edited Oct 10, 2008 at 9:47 AM by johnrusk, version 12