mercredi 20 janvier 2016

EF: Use reflection to build model for platform agnostic database

My Code First EF application currently supports SQL Server and Oracle. I have two different SQL scripts for each platform that basically go like this.

CREATE TABLE TABLE1(...etc.)
CREATE TABLE TABLE2(...etc)

The tables and attributes are all uppercase and so are the properties in my POCO class.

Now, for the next version I want to pick up support for PostgreSQL. I was able to get my same code to work by modifying my DDL to force the tables/attributes to be upper case.

e.g. create table "TABLE1"(...etc.)

Of course, I don't want to make upper case objects in Postgres. So I know I can write a bunch of code in my OnModelCreating() that's like

if(dbType == DbType.POSTGRES)
    modelBuilder.Entity<TABLE1>().ToTable("table1")

and I could also write code for each of the properties like

modelBuilder.Entity<TABLE1>().Property(p => p.COLUMN1).HasColumnName("column1")
modelBuilder.Entity<TABLE1>().Property(p => p.COLUMN2).HasColumnName("column2")
//etc

I think an even more slick way would be to use reflection that:

  1. For each table: set its table name ToLower()
  2. For each attribute in each table, set its name ToLower()

Going to be working on this today, and will post an answer if I find something workable.

Note: It's probably worth mentioning that the reason uppercase objects in my POCO classes don't work with Postgres because the way Entity Framework does Linq to SQL is that it generates SQL with quotation marks around the tables and attributes. So this SQL generated by EF:

SELECT "COLUMN1", "COLUMN2" FROM "TABLE1"

forces the postgres database to look for upper case objects.





Aucun commentaire:

Enregistrer un commentaire