Few days ago I wrote a post “Custom conventions in Entity Framework 6 helping Firebird“. Arthur Vickers from Entity Framework team had a good question whether it works also for columns and tables that are generated by Entity Framework (like join tables for M:N, FK columns (if not in model), etc.). And it actually does not. For this you have to dig a little bit deeper and use model-based convention. For this type of convention you have to write a class as there’s (currently, alpha 3) no way to do it using fluent API in a lightweight way (and I would not expect this to change, this isn’t common scenario).

Anyway for properties to work you have to implement IDbConvention<EdmProperty> and for table IDbConvention<EntityType> (not EntitySet, you need to be able to see whether somebody set the table name already or not which for me is easier from EntityType type). Don’t ask me how I found this. A lot of trial and error. And actually a lot of memories from around 2010 and Entity Framework v1 (link, link (anybody here ever explored MetadataProperties?)) .

class FirebirdFriendlyModelConvention : IDbConvention<EdmProperty>, IDbConvention<EntityType>
{
	public void Apply(EdmProperty dbDataModelItem, EdmModel model)
	{
		var preferredName = (string)dbDataModelItem.Annotations.First(x => x.Name == "PreferredName").Value;
		if (preferredName == dbDataModelItem.Name)
			dbDataModelItem.Name = FirebirdNamingConvention.CreateName(dbDataModelItem.Name);
	}

	EntityType GetRootType(EntityType entityType)
	{
		if (entityType.BaseType != null)
			return GetRootType((EntityType)entityType.BaseType);
		return entityType;
	}

	string GetTableName(ICollection<DataModelAnnotation> anotations)
	{
		var anotation = anotations.FirstOrDefault(x => x.Name == "TableName");
		if (anotation == null)
			return null;
		return (string)anotation.Value;
	}

	public void Apply(EntityType dbDataModelItem, EdmModel model)
	{
		var entitySet = model.Containers.First().EntitySets.SingleOrDefault(e => e.ElementType == GetRootType(dbDataModelItem));
		var tableName = GetTableName(dbDataModelItem.Annotations);
		if (tableName == null)
		{
			tableName = FirebirdNamingConvention.CreateName(dbDataModelItem.Name);
			entitySet.GetType().GetProperty("Table").SetValue(entitySet, tableName);
		}
	}
}

As you can see it’s not magic, if you know where to look at. Because the Table property is currently (alpha 3) not public I was forced to use reflection. There’s the related work item.

With this conventions, hopefully, all the tables and columns, except specified explicitly, will be renamed to “common” Firebird naming.

There’s a part 2 of this story.

The next version of Entity Framework, version 6, has a nice new feature Custom Code First Conventions. In short you can create your own conventions and using these together with the default ones (these were there before). Does you primary key column/property always ends up _PK? You can create convention for that and completely remove bunch of HasKey() lines. But that’s not what I’m going to talk about, if you want to know more follow the link above.

Firebird as other databases adhering to SQL standard in naming and quotations treats unquoted column/table/… as upper case. A lot of ORMs normally quote everything, just to be safe and because it’s easier than to hunt all the exceptions and places where it might collide with reserved keywords. I’m doing that too in provider for Entity Framework for Firebird. That means, that if you create some Code First model, the SQL statements will be quoted and using default naming convention – that’s, simply speaking, same as property/class/… name. Not good. You have to write a lot of explicit HasColumnName.

But with custom conventions today, you can save your typing. Let’s say, that my naming convention is like this. Property SomeValue goes to column SOME_VALUE. So it’s upper case, words separated by underscores. In fact that’s very close to what majority of Firebird users use.

public static string CreateName(string s)
{
	return s.Aggregate(string.Empty, (acc, c) => acc + (char.IsUpper(c) && !string.IsNullOrEmpty(acc) ? "_" + c : char.ToUpperInvariant(c).ToString()), _ => _);
}

Yes, it’s a little over-LINQ-ed, but I wanted to try to write it like this.

The convention itself needs to implement IConfigurationConvention interface with proper mix of two generic parameters. Let’s name our columns and tables.

class FirebirdNamingConvention :
	IConfigurationConvention<PropertyInfo, PrimitivePropertyConfiguration>,
	IConfigurationConvention<Type, EntityTypeConfiguration>
{
	public static string CreateName(string s)
	{
		return s.Aggregate(string.Empty, (acc, c) => acc + (char.IsUpper(c) && !string.IsNullOrEmpty(acc) ? "_" + c : char.ToUpperInvariant(c).ToString()), _ => _);
	}

	public void Apply(PropertyInfo memberInfo, Func<PrimitivePropertyConfiguration> configuration)
	{
		var conf = configuration();
		conf.ColumnName = CreateName(memberInfo.Name);
	}

	public void Apply(Type memberInfo, Func<EntityTypeConfiguration> configuration)
	{
		var conf = configuration();
		conf.ToTable(CreateName(memberInfo.Name), null);
	}
}

Simple, isn’t it? Now we need to just register this convention.

class FirebirdContext : DbContext
{
	public FirebirdContext()
		: base(new FbConnection(@"database=localhost:test;user=sysdba;password=masterkey"), true)
	{ }

	public IDbSet<TestEntity> TestEntities { get; set; }

	protected override void OnModelCreating(DbModelBuilder modelBuilder)
	{
		modelBuilder.Conventions.Add(new FirebirdNamingConvention());
		//modelBuilder.Properties().Configure(c => c.HasColumnName(FirebirdNamingConvention.CreateName(c.ClrPropertyInfo.Name)));
		//modelBuilder.Entities().Configure(c => c.ToTable(FirebirdNamingConvention.CreateName(c.ClrType.Name)));
	}
}

I have also included other way to write the conventions, directly in OnModelCreating using so-called lightweight conventions.

Simple code to test.

Database.SetInitializer<FirebirdContext>(null);
using (var ctx = new FirebirdContext())
{
	Console.WriteLine(ctx.TestEntities.Where(x => x.MyInteger == 0).ToString());
}

class TestEntity
{
	public int Id { get; set; }
	public string SomeBoringColumn { get; set; }
	public int MyInteger { get; set; }
	public DateTime DateTime { get; set; }
	public DateTime Timestamp { get; set; }
}

And the result.

SELECT
"B"."ID" AS "ID",
"B"."SOME_BORING_COLUMN" AS "SOME_BORING_COLUMN",
"B"."MY_INTEGER" AS "MY_INTEGER",
"B"."DATE_TIME" AS "DATE_TIME",
"B"."TIMESTAMP" AS "TIMESTAMP"
FROM "TEST_ENTITY" AS "B"
WHERE 0 = "B"."MY_INTEGER"

Few lines of code and could save you maybe hundreds of lines of code you’d have to write otherwise.

Note: This code uses custom build FirebirdClient (with current stable one it will not work), because Entity Framework 6 contains some breaking changes for provider writers. I’m working on it and the test builds will be available soon.

goes to column