How to properly "Singularize" table names with Dapper.Contrib?

Question

I have a .Net Core 3.1 Console application.

In SQL Server database I have tables with singular names, the same as my POCO classes, which is convenient for matching and maintaining.

For Insert, Update and Delete operations I want to use Dapper.Contrib library. But when I run Insert function Dapper pluralize table names in generated SQL queries.

SQL table "Student":

CREATE TABLE Student
    StudentId int NOT NULL PRIMARY KEY,
    FirstName varchar(50),
    LastName varchar(50)

C# code

public class Student
{
    public int StudentId {get; set;}
    public string FirstName {get; set;}
    public string LastName {get; set;}
}


class Program
{
    static void Main(string[] args)
    {
        var students = new List<Student>()
            {
                new Student { StudentId = 1, FirstName = "John", LastName = "Doe"},
                new Student { StudentId = 2, FirstName = "Tony", LastName = "Montana"}
            }

        long result;
        using (var cn = new SqlConnection(connString))
            {
                        result = cn.Insert(students);
            }
    }
}

On output I get an exception:

Invalid object name 'Students'.

I surfed around to find a solution, but couldn't find a working example. In general there two kinds of recommendations:

1. Use data annotations. This one is not suitable for me, as there are a lot of POCO objects created automatically from a database using Scaffold-DbContext. During the development process, I make changes in database and then re-create POCOs again. This operation deletes all changes made in generated POCO classes.

2. Use of SqlMapperExtensions delegate:

   SqlMapperExtensions.TableNameMapper = (type) => { // do something here to pluralize the name of the type return type.Name; };

I don't know how to properly use this delegate, and where to place it. I experimented a while, but I am sure that I don't use it properly:

using (var cn = new SqlConnection(connString))
    {
            SqlMapperExtensions.TableNameMapper = (type) =>
            {
                type.Name.Remove(type.Name.Length - 1, 1);
                return type.Name;
            };
            result = cn.Insert(students);
    }

On this line of code type.Name.Remove(type.Name.Length - 1, 1); I tried to implement a function which cuts-off the last letter in the name of the type Student, assuming that Dapper adds the last letter "s" to the name of the class, and I remove it in my implementation. Example: Student => Students => Student(s) => Student.

Long story short - I couldn't find a solution of how to implement a function that will "singularize" my table names.

How should I use SqlMapperExtensions or maybe there is another approach which may "singularize" table names without making changes in POCO classes?

Answer 1

[Table("student")]
public class Student
{
    public int StudentId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

This is the way

Answer 2

Your implementation has a couple of problems but, because you're essentially returning type.Name, it should be returning the name of your type (in this case Student).

First let's address the problems with your current implementation of TableNameMapper;

1. You only need to set the delegate once. You can do this somewhere like the ConfigureServices of your Startup class. Right now you're setting it everytime you open your connection.

2. Your implementation executes type.Name.Remove(type.Name.Length - 1, 1); but you're not assigning the result of the operation to a variable. Even if you had assigned the result you're just returning type.Name anyway.

I ran the code you have above and it works correctly for me in LINQPad. I'd suggest adding a breakpoint in your debugger on the return type.Name; line of your delegate. If you need a hand debugging then take a look at the Visual Studio debugging documentation. Check what type.Name actually is and go from there.

Here's the code I ran verbatim:

public class Student
{
    public int StudentId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

class Program
{
    static void Main()
    {
        SqlMapperExtensions.TableNameMapper = (type) => type.Name;

        var students = new List<Student>
        {
            new Student { StudentId = 1, FirstName = "John", LastName = "Doe" },
            new Student { StudentId = 2, FirstName = "Tony", LastName = "Montana" }
        };

        using (var sqlConnection = new SqlConnection(connectionString))
        {
            sqlConnection.Insert(students);
        }
    }
}