Tag Archives: Entity Framework

Custom conventions in Entity Framework 6 helping Firebird

8 Replies
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

Custom encryption of field with Entity Framework

Leave a reply

Yesterday there was a good question on Twitter with #efhelp hashtag. The core is about using Entity Framework to store entities, that store the properties encrypted somehow. So it’s custom encryption on client side (not on server). Of course, I could take Entity Framework sources and modify some file, but I wanted to do it with official release. The assumption I’m working with is that the entity itself knows how to encrypt and decrypt data (another valid approach might be that the DbContext does that).

The problem is, that the entity itself doesn’t know when it’s providing data to EF’s code (and hence it should be encrypted) and when to “normal” code (unencrypted). But we can kind of get this info (except looking at stack trace, which might be very slow). I used similar approach as I did few years back with validation, before it was added directly to EF’s API.

Here’s the code:

class MyContext : DbContext
{
	public MyContext()
		: base(@"server=(localdb)\mssql;integrated security=true;database=test;")
	{ }

	public IDbSet<SuperSecured> SuperSecured { get; set; }

	public override int SaveChanges()
	{
		var secured = this.ChangeTracker.Entries()
			.Where(x => x.State == EntityState.Added || x.State == EntityState.Modified)
			.Where(x => x.Entity is ISecured)
			.Select(x => x.Entity as ISecured)
			.ToArray();
		foreach (var item in secured)
		{
			item.Unlock();
		}
		try
		{
			return base.SaveChanges();
		}
		finally
		{
			foreach (var item in secured)
			{
				item.Lock();
			}
		}
	}
}

interface ISecured
{
	void Lock();
	void Unlock();
}

class SuperSecured : ISecured
{
	const string EncryptedStringPrefix = "X";

	bool _locked;

	public SuperSecured()
	{
		_locked = true;
	}

	public int ID { get; set; }
	public string Name { get; set; }
	string _topSecret;
	public string TopSecret
	{
		get
		{
			return _locked
				? Decrypt(_topSecret)
				: _topSecret;
		}
		set
		{
			_topSecret = IsEncrypted(value)
				? value
				: Encrypt(value);
		}
	}

	public static string Encrypt(string s)
	{
		return EncryptedStringPrefix + new string(s.Reverse().ToArray());
	}

	public static string Decrypt(string s)
	{
		return new string(s.Remove(0, 1).Reverse().ToArray());
	}

	public static bool IsEncrypted(string s)
	{
		return s.StartsWith(EncryptedStringPrefix);
	}

	public void Lock()
	{
		_locked = true;
	}

	public void Unlock()
	{
		_locked = false;
	}
}

using (var ctx = new MyContext())
{
	if (ctx.Database.Exists())
		ctx.Database.Delete();
	ctx.Database.Create();
	ctx.SuperSecured.Add(new SuperSecured() { Name = "Testing", TopSecret = "This is not a palindrome ;) " });
	ctx.SaveChanges();
	ctx.SuperSecured.ToList().ForEach(x => Console.WriteLine("{0}|{1}", x.Name, x.TopSecret));
}

First a note. The “encryption” here isn’t smart even a little. It’s just to be there. Do not even think about using it. ;) So how it works?

Let’s start with SaveChanges method. This method looks, before doing any saving, for entities with ISecured interface (It might be good idea not to expose this interface to public, but it’s up to you. ;) ). Then every entity is switched to unlocked state. Unlocked means, that it will provide raw data, encrypted. Then the saving goes and finally the same entities (the collection is materialized before) are switched back to locked state. The locked and unlocked state is simply switching between providing clean decrypted data or raw encrypted data respectively. That’s for the saving.

What about reading. Here we don’t have a directly one place where this happens. So I used another trick that’s often used. Because what’s the problem here? We need to distinguish between user code storing some values (this data should be encrypted) and EF’s infrastructure storing values while materializing entities (this data is already encrypted). So the trick is to us some kind of flag or marker to recognize whether the data is encrypted or not (again my implementation is way too dumb, just proof of concept). If so, store it as is, it’ll decrypted in getter. Else encrypt.

