title: How can I use Dapper with a SELECT stored procedure containing an INNER JOIN between two tables?
body: <p>I am experimenting with Dapper for the first time. I have two tables: Films and Ratings.</p>
  
  <pre><code>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 
  )
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>return this._db.Query&lt;Film, Rating, Film&gt;(
              "dbo.GetFilms",
              (f, r) =&gt;
              {
                  f.Rating = r;
                  return f;
              },
              splitOn: "RatingId",
              commandType: CommandType.StoredProcedure
              ).ToList();
  </code></pre>
  
  <p>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.</p>