Question: How to add computed persisted column with index?

[IdeaHunter]

I want to create a persisted computed column to be able to have index on it in MSSQL.
Im trying following piece of code for mapping but column isn't getting created in sql:

classMap.Property(prop.Name, pMap =>
{
    pMap.Formula("CAST(CASE WHEN [Column1] > [Column2] THEN 1 ELSE 0 END AS bit) PERSISTED");
    pMap.Generated(PropertyGeneration.Always);
    pMap.Update(false);
    pMap.Insert(false);
    pMap.Index($"IX_{type.Name}_{prop.Name}");
});

Can anyone please tell me what im doing wrong?

[gliljas]

A computed column is something you create in MSSQL, not NHibernate. You map ii as a regular column, and make it read only. In other words, remove the formula. And make sure it's worth the effort. A single index on a bit column can be a waste of time. Den ons 4 dec. 2019 kl 19:24 skrev IdeaHunter <[email protected]>:

…

I want to create a persisted computed column to be able to have index on it in MSSQL. Im trying following piece of code for mapping but column isn't getting created in sql: classMap.Property(prop.Name, pMap => { pMap.Formula("CAST(CASE WHEN [Column1] > [Column2] THEN 1 ELSE 0 END AS bit) PERSISTED"); pMap.Generated(PropertyGeneration.Always); pMap.Update(false); pMap.Insert(false); pMap.Index($"IX_{type.Name}_{prop.Name}"); }); Can anyone please tell me what im doing wrong? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#2280?email_source=notifications&email_token=AACG35VMTCCDKTU5W6PUPCLQW7YWNA5CNFSM4JVMZX2KYY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4H6CX7WQ>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AACG35XHYOCMDJTJ765S2JLQW7YWNANCNFSM4JVMZX2A> .

[IdeaHunter]

@gliljas the bit column was a easiest thing to test and show as example - in real product i have on hands there are about 8 formulas of different complexity (that mostly returns union or strings).

A computed column is something you create in MSSQL, not NHibernate

can you please elaborate what design choices of nhibernate prohibits having code that assigns formula to persisted computed column, and how situation differs from having index on column since sql index have really nothing to with mapping sql data to object?

[gliljas]

An NHibernate formula is an SQL expression that you can map to a property. When the property is queried, the SQL expression is used in its place. A computed column (persisted or not) is a MSSQL concept. You can use it just fine with NHibernate, but it should be mapped as a normal column and it won't work well with schema generation. /G Den tors 5 dec. 2019 kl 14:39 skrev IdeaHunter <[email protected]>:

…

@gliljas <https://github.com/gliljas> the bit column was a easiest thing to test and show as example - in real product i have on hands there are about 8 formulas of different complexity (that mostly returns union or strings). A computed column is something you create in MSSQL, not NHibernate can you please elaborate what design choices of nhibernate prohibits having code that assigns formula to persisted computed column, and how situation differs from having index on column since sql index have really nothing to with mapping sql data to object? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#2280?email_source=notifications&email_token=AACG35THREOUIXQMUBCVBYDQXEABBA5CNFSM4JVMZX2KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEGAXKHY#issuecomment-562132255>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AACG35RNOIGMUDLLWDCPZP3QXEABBANCNFSM4JVMZX2A> .

[IdeaHunter]

@gliljas oh, thanks

it won't work well with schema generation.

what makes you think so?

A computed column (persisted or not) is a MSSQL concept.

yeah, but i see other databases have it too
https://mariadb.com/kb/en/library/generated-columns/
https://www.postgresql.org/docs/current/ddl-generated-columns.html
https://www.oracle.com/technetwork/database/database-technologies/rdb/automatic-columns-132042.pdf

if i want to implement support for such column where should i start look(code)?