Nothing magical, right? :) Feel free to comment and/or improve.

ADO.NET provider for Firebird 3.0.1 released

12 Replies

Few weeks ago the 3.0.0 version was released (actually it was during Firebird Conference 2012, during my talk ;) ) and it came with some new features and improvements (hence version 3.0.0). And as it goes, nothing is perfect and few items slipped through testing phase. So that’s th reason why today, I’m happy to announce version 3.0.1 to be released.

You can download it from NuGet or from Firebird SQL site as it gets updated.

This release contains fixes for two regressions introduced in 3.0.0. You can see both in tracker. It’s also my initial step to release these small bugfix releases more often (as discussed in list).

Go grab it, while it’s hot.

How to show SQL command created by Entity Framework? [2012 edition]

Leave a reply

Four years back (wow) I wrote a post about how to show SQL command created by Entity Framework. The information there still holds. But now it’s 2012, Entity Framework progressed. Now you’re probably using DbContext and IDbSet<T> APIs, it’s actually recommended.

There you don’t have the ObjectQuery. As the new API is simpler and more focused, also getting the command is simple. Simply call ToString() method and you’re done.

class Program
{
	static void Main(string[] args)
	{
		Database.SetInitializer<MyContext>(null);
		using (var ctx = new MyContext())
		{
			Console.WriteLine(ctx.FooBars.Where(x => x.Id == -1).ToString());
		}
	}
}

class MyContext : DbContext
{
	public IDbSet<FooBar> FooBars { get; set; }
}

class FooBar
{
	public int Id { get; set; }
	public string Baz { get; set; }
}

Union and Concat in LINQ to Entities

Leave a reply

Few years back I wrote about “UNION and UNION ALL in LINQ to SQL” and comment there today made me re-think it again. Everything that’s written there is correct. But it might be little bit confusing to see the query being generated from Entity Framework. Actually the query is generated by SqlClient and every 3rd party provider should do the same.

Basically, as written in above mentioned article, Concat generates query, that has the semantic of UNION ALL. Similarly the Union generates query with UNION semantic.

Let’s check that. I’ll use simple Entity Framework’s Code First base set up. And some queries.

class MyContext : DbContext
{
	public MyContext()
		: base(new SqlConnection(@"Initial Catalog=test;Data Source=(localdb)\mssql;Integrated Security=True;Pooling=false;"), true)
	{

	}

	public IDbSet<Test1> Test1 { get; set; }
	public IDbSet<Test2> Test2 { get; set; }
}

class Test1
{
	public int Id { get; set; }
	public int FooBar { get; set; }
}
class Test2
{
	public int Id { get; set; }
	public int FooBar { get; set; }
}

using (var ctx = new MyContext())
{
	Console.WriteLine(ctx.Test1.Select(x => x.Id).Union(ctx.Test2.Select(x => x.Id)).ToString());
	Console.WriteLine(ctx.Test1.Select(x => x.Id).Concat(ctx.Test2.Select(x => x.Id)).ToString());
}

Running this code, produces following queries.

SELECT
[Distinct1].[C1] AS [C1]
FROM ( SELECT DISTINCT
        [UnionAll1].[Id] AS [C1]
        FROM  (SELECT
                [Extent1].[Id] AS [Id]
                FROM [dbo].[Test1] AS [Extent1]
        UNION ALL
                SELECT
                [Extent2].[Id] AS [Id]
                FROM [dbo].[Test2] AS [Extent2]) AS [UnionAll1]
)  AS [Distinct1]

SELECT
[UnionAll1].[Id] AS [C1]
FROM  (SELECT
        [Extent1].[Id] AS [Id]
        FROM [dbo].[Test1] AS [Extent1]
UNION ALL
        SELECT
        [Extent2].[Id] AS [Id]
        FROM [dbo].[Test2] AS [Extent2]) AS [UnionAll1]

Both queries are using UNION ALL. What’s wrong? The key magic is in usage of DISTINCT keyword in first query. That basically turns UNION ALL into UNION. Because UNION is all rows from both sets without duplicates. And DISTINCT will remove these duplicates.

Yes, little bit hidden, but the semantic is kept.