OrmLite pass tuple of parameter

Question

I want to pass a list of objects with two properties as a parameter in a ServiceStack OrmLite query but it is run an error "The member of type Mgh.Application.Queries.GetDataStepThreeQuery+Configuration cannot be used as a parameter value"

Parameter class:

public class Configuration
    {
        public int RoomTypeId { get; set; }
        public int Adults { get; set; }
    }

Query:

select * from (values @Configurations) as V(InternalNameRoomId, MaximumPersons)
where r.InternalNameRoomId = V.InternalNameRoomId and
      r.MaximumPersons = V.MaximumPersons

Pass parameter:

var rooms = _db.Query<Room>(sqlRooms, new
    {
       request.From,
       request.To,
       request.EstablishmentId,
       Configurations = request.SelectedConfigurations.ToArray()
    });

score 1 · Answer 1 · answered Mar 18 '19 at 15:09

1

The SQL you’re trying to execute is invalid, RDBMS’s have no concept of Tuple parameters and I don’t know what SQL Feature you’re trying to Execute that selects from a deconstructed Tuple in SQL that your example is trying to do.

Start with valid SQL that you know works by running it against your database then you can use it in OrmLite, but you’ll need to split your Tuple into separate db parameters, e.g:

new { item1 = Tuple.Item1, item2 = Tuple.Item2 }

Also db.Query uses the embedded version of Dapper in OrmLite, the equivalent API in OrmLite is db.SqlList or db.Select but your SQL would work in neither.

answered Mar 18 '19 at 15:09

mythz

141,670
29
246
390

Of course, the sql works, I've taken it out of this forum https://stackoverflow.com/questions/8006901/using-tuples-in-sql-in-clause – Federico Fia Sare Mar 19 '19 at 19:29
@FedericoFiaSare it doesn't work for db parameters, i.e. you can't destruct ADO.NET DB Tuple parameters in SQL, nor can you have Tuple DB parameters. – mythz Mar 19 '19 at 19:31

OrmLite pass tuple of parameter

1 Answers1