Support for other RDBMS than SQL Server

[LeandroPT]

Hello All,

Sorry about "reopening" a closed issue #2....

As Brock has probably noticed ( by my constant nagging :) ), it is difficult to use current EF code first against other RDBMS than SQL Server, in my case Oracle.

I have been able to IDSRV + EF successfully against an Oracle RDBMS by the use of a interceptor.
The disadvantage of this approach, is that it requires to parse and modify every generated query before execution, and to disableDatabaseInitialization.
The advantage however is the possibility to do about anything, including type change, name, change, whatever, without changing code, by the use of app.config
<interceptors> <interceptor type="IDS.Core.ef6.ChangeSchemaNameCommandInterceptor, IDS.Core" /> </interceptors> <contexts> <context type="Thinktecture.IdentityServer.Core.EntityFramework.OperationalDbContext, Thinktecture.IdentityServer.Core.EntityFramework" disableDatabaseInitialization="true" /> <context type="Thinktecture.IdentityServer.Core.EntityFramework.ClientConfigurationDbContext, Thinktecture.IdentityServer.Core.EntityFramework" disableDatabaseInitialization="true" /> <context type="Thinktecture.IdentityServer.Core.EntityFramework.ScopeConfigurationDbContext, Thinktecture.IdentityServer.Core.EntityFramework" disableDatabaseInitialization="true" />

I have been looking for a better way to change the schema name from the dreaded "dbo." to the oracle user, uppercase table and column names (Oracle accepts camel case, but "hand made" queries implies the use of "" in every column, and table names, which is not the "normal" for oracle developers.

The better way, is by tweaking the OnModelCreating by injecting two conventions on the "OnModelCreating" on BaseDbContext. (pretty much this: http://msdn.microsoft.com/en-us/data/jj819164.aspx)

So, for it to work, and to allow it to adjust to other RDBMS, i would like to know if you would be willing to place some kind of plug-in or (dependency injection) on BaseDbContext;
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Add<UppercaseForeignKeyNameConvention>(); modelBuilder.Conventions.Add<UppercaseTableSchemaConvention>();
base.OnModelCreating(modelBuilder);
}

The part in bold, would be whatever the user wants inside a class outside the EF assembly from a predefined interface.

Public Interface IBaseDbContextOnModelCreating
{
public void OnModelCreating(DbModelBuilder modelBuilder);
}

What is your thought on this?

[brockallen]

This sounds like EF does a bad job of handling Oracle. Why is that? Sounds like a complaint for Microsoft.

[LeandroPT]

True,
Unfortunatelly, there is no way to add those except on the DbContext or interceptor.
And i am sure Microsoft will do nothing on this version, since Oracle is the one with the provider and says it«s microsoft, and microsoft says it is oracle...

Despite that, it is not just Oracle, other providers have same problem too.

That said, i was wondering if you would like to prepare it for other rdbms by allowing a plugin in there, that is the best place for it right now...
That was the main question, would you like to incorporate, or not.

[brockallen]

Maybe we can change the design so these inject the DbContext. That way you can derive and do your override for ModelCreating and then the core logic can just have yours injected. I'll look into it (next week).

[brockallen]

Ok, the DbContexts are now injected. Give this a try and see if it works for you.

[LeandroPT]

I will check.

[LeandroPT]

Ok, just finished testing.

I got from the dev branch, and running against Thinktecture.IdentityServer.v3 version="1.0.0-rc" i get an error on mapping:

Unmapped members were found. Review the types and members below.
Add a custom mapping expression, ignore, add a custom resolver, or modify the source/destination type
Client -> Client (Source member list)
Thinktecture.IdentityServer.Core.Models.Client -> Thinktecture.IdentityServer.Core.EntityFramework.Entities.Client (Source member list)
Unmapped properties:
AllowLocalLogin 

Fixed it on my version on the EntitiesMap and ModelsMap, since one is AllowLocalLogin and the other is EnableLocalLogin

I see that the EntityFrameworkServiceFactory has beed "disposed" :), however, there was a feature i liked, the possiblity to configure clients and scopes:

