如何在两个表之间使用包含INNER JOIN的SELECT存储过程的Dapper？

I am experimenting with Dapper for the first time. I have two tables: Films and Ratings.

我是第一次尝试Dapper。我有两张桌子：电影和评级。

CREATE TABLE [dbo].[Films]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [Title] VARCHAR(250) NOT NULL, 
    [Genre] VARCHAR(50) NOT NULL, 
    [RatingId] INT NOT NULL, 
    CONSTRAINT [FK_Films_To_Ratings] FOREIGN KEY (RatingId) REFERENCES Ratings(Id)
)

CREATE TABLE [dbo].[Ratings]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [Name] VARCHAR(50) NOT NULL 
)

I have written a stored procedure that will return all films in the Films table and joins with the Ratings table. Dapper works easily when I have the table structured using the same name between the FK and PK of the tables.

我编写了一个存储过程，它将返回Films表中的所有影片并与Ratings表连接。当我在表的FK和PK之间使用相同的名称构建表时，Dapper很容易工作。

CREATE PROCEDURE [dbo].[GetFilms]
AS
BEGIN
    SELECT
        F.Id,
        F.Title,
        F.Genre,
        F.RatingId,
        R.Name
    FROM
        dbo.Films as F
    INNER JOIN
        dbo.Ratings as R
    ON
        F.RatingId = R.Id;
END

When my query runs, the film object becomes instantiated correctly but the RatingId was set to 0 (since int defaults to 0). The rating property contains the name, but the Id was also set to 0.

当我的查询运行时，电影对象变得正确实例化，但RatingId设置为0（因为int默认为0）。 rating属性包含名称，但Id也设置为0。

return this._db.Query<Film, Rating, Film>(
            "dbo.GetFilms",
            (f, r) =>
            {
                f.Rating = r;
                return f;
            },
            splitOn: "RatingId",
            commandType: CommandType.StoredProcedure
            ).ToList();

How can I successfully run my Dapper query and get the exact results I need when the column names are not identical like in my example? Table structure looks cleaner to me when I have columns named Ratings.Id instead of Ratings.RatingId.

如何在我的示例中列名不相同时，如何成功运行Dapper查询并获得所需的确切结果？当我有名为Ratings.Id而不是Ratings.RatingId的列时，表结构看起来更干净。

1 个解决方案

#1

Dapper will try and map property for property at the point you start splitting. You want your query to look like the following, and you can ditch the splitOn since you are splitting on Id.:

Dapper将尝试在您开始拆分时映射属性的属性。您希望您的查询看起来如下所示，并且您可以抛弃splitOn，因为您要拆分ID：

    SELECT
            F.Id,
            F.Title,
            F.Genre,
--Rating object starts here
            R.Id,
            R.Name
        FROM
            dbo.Films as F
        INNER JOIN
            dbo.Ratings as R
        ON
            F.RatingId = R.Id;