Insert and Update Builders break when column name is the same as another table.

[nicholas-peterson]

I've noticed an issue when a table has a column name with the same name as a table.
This happens if the column name overlaps any table, including the table it's on. For instance, the following schema:

CREATE TABLE [dbo].[person] (
    record_id BIGINT NOT NULL PRIMARY KEY,
    person NVARCHAR(100) NOT NULL
);

This results in HydraQuery failing to build when using update/insertTask:

open Microsoft.Data.SqlClient
open SqlHydra.Query
open DbDataReaders
let openContext () =
    let compiler = SqlKata.Compilers.SqlServerCompiler()
    let conn = new SqlConnection("data source=(LocalDB)\\MSSQLLocalDB;Database=Test;trusted_connection=true")
    conn.Open()
    let ctx = new QueryContext(conn, compiler)
    ctx

let testInsert () = 
    insertTask (Create openContext) {
        for p in dbo.person do
        entity {
            dbo.person.record_id = 0
            dbo.person.person = ""
        }
    }

dotnet build output:

MSBuild version 17.8.3+195e7f5a3 for .NET
  Determining projects to restore...
  All projects are up-to-date for restore.
C:\projects\sqlhydra-test\Program.fs(15,24): error FS1130: Invalid field label [C:\projects\sqlhydra-test\sqlhydra-test.fsproj]
C:\projects\sqlhydra-test\Program.fs(16,24): error FS1130: Invalid field label [C:\projects\sqlhydra-test\sqlhydra-test.fsproj]
C:\projects\sqlhydra-test\Program.fs(15,36): error FS0001: This expression was expected to have type↔    'string'    ↔but here has type↔    'int' [C:\proj
ects\sqlhydra-test\sqlhydra-test.fsproj]
C:\projects\sqlhydra-test\Program.fs(14,16): error FS0764: No assignment given for field 'record_id' of type 'DbDataReaders.dbo.person' [C:\projects\sqlhy
dra-test\sqlhydra-test.fsproj]

Build FAILED.

C:\projects\sqlhydra-test\Program.fs(15,24): error FS1130: Invalid field label [C:\projects\sqlhydra-test\sqlhydra-test.fsproj]
C:\projects\sqlhydra-test\Program.fs(16,24): error FS1130: Invalid field label [C:\projects\sqlhydra-test\sqlhydra-test.fsproj]
C:\projects\sqlhydra-test\Program.fs(15,36): error FS0001: This expression was expected to have type↔    'string'    ↔but here has type↔    'int' [C:\proj 
ects\sqlhydra-test\sqlhydra-test.fsproj]
C:\projects\sqlhydra-test\Program.fs(14,16): error FS0764: No assignment given for field 'record_id' of type 'DbDataReaders.dbo.person' [C:\projects\sqlhy 
dra-test\sqlhydra-test.fsproj]
    0 Warning(s)
    4 Error(s)

Changing the definition for the table in the DML to the following fixes the issue:

CREATE TABLE [dbo].[person] (
    record_id BIGINT NOT NULL PRIMARY KEY,
    the_person BIGINT NOT NULL
);
GO

While this is straightforward to design around (just don't name columns the same as a table), it leads to a somewhat mysterious error (the message makes it sound like you've formatted the fsharp record wrong). Plus, in sql design, foreign keys to other tables often just use the table name for the column, so I think people will hit this somewhat often.

[JordanMarr]

The most simple fix I can think of would be to set table_declarations to false in the toml config.

Then you could manually create them:

let personTable = table<dbo.person>

Or just use table<dbo.person> inline in your queries.

[nicholas-peterson]

Nice, thanks for the answer!