Search code examples
sqlf#type-providers

Computed columns and Microsoft.FSharp.Data.TypeProviders.SqlDataConnection

I have a table in a SQL database which contains a computed column, something like the following:

CREATE TABLE MyTable
(
Rate     decimal(10,5) NOT NULL,
Quantity int           NOT NULL,
Cost as Quantity * Rate
)

When I attempt to insert a row into this table using the Microsoft.FSharp.Data.TypeProviders.SqlDataConnection type provider, it fails with the error "The column "Cost" cannot be modified because it is either a computed column or is the result of a UNION operator."

The corresponding generated SQL is:

INSERT INTO [MyTable]([Rate], [Quantity], [Cost])
VALUES (@p0, @p1, @p2)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
-- @p0: Input Decimal (Size = -1; Prec = 10; Scale = 5) [99.77]
-- @p1: Input Int (Size = -1; Prec = 0; Scale = 0) [1000000]
-- @p2: Input Decimal (Size = -1; Prec = 21; Scale = 5) [Null]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.17929

Is there any way to prevent the type provider from trying to specify the computed field?

Solution

  • SqlDataConnection is using LINQ to SQL under the hood, so you can customize column handling via a DBML file. Instead of letting the type provider (somewhat incompletely) infer the entire DB schema, make a DBML file and connect to that. Here is information on mapping computed columns.