Computed Columns in SQL Server with Subsonic

I was not able to insert data into table with computed column using Subsoni开发者_Python百科c. Is it well-known bug? And how I can to resolve it?

This is the code SubSonic runs against the db to determine if it's a computed column:

const string COLUMN_SQL=@"SELECT 
        TABLE_CATALOG AS [Database],
        TABLE_SCHEMA AS Owner, 
        TABLE_NAME AS TableName, 
        COLUMN_NAME AS ColumnName, 
        ORDINAL_POSITION AS OrdinalPosition, 
        COLUMN_DEFAULT AS DefaultSetting, 
        IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType, 
        CHARACTER_MAXIMUM_LENGTH AS MaxLength, 
        DATETIME_PRECISION AS DatePrecision,
        COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
        COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME=@tableName
    ORDER BY OrdinalPosition ASC";

This statement should be of interest:

COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'),
        COLUMN_NAME, 'IsComputed') as IsComputed

First you should run that against your db to determine if the result is true.

The second thing I noticed is that, even if this value is queried from the db, it is not set in the code:

List<Column> LoadColumns(Table tbl){
    var result=new List<Column>();
    var cmd=GetCommand(COLUMN_SQL);
    cmd.Parameters.AddWithValue("@tableName",tbl.Name);

    using(IDataReader rdr=cmd.ExecuteReader(CommandBehavior.CloseConnection)){
        while(rdr.Read()){
            Column col=new Column();
            col.Name=rdr["ColumnName"].ToString();
            col.CleanName=CleanUp(col.Name);
            col.DataType=rdr["DataType"].ToString();
            col.SysType=GetSysType(col.DataType);
            col.DbType=GetDbType(col.DataType);
            col.AutoIncrement=rdr["IsIdentity"].ToString()=="1";
            col.IsNullable=rdr["IsNullable"].ToString()=="YES";
            int.TryParse(rdr["MaxLength"].ToString(),out col.MaxLength);

            result.Add(col);
        }

    }

    return result;
}

The code is from https://github.com/subsonic/SubSonic-3.0-Templates/blob/master/ActiveRecord/SQLServer.ttinclude

You should modify your local copy of the SQLServer.ttinclude and add a line (before the result.Add(col) method) that looks like this:

col.IsComputed=rdr["IsComputed"].ToString()=="1";

(depending on the result from the query it could be "YES" instead of "1"). The Column object has a IsComputed property,

https://github.com/subsonic/SubSonic-3.0/blob/master/SubSonic.Core/Schema/IColumn.cs

but again, I don't know if it is respected during update/insert.
If it isn't, try to set col.IsReadOnly to true.

One last thing. If the SQLServer.ttinclude modification fixes your issue, you should add a pull request to the subsonic github page.

Edit: Before messing around with the SQLServer.ttinclude you can add the

col.IsComputed = true;

line directly to you your Structs.cs file (but it will be overridden the next time you execute the template).

I have fixed this maybe .... In SQLSErver.ttinclude round about line 171 I changed

     col.AutoIncrement=rdr["IsIdentity"].ToString()=="1";

to col.AutoIncrement=rdr["IsIdentity"].ToString()=="1" || rdr["IsComputed"].ToString()=="1";

Now I hate this as a solution but the Column class does not have a property for IsComputer (or IsReadOnly) so this at least stopped the inserts from failing

Hope that helps - will post again if I find a better solution

Mike

I still get this error when Inserting (Adding) a row into a SQL table with a computed column:

System.Data.SqlClient.SqlException: The column "{0}" cannot be modified because it is either a computed column or is the result of a UNION operator.

Has anyone made any progress on this or is this still a bug? Using Subsonic.Core Version 3.0.0.3.

I followed SchlaWiener's very good advice, but alas, as he/she may have suspected, even adding IsComputed = true, or even IsReadOnly=true manually to the Structs.cs file seems to still emit the computed column in the INSERT statement and thus the bug still exists.

I did not download and check the Subversion source code to see if IsComputed or IsReadOnly columns are ignored, but based on experimentation, they seem to be included in the INSERT statement and thus are the source of an annoying defect.

Does anyone know of any workarounds?

Thanks.

PS for what it's worth I submitted an issue 275 in github... If I was more confident I'd make the fix... I might try that next. BTW FWIW: commenting out the Property in the ActiveRecord.cs makes issue go away (temporarily, until you re-run your T-4 templates) AND as long as you read that property via a stored proc. https://github.com/subsonic/SubSonic-3.0/issues/275