How to use Dapper with Dommel and FluentMap to Insert a record into a table that doesn't have an Key?

Question

I'm creating a service in my API that uses Dapper with Dommel and FluentMap. The database is Microsoft SQL Server. I have an endpoint that needs to insert a record into an table that hasn't a key. The table is described like in this entity below:

public class MyTable
{ 
    public int SomeProperty{ get; set; }
    public int AnotherProperty{ get; set; }
}

This table only have these two columns in database. Doesn't have a key.

I want to use Dapper with Dommel, if it is possible, to insert a record in this table.

I already have created the maps and registered them, everything is fine and works for others tables that have an Key. But this one doesn't.

Every time a call the Dommel InsertAsync method I ended up with this error message:

fail: Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware4 An unhandled exception has occurred while executing the request. System.InvalidOperationException: Could not find the key properties for type 'MyProject.Models.MyTable'.

A already know it is possible to use only Dapper and manual create the SQL query and execute it .

I'm asking if there is a solution with Dommel.

Thanks in advance.

Answer 1

You need to specify a key. ORMs require keys so they know which row to insert, delete or update. It's simply impossible for an ORM to work without keys. Dommel isn't a SQL generator, it works with Dapper so it needs keys.

The class does have keys anyway - that's a many-to-many table with a composite key. Dommel recognizes the Key attribute which can be used to specify composite keys.

In Dommel's unit test the ProductsCategories class is used to represent a many-to-many relation with a composite key:

public class ProductsCategories
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int ProductId { get; set; }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int CategoryId { get; set; }
}