0

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.

tfidelis
  • 443
  • 8
  • 16
  • What you posted looks like a many-to-many table, in which case the PK is both ID columns. There's almost never any good reason to create a table without a primary key – Panagiotis Kanavos Oct 18 '21 at 16:55
  • 1
    Besides Dommel's landing page describes how to map one-to-one and one-to-many relations, especially if the columns match the naming conventions, eg `ClientId` for `Client`, `ApplciationId` for Application. `IdApplicativo` require explicit configuration. Many-to-Many [are unsupported](https://github.com/henkmollema/Dommel/issues/34) – Panagiotis Kanavos Oct 18 '21 at 17:01
  • @PanagiotisKanavos Unfortunately the table is already used in production(kkkrying) and I can't change it's structure. I need to know if there is a way to insert a record into a table that hasn't a key. I did not create this model and the table, I'm only working on it. – tfidelis Oct 18 '21 at 17:11
  • I just posted a link to a Dommel issue that explicitly says they won't support many-to-many relations. Dapper isn't an full-featured ORM like EF Core. It only deals with mapping SQL results to objects. It doesn't handle relations. What are you trying to do anyway? Why is writing the `INSERT` query such an issue? – Panagiotis Kanavos Oct 18 '21 at 17:14
  • @PanagiotisKanavos The many to many relationship doesn't matter, I do not validate it. It could be whatever table without a Key. I want to insert a record into a table that hasn't a Key. – tfidelis Oct 18 '21 at 17:17
  • 1
    Why would there ever be a table without a key? That's a bug, plain and simple. Again and again there are **very** few cases where a table without a primary key would make sense. Those very rare cases deal with complex problems and require writing queries by hand and taking care of locking issues. This is definitely not such a case. Unless you want to return duplicate Client/Application pairs, your actual table *will* have a key. – Panagiotis Kanavos Oct 18 '21 at 17:28
  • @PanagiotisKanavos I couldn't agree more with you, Unfortunately can't do code refactoring on this and I need to work with this structure exactly the way it is. The `INSERT` query is not a issue, actually it is my current solution. Just forget about the many-to-many relationship and lets discuss about if it is possible to insert a record using Dommel into a table that has not a Key. I didn't find a solution in Dommel docs and I'm curious about it's possibility. Just that. – tfidelis Oct 18 '21 at 17:36
  • That's not `just that`. ORMs require keys to know which rows to insert, update or delete. Dommel isn't a generic SQL generator, it's an ORM component that generates the proper SQL queries to persist objects. So it needs keys. Besides, your table **does have keys**. – Panagiotis Kanavos Oct 18 '21 at 17:40
  • I edited the question to remove the many-to-many relationship out of discussion. – tfidelis Oct 18 '21 at 17:41

1 Answers1

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; }
}
Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • So, the answer to the question is: No, you need to use keys, there is no way to use Dommel. I will stick with my query solution mentioned in the question. – tfidelis Oct 18 '21 at 23:35