Fluent NHibernate - ORA-00904: : invalid identifier Since Object property name is an oracle key word

Question

I have used Fluent NHibernate to auto map for Oracle database. My Domain class have some Oracle key words as property names. Example: Group is a key word in Oracle. When we use Group as a column the it needs to be enclosed within quotes (like "Group"). How can we take care of this in Fluent NHibernate?

I have modified the IColumnInstances to add ColumnName method and added a Convention. After adding this code it is throwing another error "ORA-00907: missing right parenthesis". Is there any alternate solution?

Here is the code

public class ColumnNameConvention : IColumnConvention
{
    public void Apply(IColumnInstance instance)
    {
        try
        {
            if (instance.Name.Length > 30)
            {
                instance.ColumnName(instance.Name.Substring(instance.Name.Length - 25, 25));
            }
            else
            {
                instance.ColumnName("\"" + instance.Name + "\"");
            }
            instance.Length(200);
        }
        catch (System.Exception ex)
        {
            throw ex;
        }
    }
}

score 2 · Accepted Answer · edited May 23 '17 at 11:57

The correct magic sign to be used in your case is: ` (see below example for better understanding)

Check it here:

Example:

Map(x => x.Group).Column("`Group`");

Interesting reading for a global settings:

example:

FluentConfiguration configuration = ...

configuration
 .ExposeConfiguration(x => x.SetProperty("hbm2ddl.keywords", "auto-quote"));

return configuration.BuildSessionFactory();

or

SchemaMetadataUpdater.QuoteTableAndColumns(config)

Great to see that! Nhibernate is powerful tool...;) – Radim Köhler May 20 '13 at 14:15 — Radim Köhler, May 20 '13 at 14:15

score 0 · Answer 2 · answered May 20 '13 at 14:09

Here is the working code.

private static ISessionFactory CreateSessionFactory()
    {
        try
        {
            var cfg = OracleClientConfiguration.Oracle10.ConnectionString(c =>
            c.Is("Data Source=XXXXXXX;User ID=XXXXX;Password=XXXX;"));

            FluentConfiguration configuration = Fluently.Configure()
                    .Database(cfg)
                    .Mappings(m => m.AutoMappings.Add(CreateAutomappings))
                    .ExposeConfiguration(x => x.SetProperty("hbm2ddl.keywords", "auto-quote"))
                    .ExposeConfiguration(BuildSchema);

            return configuration.BuildSessionFactory();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.InnerException.Message);
            throw ex;
        }
    }

Remember.. You still need to limit the column length to 30 characters. I have modified the IColumnInstances to add ColumnName method and added a Convention. Here is the code for that

Add this code in ColumnInstance class of FluentNHibernate. I have also added the respective interface methods.

 public void ColumnName(string columnname)
    {
        mapping.Set(x => x.Name, Layer.Conventions, columnname);
    }

Add this implementation code in your code.

public class ColumnNameConvention : IColumnConvention, IColumnConventionAcceptance
{
    public void Accept(IAcceptanceCriteria<IColumnInspector> criteria)
    {
        criteria.Expect(x => x.Name.Length > 30);
    }

    public void Apply(IColumnInstance instance)
    {
        try
        {
            instance.ColumnName(instance.Name.Substring(instance.Name.Length - 25, 25));
        }
        catch (System.Exception ex)
        {
            throw ex;
        }
    }
}

Fluent NHibernate - ORA-00904: : invalid identifier Since Object property name is an oracle key word

2 Answers2