在使用多映射api时，如果您有Id"， "splitOn以外的键，请确保设置splitOn参数

本文关键字：quot splitOn 参数 设置 确保 Id 映射 api 如果 | 更新日期: 2023-09-27 18:19:03

我试图使用dapper的多映射功能来返回专辑和相关艺术家和流派的列表。

public class Artist
{
public virtual int ArtistId { get; set; }
public virtual string Name { get; set; }
}    

public class Genre
{
public virtual int GenreId { get; set; }
public virtual string Name { get; set; }
public virtual string Description { get; set; }
}

public class Album
{
public virtual int AlbumId { get; set; }
public virtual int GenreId { get; set; }
public virtual int ArtistId { get; set; }
public virtual string Title { get; set; }
public virtual decimal Price { get; set; }
public virtual string AlbumArtUrl { get; set; }
public virtual Genre Genre { get; set; }
public virtual Artist Artist { get; set; }
}

var query = @"SELECT AL.Title, AL.Price, AL.AlbumArtUrl, GE.Name, GE.[Description], AR.Name FROM Album AL INNER JOIN Genre GE ON AL.GenreId = GE.GenreId INNER JOIN Artist AR ON AL.ArtistId = AL.ArtistId";
var res = connection.Query<Album, Genre, Artist, Album>(query, (album, genre, artist) => { album.Genre = genre; album.Artist = artist; return album; }, commandType: CommandType.Text, splitOn: "ArtistId, GenreId");

我已经检查了关于这个的解决方案，它不起作用。谁能告诉我哪里做错了吗?

谢谢@Alex:)但是我还是很感动。这是我所做的: 
CREATE TABLE Artist
(
ArtistId INT PRIMARY KEY IDENTITY(1,1)
,Name VARCHAR(50)
)
CREATE TABLE Genre
(
    GenreId INT PRIMARY KEY IDENTITY(1,1)
    ,Name VARCHAR(20)
    ,[Description] VARCHAR(1000)
)
CREATE TABLE Album
(
    AlbumId INT PRIMARY KEY IDENTITY(1,1)
    ,GenreId INT FOREIGN KEY REFERENCES Genre(GenreId)
    ,ArtistId INT FOREIGN KEY REFERENCES Artist(ArtistId)
    ,Title VARCHAR(100)
    ,Price FLOAT
    ,AlbumArtUrl VARCHAR(300) 
)
INSERT INTO Artist(Name) VALUES ('Jayant')
INSERT INTO Genre(Name,[Description]) VALUES ('Rock','Originally created during school days. The year was.....I guess 1998')
DECLARE @gen_id INT
        ,@art_id INT
SET @gen_id = (SELECT MAX(GenreId) FROM Genre)
SET @art_id = (SELECT MAX(ArtistId) FROM Artist)
INSERT INTO Album(GenreId,ArtistId,Title,Price,AlbumArtUrl) VALUES (@gen_id,@art_id,'I go mad for you',200,'http://asha4u.com/IGoMad')

根据您的建议，我将查询更改为:

var query = @"SELECT AL.AlbumId, AL.Title, AL.Price, AL.AlbumArtUrl, GE.GenreId, GE.Name, GE.Description, AR.ArtistId, AR.Name FROM Album AL INNER JOIN Artist AR ON AR.ArtistId = AL.ArtistId INNER JOIN Genre GE ON GE.GenreId = AL.GenreId";
var res = connection.Query<Album, Genre, Artist, Album>(query, (album, genre, artist) => { album.Genre = genre; album.Artist = artist; return album; }, commandType: CommandType.Text, splitOn: "GenreId, ArtistId");

现在我使用splitOn为GenreId和ArtistId。我还是会得到相同的错误。请帮助。

在使用多映射api时，如果您有Id"， "splitOn以外的键，请确保设置splitOn参数

您需要在选择查询中包含您想要分割的列。您的只是选择所有其他属性-因此Dapper不会找到匹配的列来拆分对象。

你的查询应该是这样的:

var query = @"SELECT AlbumId, Title, Price, AlbumArtUrl, GenreId, Name, Description , ArtistId, Name ......" etc

Sam为多重映射和splitOn选项写了一个很好的答案:https://stackoverflow.com/a/7478958/1028323

编辑:如果您的查询如上所述，您将不得不在GenreId和ArtistId上分裂。

 AlbumId, Title, Price, AlbumArtUrl | GenreId, Name, Description | ArtistId, Name

管道用于您正在尝试映射的新POCO的开始。因此SplitOn参数为GenreId和ArtistId。

编辑2:问题是你的POCO Album。您指定ArtistId和GenreId作为属性，但它们基本上属于各自的POCO's。

public class Album
    {
        public virtual int AlbumId { get; set; }
        public virtual string Title { get; set; }
        public virtual decimal Price { get; set; }
        public virtual string AlbumArtUrl { get; set; }
        public virtual Genre Genre { get; set; }
        public virtual Artist Artist { get; set; }
    }

和

var sql = @"SELECT AL.AlbumId
                 , AL.Title
                 , AL.Price
                 , AL.AlbumArtUrl
                 , GE.GenreId
                 , GE.Name
                 , GE.Description
                 , AR.ArtistId
                 , AR.Name 
            FROM Album AL 
      INNER JOIN Artist AR ON AR.ArtistId = AL.ArtistId 
      INNER JOIN Genre GE ON GE.GenreId = AL.GenreId";
using (var conn = connFactory.OpenConnection())
{
    var res = conn.Query<Album, Genre, Artist, Album>(sql, (album, genre, artist) =>
    {
        album.Genre = genre;
        album.Artist = artist;
        return album;
    }, splitOn: "GenreId,ArtistId");
}

就可以了。无论如何，您不需要GenreId和ArtistId，因为您在Albums中有对这些对象的引用。

我也遇到过同样的问题。这里有一个技巧& &;例子。

public abstract class BaseEntity
{
    [Key]
    public int Id { get; set; }
}
public class Category : BaseEntity
{
    public string Name { get; set; }
}

public class Status : BaseEntity
{
    public string Name { get; set; }
}
public class User : BaseEntity
{
    public string Name { get; set; }
    public string Surname { get; set; }
    public bool Active { get; set; }
}

public class TodoItem : BaseEntity
{
    public string Title { get; set; }
    public string Message { get; set; }
    public Status Status { get; set; }
    public Category Category { get; set; }
    public User User { get; set; }
    public DateTime CreatedOn { get; set; }
}
使用

            string sql = @"select 
                          t.Id,
                          t.Title,
                          t.Message,
                          t.CreatedOn,
                          s.Id as Id,
                          s.Name,
                          c.Id as Id,
                          c.Name,
                          u.Id as Id,
                          u.Name,
                          u.Surname,
                          u.Active
                         from ToDoItem t
                        inner join [Status] s on (t.StatusId = s.Id)
                        inner join [Category] c on (t.CategoryId = c.Id)
                        inner join [User] u on (t.AssignUserId = u.Id)";
            var result = connection.Query<TodoItem, Status, Category, User, TodoItem>
                (sql, (todoItem, status, category, user) =>
            {
                todoItem.Status = status;
                todoItem.Category = category;
                todoItem.User = user;
                return todoItem;
            },splitOn: "Id,Id,Id,Id");

这是技巧 splitOn:"Id, Id, Id, Id"

我也有这个问题，并发现问题是我没有选择。id列为我的每个表在选择

相关文章：