Issues with Dapper query syntax

little assistance here with my query using dapper, been getting error Message = “ORA-00936: missing expressionn” on my query. I would like to know what am I missing here?

  public class LocationDto
        {
            public int LocationId { get; set; }
            public int RouteId { get; set; }
            public string StartTime { get; set; }
            public string Location { get; set; }
        }
// Query Below
    using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
                {
     try {
                        var x = connection.QueryAsync<LocationDto>("Select ROUTE_ID as RouteId,  SCHEDULE_STOP as Location, START_TIME as StartTime From SCHEDULE WHERE ROUTE_ID = @Id", new { input.RouteId }).Result.ToList();
    
                    }
                    catch (Exception ex)
                    { 
                    
                    }
    }

Answer

Firstly with Oracle queries you need to use : instead of @ to denote a parameter placeholder.

Then secondly this code:

new { input.RouteId }

will produce an object with a property called RouteId.

This RouteId does not match the name of the parameter in your query, which is Id. They need to match, otherwise there’s no way for the database to bind the parameter to the correct input value.

You can either

1. change the Sql query:

   WHERE ROUTE_ID = :RouteId
   

OR

2. change the C#:

   new { Id = input.RouteId }
   

and use :Id in the SQL.

It doesn’t really matter which, the important thing is that the names match.