Is It Possible to Assign Default value to Id from Result of Scalar Function

Question

I am working on an .NET Web api app which uses NHibernate and Fluent NHibernate for mapping.

I have a scalar-function named "next_photo_id()" generates Int64 number like Sharding & IDs at Instagram

But I couldn't assign scalar function as default value to Id column in Mysql. I tried and I got error.

ALTER TABLE `photos`
    CHANGE COLUMN `id` `id` BIGINT(20) NOT NULL DEFAULT next_photo_id() FIRST ;

So I tried to assign Id from Application side using fluentNhibernate.

My Photo table's Classmap is

 public class PhotoMap : ClassMap<Photo>
{
    public PhotoMap()
    {
        Table("photos");


        Id(m => m.Id).Column("id").Access.Property().Default("next_photo_id()");

        Map(m => m.Caption).Column("caption");
    }
 }

Photo table Insert Query must be,

Insert Into photos (id,caption) values (next_photo_id(),"test")

But I couldn't do it. Is there anyway to do it ?

Answer 1

Defining default value in mapping is only for DDL. So essentially, you have just told NHibernate to try defining the default SQL constraint in case you ask it to generate the db schema for you. (It would be the same than the one you have tried to directly define in SQL.)

For your case, you need to define a custom insert query. I know how to do it with hbm.xml mappings, it is documented here, and it would be something like:

<class name="PhotoMap" table="photos">
    ...

    <sql-insert>insert into photos (caption, id)
      values (?,
        case when ? is null then next_photo_id() else next_photo_id() end)
    </sql-insert>
</class>

As documented, you should first determine in which order NHibernate send its parameters and stick to this order. Moreover, NHibernate will expect a positional parameter for the id, and would fail without it. So I work around it with the case. You may handle that more cleanly by using a stored proc accepting all parameters, and which body would simply ignore the id one.

For translating this to Fluent NHibernate, I do not know, I do not use it.

You will now still have a major hurdle to overcome: what generator should you use for your id field? I do not see any which could be suitable. You may lie to NHibernate and told it something like assigned, but then it will not retrieve for you the id generated on db side. You would have to clear the session and query for your newly added photos if you have some natural-id to find it...

Otherwise, you have to implement a custom id generator for supporting your case. It should be something near the identity one. But beware, this one, as documented, causes the sql-insert to be ignored. You may have the same issue when implementing your custom generator.

If your id was a simple property, not the entity id, you could simply tell NHibernate it is generated:

<property name="Id" generated="insert" />

But for the id, as stated above, this is way more complex as far as I know.

DB side

Your default should be doable in db. With SQL server, a correct way of defining the default would be something like:

alter table photos add constraint DefPhotosId default (dbo.next_photo_id()) for Id

You should open a mysql question if your error occurs while attempting to define the default.

But anyway, you will still have the id generator trouble on NHibernate side.