how to map column if exists otherwise empty

Question

I'm trying to map a property to a Formula to select value if the column if exists otherwise default value

I tried the following

mapper.Map(x => x.GroupId).Formula("(select case when exists (select * from INFORMATION_SCHEMA.COLUMNS SYS_COLS_TBL WHERE SYS_COLS_TBL.TABLE_NAME ='Azure' AND SYS_COLS_TBL.COLUMN_NAME = 'GroupId') then this_.GroupId else '' end)");

I get an error SqlException: Invalid column name 'GroupId'.

score 1 · Answer 1 · answered Jun 24 '19 at 13:41

This SQL statement will return all tables, which do have name 'Azure'

select * from INFORMATION_SCHEMA.COLUMNS SYS_COLS_TBL 
 WHERE SYS_COLS_TBL.TABLE_NAME ='Azure' 
   AND SYS_COLS_TBL.COLUMN_NAME = 'GroupId'

but, there could be e.g. two such tables .. but in different schemas. And if that happens, we can be querying dbo.Azure .. while SomeOtherSchema.Azure is having column GroupId. This will fix it:

select * from INFORMATION_SCHEMA.COLUMNS SYS_COLS_TBL 
 WHERE SYS_COLS_TBL.TABLE_NAME ='Azure' 
   AND SYS_COLS_TBL.COLUMN_NAME = 'GroupId'
   AND SYS_COLS_TBL.TABLE_SCHEMA = 'dbo'

Also, to support more complex querying (JOIN associations) remove this_.:

instead of:

then this_.GroupId else '' end)

use:

then GroupId else '' end)

NHibernate will provide proper alias based on context

Actually you're not answering the question. The question is similar to this one https://stackoverflow.com/q/16952442/259946, but... — hazzik, Jun 25 '19 at 09:55

score 1 · Answer 2 · answered Jun 25 '19 at 10:32

The SQL statement requires that all references to the tables and columns do exist. Therefore you're getting Invalid column name 'GroupId' error.

The usual way to do something like that would be to use dynamic sql (sp_executesql @sql):

DECLARE @sql NVARCHAR(MAX) = '
SELECT '+
    (CASE WHEN EXISTS (SELECT *
                       FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_SCHEMA = 'dbo' AND --assuming the schema is dbo
                             TABLE_NAME = 'Azure' AND
                             COLUMN_NAME = 'GroupId'
                      )
          then 'GroupId'
          else 'NULL as GroupId'
     end) + '
FROM Azure';

exec sp_executesql @sql

But this would not work because you are already in the context of SQL statement and it is not possible to inject the dynamic sql there.

To solve the problem there are a few solutions:

Correct solution: create the missing columns in the database.
Painful solution: map the object to a stored procedures which will execute a dynamic SQL similar to the above.
Insanely painful solution: Map your possible missing columns as normal. Before building the factory - introspect your model and either remove mappings for the columns missing or change their mapping to formulas. This would be similar to Configuration.ValidateSchema method of NHibernate, but instead of throwing errors - you'd need to remove these columns from the mapping.

how to map column if exists otherwise empty

2 Answers2