public void ConfigureClients(IEnumerable<Thinktecture.IdentityServer.Core.Models.Client> clients);
public void ConfigureScopes(IEnumerable<Thinktecture.IdentityServer.Core.Models.Scope> scopes);

Other than that it works like a charm with some notes.

OnModelCreating of the context, since it is now possible to set the schema:
if (!string.IsNullOrEmpty(this.Schema)) { modelBuilder.HasDefaultSchema(this.Schema); }

This will allow to set the __MigrationHistory table schema for each context, without it it will create on dbo, since we are setting the schema on the other entities.

I had also to implement the totality of the RegisterClientStore, RegisterScopeStore, RegisterOperationalServices (code below), perhaps an overload to specify just the specific dbContext for each service would be wonderful.

As for knowledge sharing this is what i did to make it work with Oracle (should work the same with MySql at least, ot any other with proper adjustments):

Conventions

UpperCaseColumnNameConvention

Since oracle has a UPPERCASE convention is is useful to set the column names in uppercase, otherwise all selects on columns will need the "ColuMnNamE" wraped in ", otherwise you wold get an error "Column does not exists".

public class UpperCaseColumnNameConvention : Convention
    {
        public UpperCaseColumnNameConvention()
            : base()
        {
            Properties().Configure(c => c.HasColumnName(GetColumnName(c)));
        }
        private string GetColumnName(ConventionPrimitivePropertyConfiguration type)
        {
            string result = type.ClrPropertyInfo.Name;
            return result.ToUpperInvariant();
        }

    }

UpperCaseForeignKeyNameConvention

This is for the uppercase the relationships columns, of by other words those columns that have foreignkeys.

public class UpperCaseForeignKeyNameConvention : IStoreModelConvention<AssociationType>
    {

        public void Apply(AssociationType association, DbModel model)
        {
            // Identify ForeignKey properties (including IAs)  
            if (association.IsForeignKey)
            {
                // rename FK columns  
                UpperCaseForeignKeyProperties(association.Constraint.ToProperties);
            }
        }

        private void UpperCaseForeignKeyProperties(ReadOnlyMetadataCollection<EdmProperty> properties)
        {
            for (int i = 0; i < properties.Count; ++i)
            {
                properties[i].Name = properties[i].Name.ToUpperInvariant();
            }
        }
    }

UpperCaseTableNameConvention

This is for the uppercase the actual table names.
It could be done on the UpperCaseColumnNameConvention, but this is a better place.

public class UpperCaseTableNameConvention : IStoreModelConvention<EntitySet>
    {
        public void Apply(EntitySet item, System.Data.Entity.Infrastructure.DbModel model)
        {
            item.Table = item.Table.ToUpperInvariant();
        }
    }

Wiring it Up

Customization

ClientConfigurationDbCtx

public class ClientConfigurationDbCtx : ClientConfigurationDbContext
    {
        public ClientConfigurationDbCtx(string connectionString, string schema)
            : base(connectionString, schema)
        {

        }

        protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema(this.Schema);
            modelBuilder.Conventions.Remove<System.Data.Entity.ModelConfiguration.Conventions.PluralizingTableNameConvention>();
            modelBuilder.Conventions.Add<UpperCaseTableNameConvention>();
            modelBuilder.Conventions.Add<UpperCaseForeignKeyNameConvention>();
            modelBuilder.Conventions.Add<UpperCaseColumnNameConvention>();
            base.OnModelCreating(modelBuilder);
        }
    }

OperationalDbCtx

