Can't insert into temporary table with Bulk Operations

[daaleonardo]

I'm trying to import data into a temporary table using the same connection.

I manually tried adding some values and it works, but when using BulkInsert() gives me an error:

RepoDb.Exceptions.MissingFieldsException: 'There are no database fields found for table '[dbo].[##TempTable]'. Make sure that the target table '[dbo].[##TempTable]' is present in the database and/or at least a single field is available.'

This is my code:

public static void ImportDocuments(List<ArtsoftDoc> docList)
{
        CreateTemporaryTable();

        var toMergeList = docList.Select(doc => new ArtsoftDocDTOTemp(doc)).AsList();

        var temp = con.ExecuteQuery("SELECT * FROM ##TempTable");

        con.ExecuteNonQuery("INSERT INTO ##TempTable (Doc_ID, DocType, DocNumber) VALUES (1, 1, 1)");

        temp = con.ExecuteQuery("SELECT * FROM ##TempTable");

        con.BulkInsert(toMergeList);
}

public static void CreateTemporaryTable()
{
    var createTableQuery = """
        CREATE TABLE ##TempTable (
            Doc_ID INT,
            DocType INT,
            DocNumber BIGINT,
            Partner_ID INT,
            PartnerName NVARCHAR(255),
            ReferenceDoc NVARCHAR(255),
            VendorNumber INT,
            DocDate INT,
            DueDate INT,
            VATAmount DECIMAL,
            NetAmount DECIMAL,
            DocTotal DECIMAL,
            Year INT,
            Balance DECIMAL,
            IsCanceled BIT,
            DocumentNumber NVARCHAR(255)
        );
        """;

    con.ExecuteNonQuery(createTableQuery);
}

[Map("[dbo].[##TempTable]")]
public class ArtsoftDocDTOTemp
{
    public int Doc_ID { get; set; }
    public int DocType { get; set; }
    public long DocNumber { get; set; }
    public int Partner_ID { get; set; }
    public string PartnerName { get; set; }
    public string ReferenceDoc { get; set; }
    public int VendorNumber { get; set; }
    public int DocDate { get; set; }
    public int DueDate { get; set; }
    public decimal VATAmount { get; set; }
    public decimal NetAmount { get; set; }
    public decimal DocTotal { get; set; }
    public int Year { get; set; }
    public decimal Balance { get; set; }
    public bool IsCanceled { get; set; }
    public string DocumentNumber { get; set; }  
}

[rhuijben]

The problem here is that repodb doesn't know how to fetch the database schema of temporary tables. Another problem is that RepoDB assumes a schema of a table does never change while the process is running. It is stored in a cache that is never cleared.

The first issue would be reasonably easy to fix by looking for the schema of tables that start with a '#' in the right place, but the second is not to easy to fix.