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...

[cajuncoding]

@pagner FYI, this should be fully resolved now. I needed to rework some low level SQL Scripting to accommodate this correctly but now all property vs column resolution should be automatically handled and Computed Columns as well as Persisted columns are now considered non-updatable and ignored when writing to the database.

However, as we discussed above I also added support for a new [SqlBulkIgnore] property attribute so devs can explicitly ignore to handle other problematic edge cases in the future ✅.

Everything -- along with some other planned enhancements -- are all now merged into v2.5.0 as part of the PR: #25

[pagner]

@cajuncoding Good Stuff! Thank You!