public class OperationalDbCtx : OperationalDbContext
    {
        public OperationalDbCtx(string connectionString, string schema)
            : base(connectionString, schema)
        {

        }
        protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema(this.Schema);
            modelBuilder.Conventions.Remove<System.Data.Entity.ModelConfiguration.Conventions.PluralizingTableNameConvention>();
            modelBuilder.Conventions.Add<UpperCaseTableNameConvention>();
            modelBuilder.Conventions.Add<UpperCaseForeignKeyNameConvention>();
            modelBuilder.Conventions.Add<UpperCaseColumnNameConvention>();
            base.OnModelCreating(modelBuilder);
        }
    }

ScopeConfigurationDbCtx

public class ScopeConfigurationDbCtx : ScopeConfigurationDbContext
    {
        public ScopeConfigurationDbCtx(string connectionString, string schema)
            : base(connectionString,schema)
        {

        }
        protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema(this.Schema);
            modelBuilder.Conventions.Remove<System.Data.Entity.ModelConfiguration.Conventions.PluralizingTableNameConvention>();
            modelBuilder.Conventions.Add<UpperCaseTableNameConvention>();
            modelBuilder.Conventions.Add<UpperCaseForeignKeyNameConvention>();
            modelBuilder.Conventions.Add<UpperCaseColumnNameConvention>();
            base.OnModelCreating(modelBuilder);
        }
    }

Turning it on

IdentityServerServiceFactory Extensions

public static class FactoryExtensions
    {
        public static void ConfigureClients(this IdentityServerServiceFactory factory, IEnumerable<Thinktecture.IdentityServer.Core.Models.Client> clients)
        {
            var clientRepo = factory.Registrations.Where(f => f.DependencyType.Equals(typeof(ClientConfigurationDbContext))).FirstOrDefault();
            using (ClientConfigurationDbCtx db = clientRepo.Factory.Invoke(null) as ClientConfigurationDbCtx)
            {
                if (!db.Clients.Any())
                {
                    foreach (var c in clients)
                    {
                        var e = c.ToEntity();
                        db.Clients.Add(e);
                    }
                    db.SaveChanges();
                }
            }
        }
        public static void ConfigureScopes(this IdentityServerServiceFactory factory, IEnumerable<Thinktecture.IdentityServer.Core.Models.Scope> scopes)
        {
            var ScopeRepo = factory.Registrations.Where(f => f.DependencyType.Equals(typeof(ScopeConfigurationDbContext))).FirstOrDefault();
            using (ScopeConfigurationDbCtx db = ScopeRepo.Factory.Invoke(null) as ScopeConfigurationDbCtx)
            {
                if (!db.Scopes.Any())
                {
                    foreach (var s in scopes)
                    {
                        var e = s.ToEntity();
                        db.Scopes.Add(e);
                    }
                    db.SaveChanges();
                }
            }
        }
    }

IdentityServerServiceFactory Configuration

 public static IdentityServerServiceFactory Configure(string connString,string schemaName)
        {
            EntityFrameworkServiceOptions options = new EntityFrameworkServiceOptions
            {
                ConnectionString = connString,
                Schema = schemaName
            };
            var factory = new IdentityServerServiceFactory();
            //factory.RegisterClientStore(svcFactory);
            factory.Register(new Registration<ClientConfigurationDbContext>(resolver => new ClientConfigurationDbCtx(options.ConnectionString, options.Schema)));
            factory.ClientStore = new Registration<IClientStore, ClientStore>();
            //factory.RegisterScopeStore(options);
            factory.Register(new Registration<ScopeConfigurationDbContext>(resolver => new ScopeConfigurationDbCtx(options.ConnectionString, options.Schema)));
            factory.ScopeStore = new Registration<IScopeStore, ScopeStore>();
            //factory.RegisterOperationalServices(options);
            factory.Register(new Registration<OperationalDbContext>(resolver => new OperationalDbCtx(options.ConnectionString, options.Schema)));
            factory.AuthorizationCodeStore = new Registration<IAuthorizationCodeStore, AuthorizationCodeStore>();
            factory.TokenHandleStore = new Registration<ITokenHandleStore, TokenHandleStore>();
            factory.ConsentStore = new Registration<IConsentStore, ConsentStore>();
            factory.RefreshTokenStore = new Registration<IRefreshTokenStore, RefreshTokenStore>();

            factory.ConfigureClients(Clients.Get());
            factory.ConfigureScopes(Scopes.Get());

            var userService = new Thinktecture.IdentityServer.Core.Services.InMemory.InMemoryUserService(Users.Get());
            factory.UserService = new Registration<IUserService>(resolver => userService);

            return factory;
        }

