DapperDinnerRepository Part 2

GenericFindDinner.cs

private PagedList<Dinner> FindDinners(string where, object parameters, string orderBy = "DinnerID", int page = 1, int pageSize = 20)
{
    using (var connection = MvcApplication.GetOpenConnection())
    {
        var builder = new SqlBuilder();

        var start = (page - 1) * pageSize + 1;
        var finish = page * pageSize;

        var selectTemplate = builder.AddTemplate(pagedQuery, new { start, finish });
        var countTemplate = builder.AddTemplate(totalQuery);

        builder.Where(where, parameters);

        builder.OrderBy(orderBy);

        var results = connection.Query<Dinner>(selectTemplate.RawSql, selectTemplate.Parameters);
        var count = connection.Query<int>(countTemplate.RawSql, countTemplate.Parameters).First();

        return new PagedList<Dinner>(results, page, count, pageSize);
    }
}

IDinnerRepository.cs

public interface IDinnerRepository
{
    PagedList<Dinner> FindByLocation(float latitude, float longitude, string orderBy = "DinnerID", int page = 1, int pageSize = 20);
    PagedList<Dinner> FindUpcomingDinners(string orderBy = "DinnerID", int page = 1, int pageSize = 20);
    PagedList<Dinner> FindUpcomingDinners(DateTime? eventDate, string orderBy = "DinnerID", int page = 1, int pageSize = 20);
    PagedList<Dinner> FindDinnersByText(string q, string orderBy = "DinnerID", int page = 1, int pageSize = 20);
    IEnumerable<Dinner> AllDinnersByUser(string name);

    Dinner Find(int id);

    void InsertOrUpdate(Dinner dinner);
    void InsertOrUpdate(RSVP rsvp);

    void Delete(int id);
    void DeleteRsvp(RSVP rsvp);
}

Query.sql

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (/**orderby**/) AS RowNumber FROM (
    SELECT d.*, COUNT(r.DinnerID) AS RsvpCount 
    FROM Dinners d LEFT OUTER JOIN RSVP r ON d.DinnerID = r.DinnerID 
    /**where**/
    GROUP BY d.DinnerID, d.Title, d.EventDate, d.Description, d.HostedById, d.HostedBy, d.ContactPhone, d.Address, d.Country, d.Latitude, d.Longitude
    ) as X 
  ) as Y
WHERE RowNumber BETWEEN @start AND @finish"