如何正确地“单一化"带有 Dapper.Contrib 的表名? [英] How to properly "Singularize" table names with Dapper.Contrib?

问题描述

我有一个 .Net Core 3.1 控制台应用程序.

I have a .Net Core 3.1 Console application.

在SQL Server数据库中，我的表是单数的，和我的POCO类一样，方便匹配和维护.

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

对于插入、更新和删除操作，我想使用 Dapper.Contrib 库.但是当我在生成的 SQL 查询中运行插入函数 Dapper 时，将表名复数化.

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 表学生":

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

C# 代码

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);
            }
    }
}

在输出时出现异常:

无效的对象名称学生".

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. 使用数据注释.这个不适合我，因为有很多 POCO 对象是使用 Scaffold-DbContext 从数据库自动创建的.在开发过程中，我对数据库进行了更改，然后再次重新创建了 POCO.此操作会删除在生成的 POCO 类中所做的所有更改.

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.

SqlMapperExtensions 委托的使用:

SqlMapperExtensions.TableNameMapper = (type) =>{//在这里做一些事情来复数类型的名称返回类型.名称；};

我不知道如何正确使用这个委托，也不知道把它放在哪里.我尝试了一段时间，但我确定我没有正确使用它:

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);
    }

在这行代码 type.Name.Remove(type.Name.Length - 1, 1); 我试图实现一个函数，它截断了名称中的最后一个字母输入 Student，假设 Dapper 将最后一个字母s"添加到类的名称中，并且我在我的实现中将其删除.示例:Student =>学生 =>学生 =>学生.

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.

我应该如何使用 SqlMapperExtensions 或者可能有另一种方法可以在不更改 POCO 类的情况下单一化"表名?

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

推荐答案

你的实现有几个问题，但是，因为你本质上是返回 type.Name，它应该返回的名称您的类型(在本例中为 Student).

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).

首先让我们解决当前 TableNameMapper 实现的问题；

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

1. 您只需要设置一次委托.您可以在类似于 Startup 类的 ConfigureServices 的地方执行此操作.现在，您每次打开连接时都要进行设置.

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.

您的实现执行 type.Name.Remove(type.Name.Length - 1, 1); 但您没有将操作的结果分配给变量.即使你已经分配了结果，你也只是返回 type.Name.

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.

我运行了你上面的代码，它在 LINQPad 中对我来说工作正常.我建议在调试器的 return type.Name; 行上添加一个断点.如果您需要手动调试，请查看 Visual Studio调试文档.检查 type.Name 实际上是什么，然后从那里开始.

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);
        }
    }
}

这篇关于如何正确地“单一化"带有 Dapper.Contrib 的表名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！