Computed Columns

[pagner]

Hi there,

I recently added a computed column to my table and it broke bulk inserts.

Exception: The column PartNumberNormalized cannot be modified because it's either a computed column... (which it is)

My entitiy does not contain this property, so I am guessing it's coming from BulkInsert grabbing the table schema.

Is there any way to ignore computed columns when inserting?

Thanks

[cajuncoding]

Can you send a sample table schema (creat table script with mock fields is fine)? So that I can be sure to simulate exactly the kind of computed field you are using.

Yes we can add support to ignore those fields — probably universally but worst case as a configuration option…

[pagner]

Hi there, thanks for the quick reply as always. The computed field in my case is PERSISTED.

What might be easier for you is to add an Ignore property to your Attribute. i.e.

[SqlBulkColumn(Ignore = true)]
public string PartNumberNormalized { get; set; }

This way we can ignore any column for any reason. When your SchemaLoader runs you can parse those out.

Best

[cajuncoding]

Yes I’m considering that, but I don’t want to have to force the inclusion of properties on models just to ignore them—polluting a clean insert model unnecessarily either.

Please provide a create table script (schema) per my request above 👆

[pagner]

Yeah, I hear what you are saying. I suggested the attribute route because that's what JSON serialization libraries do. i.e. System.Text.Json and Newtonsoft libraries use JsonIgnore. Same with ORM's such as EF. It just feels natural.

I don't think you would be polluting the model, as the property is truly part of the model. The model represents the table. Otherwise you are passing in an array of strings through the config, which to me, feels clunky. Just my two cents and I have no idea what you have to deal with to make it work. Your library is awesome!

Thanks

CREATE TABLE [dbo].[{TableName}] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (MAX) NULL,

-- raw identifiers (storage only)
[PartNumber]            NVARCHAR (MAX) NULL,
[SupplierPartNumber]    NVARCHAR (MAX) NULL,
[SupersededPartNumber]  NVARCHAR (MAX) NULL,
[SupersededSupplierPartNumber] NVARCHAR (MAX) NULL,

-- normalized identifiers (computed + persisted)
[PartNumberNormalized] AS
    UPPER(
        REPLACE(
            REPLACE(
                REPLACE([PartNumber], '-', ''),
            ' ', ''),
        '/', '')
    ) PERSISTED,

[SupplierPartNumberNormalized] AS
    UPPER(
        REPLACE(
            REPLACE(
                REPLACE([SupplierPartNumber], '-', ''),
            ' ', ''),
        '/', '')
    ) PERSISTED,

[SupplierName]          NVARCHAR (MAX) NULL,
[PackageQty]            INT NULL,
[UPC]                   NVARCHAR (MAX) NULL,
[SKU]                   NVARCHAR (MAX) NULL,
[SupersededSKU]         NVARCHAR (MAX) NULL,
[ProductCode]           NVARCHAR (MAX) NULL,
[CommodityCode]         NVARCHAR (MAX) NULL,
[Status]                NVARCHAR (MAX) NULL,
[InStock]               INT NULL,
[TotalAvailable]        INT NULL,
[CostPrice]             DECIMAL (18, 2) NULL,
[ListPrice]             DECIMAL (18, 2) NULL,
[SellPrice]             DECIMAL (18, 2) NULL,
[UnitOfMeasure]         NVARCHAR (MAX) NULL,
[Length]                DECIMAL (18, 2) NULL,
[Width]                 DECIMAL (18, 2) NULL,
[Height]                DECIMAL (18, 2) NULL,
[Weight]                DECIMAL (18, 2) NULL,
[Description]           VARCHAR (MAX) NULL,
[CountryOfOrigin]       NVARCHAR (MAX) NULL,
[Notes]                 NVARCHAR (MAX) NULL,
[CreationTime]          DATETIME2 (7) NOT NULL DEFAULT SYSUTCDATETIME(),

CONSTRAINT [PK_{TableName}] 
    PRIMARY KEY CLUSTERED ([Id] ASC)

);

[cajuncoding]

Ok, I'll think on it a bit over the holidays and try to patch it soon, hopefully first week of January if I can get some time to focus on this...