Wednesday, 5 December 2012

Simple Metadata Driven Linq To Sql Type Generator

As part of a larger body of work, I implement a “Dynamic” Linq to Sql Type generator that was based on metadata. A number of people have asked about the source code for this generator so I have decided to publish this portion of the code as a stand alone component.

DumpingCoreMemory.Data.Linq.Generation assembly

  • LinqToSqlTypeGenerator: This class generates the types based on metadata provided at runtime.
  • EntityDescriptor: Instances of this class define entities that the LinqToSqlTypeGenerator class will generate concrete runtime types for
  • EntityPropertyDescriptor: Instances of this class define properties of the concrete runtime types that the LinqToSqlTypeGenerator will generate

How To Use the Type Generator

Included in the attached source code zip file is a contrived example, which briefly demonstrates how the type generator can be used. The example assumes a database which contains a table called TableA which contains three columns Id, ColumnA and ColumnB. In the example code this table is to be mapped to a Type called EntityA with three properties Identity, PropertyA and PropertyB. The type EntityA is to be derived from an interface IEntity. This is to enable demonstration of one of the ways a generated Type can be manipulated. The code for the mapping is as follows:
private static List<EntityDescriptor> GenerateEntityDescriptors()
 var entityDescriptors = new List<EntityDescriptor>
  new EntityDescriptor
    BaseType = typeof (IEntity),
    EntityName = "EntityA",
    TableName = "TableA",
    EntityProperties = new List<EntityPropertyDescriptor>
        new EntityPropertyDescriptor
          ColumnName = "Id",
          Name = "Identity",
          Type = typeof (Int32),
          IsIdentity = true
        new EntityPropertyDescriptor
          ColumnName = "ColumnA",
          Name = "PropertyA",
          Type = typeof (String)
        new EntityPropertyDescriptor
          ColumnName = "ColumnB",
          Name = "PropertyB",
          Type = typeof (Int32)
 return entityDescriptors;

The generator is invoked which generates the types, in the example I pass in an assembly file name, this is not necessary but for the purpose of the example it is useful as we can have a look at the generated types.
var entityDescriptors = GenerateEntityDescriptors();
var generator = new LinqToSqlTypeGenerator();
IList<Type> generatedTypes;

#warning Ensure that the process that invokes this method has rights to write to the disk
// Passing the assemblyFileName parameter is not necessary. 
// The generator will write the assembly to the specified filename.
// This allows us to reuse the generated types or just take a look at them with ILSpy
generator.GenerateTypes("LinqToSqlGeneratedTypes", "LinqToSqlGeneratedTypesAssembly.dll",
      out generatedTypes);

Using ILSpy we can inspect the generated code that was written to the assembly LinqToSqlGeneratedTypesAssembly.dll.


As one would expect we see a simple object with mapping attributes to the underlying database table called TableA. The next step is to activate and use the type for this we have a number of options:
  1. Statically reference the type, this is not shown in the test application as it is trivial to reference the assembly and instantiate its types. It also kind of defeats the purpose of generating types dynamically
  2. Cast to a known base type, in the test application the base type of the generated Type was specified as IEntity. The instance of the type is cast to an IEntity type so that properties can be accessed, the EntityDescriptor for the type specified a matching property for the IEntity interfaces specified Identity property
  3. Use a dynamic type to contain the type. This allows bypass of static type checking to allow access to properties of a type at runtime
  4. Use reflection to get or set properties of the underlying instance, using the name of the property for access

The following code snippet shows instantiation of an instance of the generated type and access by options 2,3 and 4.
object instance = Activator.CreateInstance(generatedTypes[0]);

var instanceAsIEntity = (IEntity) instance;
dynamic o = instance;

Console.WriteLine("Accessed as interface: {0}", instanceAsIEntity.Identity);
Console.WriteLine("Accessed as dynamic: {0}", o.Identity);
Console.WriteLine("Accessed by reflection: {0}", GetPropertyValueByReflection(instance, "Identity"));

Persisting instances of the types to a database is straight forward. The method PersistEntity in the test application shows how to insert an instance to a database.
private static void PersistEntity(object instance)
 using (var sqlConnection = new SqlConnection("Server=localhost;Database=Test;Trusted_Connection=True;"))
  using (var context = new DataContext(sqlConnection))
   ITable table = context.GetTable(instance.GetType());



For simplicity the code sets the properties via the dynamic object reference. In a real world example the instance may be bound to a UI control prior to CRUD operations being performed.


  • There is no abstraction of the underlying types from the data store. So if the underlying column is of type nvarchar then the property of the generated entity must be a String. The generator does not support type coercion or conversion which can get messy. The WCF service that allows the definition of the types metadata ensures that this cannot occur
  • There is no abstraction from the underlying data stores foreign key relationships. So if a type is mapped to a table that requires a foreign key value to be specified which is not exposed via the generated type then new rows cannot be inserted
  • There is no support for nested relationships. i.e a Thing contains a list of Parts. For the use cases of the overall project this is not a key requirement and can be added with more elaborate metadata and a change to the type generator