Bonus Track

Logging Interceptor

For debuging purpose i placed an interceptor to let me see what was being generated against the RDBMS

    public class ConsoleLogCommandInterceptor : IDbCommandInterceptor
    {
        public void NonQueryExecuting(
            DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
            LogIfNonAsync(command, interceptionContext);
        }

        public void NonQueryExecuted(
            DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
            LogIfError(command, interceptionContext);
        }

        public void ReaderExecuting(
            DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            LogIfNonAsync(command, interceptionContext);
        }

        public void ReaderExecuted(
            DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            LogIfError(command, interceptionContext);
        }

        public void ScalarExecuting(
            DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
            LogIfNonAsync(command, interceptionContext);
        }

        public void ScalarExecuted(
            DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
            LogIfError(command, interceptionContext);
        }

        private void LogIfNonAsync<TResult>(
            DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
        {
            if (!interceptionContext.IsAsync)
            {
                Console.WriteLine("Non-async command used: {0}", command.CommandText);
            }
        }

        private void LogIfError<TResult>(
            DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
        {
            if (interceptionContext.Exception != null)
            {
                ConsoleColor prevC = Console.ForegroundColor;
                Console.ForegroundColor = ConsoleColor.DarkRed;
                Console.WriteLine("Command {0} failed with exception {1}",
                    command.CommandText, interceptionContext.Exception);
                Console.ForegroundColor = prevC; 
            }
        }
    }

OracleHistoryBugInterceptor

/// <summary>
    /// <summary>
    /// Fixing Oracle BUG 20229532 - EF6: CODE FIRST MIGRATION MAY DROP MIGRATION HISTORY TABLE for tracking this behavior.
    /// More details in https://community.oracle.com/thread/3639602
    /// </summary>
    public class OracleHistoryBugInterceptor : IDbCommandInterceptor
    {
        public void NonQueryExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
            if (command.CommandText.ToUpperInvariant().Contains("create table".ToUpperInvariant()) && command.CommandText.ToUpperInvariant().Contains("__MigrationHistory".ToUpperInvariant()))
            {
                //Just ignore the error
                interceptionContext.Exception = null;
            }
        }

        public void NonQueryExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {

        }

        public void ReaderExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
        {

        }

        public void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
        {

        }

        public void ScalarExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {

        }

        public void ScalarExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {

        }
    }

And on app.config

  <entityFramework>
    <defaultConnectionFactory type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess" />
    <providers>
      <provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    </providers>
    <interceptors>
      <interceptor type="{namespace}.ConsoleLogCommandInterceptor, {Assembly}" />
      <interceptor type="{namespace}.OracleHistoryBugInterceptor, {Assembly}" />
    </interceptors>
  </entityFramework>

Notes

This was tested with Oracle ODP.Net 12c Release 3
ODAC 12c Release 3 with support for Entity Framework 6 Code First and Code First Migrations, NuGet, .NET Framework 4.5.2, and ODP.NET, Managed Driver XML DB.

Resources

Microsoft

Oracle Data Provider for .NET

Logging and Intercepting Database Operations

Custom Code First Conventions (EF6 onwards)

Thank you.

[brockallen]

Good feedback.

Question -- are you saying that builder.HasDefaultSchema will make it so that I do not have to use the ToTable(EfConstants.TableNames.Client, Schema) calls?

[brockallen]

Hmm, I tried it and now I have conflicts -- it seems that the different DbContext classes don't want to share the migrations table if it's in a schema.

[brockallen]

Ok, seems that using HasDefaultSchema has a bug: https://entityframework.codeplex.com/workitem/1685

[LeandroPT]

I had already seen that, but was unable to reproduce.
The problem has the same problem as the oracle bug. With multiple contexts on same DB sometimes (can't figure out when) ef tells it to drop and recreate. [(https://community.oracle.com/thread/3639602)]

Regarding your question
Question -- are you saying that builder.HasDefaultSchema will make it so that I do not have to use the ToTable(EfConstants.TableNames.Client, Schema) calls? the answer is yes.

About the error, you can use OracleHistoryBugInterceptor, or whatever name you want.
This is an issue if you want to share contexts. In my case, the 3 IDSrv contexts plus the memebershipReboot (identity Manager).. All in one.

[brockallen]

With an empty DB, when I use HasDefaultSchema the 2nd context trying to create its tables in the DB throws. I don't think I'll be able to use it.

[ghost]

sorry for interrupting the conversation but since my question is the same question here I don't want to open another question, I am using oracle with entity framework 5.0 database-first approach and want to use Thinktecture v3, is it possible or not ? the thread here is talking about code-first and has a lot of info I didn't find the answer I want in previous comments.

[brockallen]

Well, IdentityServer should be used stand alone, so this means just because you use EF5 elsewhere, IdentityServer can use EF6. Having said that, with EF6, @LeandroPT has shown here in great detail how to get it working.

[ghost]

thanks brockallen but he used code-first , is it still possible with database-first ?

[brockallen]

Well, unless I'm missing something from your question, if you want to use the IdSvr.Ef library, the code's already all done and it uses code first. If you want DB first, then you'd not be using this library and you're then just coding it all from scratch.

[LeandroPT]

Sorry for the delay...

@brockallen I am assuming you are trying it against SQL Server.
Let me check again...

As for @mohammadhammod:
The use of codefirst as @brockallen said the default for the IDSvr.Ef... what you mean by it is still possible? It was never possible...
If you see the nuget package "Thinktecture IdentityServer v3 - Entity Framework" one of the dependency is EntityFramework (≥ 6.1.1)

What are you trying todo?

[ghost]

@LeandroPT what I am trying to do is to use ThinkTecture with a very large asp.net application, this applications uses EntityFramework 5.0.0 (database first ) with oracle. I am new to ThinkTecture v3 so it seems that I asked a very stupid question! sorry for that.
first of all how are you using oracle with EF 6.1.1 ? the oracle data provider only supports EF 5 , are you using a third party providers from devart or something?

[LeandroPT]

Hey @mohammadhammod,

Regarding the limitation on ef with oracle provider, the answer is Not Anymore, Oracle has just released ODP.net 12c release 3 which supports ef6, so no need for ef5, and therefore this project can be now used..
Check my previous post and implementation to make it work with oracle:

Notes
This was tested with Oracle ODP.Net 12c Release 3
ODAC 12c Release 3 with support for Entity Framework 6 Code First and Code First Migrations, NuGet, .NET Framework 4.5.2, and ODP.NET, Managed Driver XML DB.

It also has the link to the Oracle Site.

Regarding IdentityServer, yes you can use with any ef or custom implementation, however this discussion is for @brockallen implementation on ef6 a separate project with a suggestive name :) .

[duhowise]

@LeandroPT i have identityServer3 working perfectly for mssql. i have been trying to migrate it by inheriting and using my own db-contexts the OperationalDbContext that has no direct implementation in the code.
i have how ever implemented a sample of your OperationalDbCtx but thing is it doesnt get called anywhere in the code . My solution runs perfectly, identityManager also works except when i click on discovery document i get a dbo erro from the operationdbcontext.

[duhowise]

@LeandroPT @brockallen I have found the solution the above problem i indicated: solution is to specify the Schema name in the EntityFramework service options such as:
var serviceOptions = new EntityFrameworkServiceOptions {ConnectionString = connectionString,Schema="AuthServer".ToUpper()};
Everything else works !