时间戳

首页 > 解决方案 > Dapper Multi 映射不正确

c# - Dapper Multi 映射不正确

问题描述

我正在尝试使用 Dapper 映射两个实体。就像这样：

您有表电影：电影 ID、运行时间、描述、标题、导演、流派

和表投影：ProjectionID、Time、Price、Hall、MovieID

电影有一个投影列表（在每部电影之前有一个或多个投影）

将电影与其投影的结果列表连接后，就是第一部电影和投影的所有记录。

基本上，Movie1 有两个 Projections Movie2 有三个 Projections 它返回五部电影的列表，它们都是第一部电影，它们都有五个投影。

有人可以帮我在哪里犯错吗？

public List<Movie> ReturnMovieProjections()
{
    var list = new List<Movie>();
    var sql = "SELECT " +
                        "m.MovieID, " +
                        "m.Runtime, " +
                        "m.Description, " +
                        "m.Title, " +
                        "m.Director, " +
                        "m.Genres, " +
                        "p.MovieID, " +
                        "p.ProjectionID, " +
                        "p.Time, " +
                        "p.Price, " +
                        "p.Hall " +
                  "FROM Movie AS m INNER JOIN Projection AS p " +
                    "ON m.MovieID = p.MovieID " +
                  "WHERE p.MovieID = m.MovieID";

     using (var connection = new OleDbConnection(GetConnectionString("CinemaDB")))
     {
         var movieDictionary = new Dictionary<int, Movie>();
         list = connection.Query<Movie, Projection, Movie>(
         sql, (movie, projection) =>
         {
             Movie movieEntry;
             if (!movieDictionary.TryGetValue(movie.MovieID, out movieEntry))
             {
                 movieEntry = movie;
                 movieEntry.Projections = new List<Projection>();
                 movieDictionary.Add(movieEntry.MovieID, movieEntry);
             }
             movieEntry.Projections.Add(projection);
             return movieEntry;
         },
         splitOn: "p.MovieID").AsList();
        }
        return list;
    }

标签： c#dapper

解决方案

您应该返回字典值而不是 Dapper 获得的 IEnumerable。
该值将始终包含查询生成的所有 5 条记录

也不需要添加等于 JOIN 语句的 WHERE 条件

var sql = "SELECT " +
                "m.MovieID, " +
                "m.Runtime, " +
                "m.Description, " +
                "m.Title, " +
                "m.Director, " +
                "m.Genres, " +
                "p.MovieID, " +
                "p.ProjectionID, " +
                "p.Time, " +
                "p.Price, " +
                "p.Hall " +
          "FROM Movie AS m INNER JOIN Projection AS p " +
            "ON m.MovieID = p.MovieID ";
 using (var connection = new OleDbConnection(GetConnectionString("CinemaDB")))
{
    var movieDictionary = new Dictionary<int, Movie>();
    list = connection.Query<Movie, Projection, Movie>(sql, (movie, projection) =>
    {
         Movie movieEntry = null;
         if (!movieDictionary.TryGetValue(movie.MovieID, out movieEntry))
         {
             movieEntry = movie;
             movieEntry.Projections = new List<Projection>();
             movieDictionary.Add(movieEntry.MovieID, movieEntry);
         }
         movieEntry.Projections.Add(projection);
         return movie; // return the same instance passed by Dapper.
    },splitOn: "p.MovieID");
}
return movieDictionary.Values.ToList();

推荐阅读