Is it possible to use .QueryMultiple
(or some other method) in Dapper, and use the results of each former query to be used in the where clause of the next query, without having to do each query individually, get the id, and then .Query
again, get the id and so on.
For example,
string sqlString = @"select tableA_id from tableA where tableA_lastname = @lastname;
select tableB_id from tableB WHERE tableB_id = tableA_id";
db.QueryMultiple.(sqlString, new {lastname = "smith"});
Is something like this possible with Dapper or do I need a view or stored procedure to accomplish this? I can use multiple joins for one SQL statement, but in my real query there are 7 joins, and I didn't think I should return 7 objects.
Right now I'm just using object
.
You can store every previous query in table parameter and then first perform select from the parameter and query for next, for example:
DECLARE @TableA AS Table(
tableA_id INT
-- ... all other columns you need..
)
INSERT @TableA
SELECT tableA_id
FROM tableA
WHERE tableA_lastname = @lastname
SELECT *
FROM @TableA
SELECT tableB_id
FROM tableB
JOIN tableA ON tableB_id = tableA_id