Is it possible to chain subsequent queries's where clauses in Dapper based on the results of a previous query in the same connection?

Question

Is it possible to chain subsequent queries's where clauses in Dapper based on the results of a previous query in the same connection?

asp.net-core-2.0 dapper

Question

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.

johnny

View more on Stack Overflow

Daniel Tshuva · Accepted Answer · 4/3/2018 7:20:40 AM

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

Is it possible to chain subsequent queries's where clauses in Dapper based on the results of a previous query in the same connection?

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator