Extracting data from DapperRow

avif · May 23, 2020, 6:47am

Hi,
I have a use-case which needs in one query 12 joins.
How can the following query be extracted to List<ResearchEntity>?
Thanks

var from =
        db.From<ResearchEntity>()
       .LeftJoin<ResearchEntity, NameEntity>((x, y) => x.Id == y.ResearchId)
       .LeftJoin<ResearchEntity, CountryEntity>((x, y) => x.Id == y.ResearchId)
       .LeftJoin<ResearchEntity, EmailEntity>((x, y) => x.Id == y.ResearchId)
       .LeftJoin<ResearchEntity, PhoneEntity>((x, y) => x.Id == y.ResearchId)
       /// 12 joins in total...
       .SelectDistinct(x => x.Id);

       using (var multi = db.QueryMultiple(from.ToSelectStatement()))
       {
            //var res = multi.Read<dynamic>();
            
       }

mythz · May 23, 2020, 7:59am

QueryMultiple is Dapper’s API, a verbatim copy is embedded in OrmLite since they’re both Micro ORMs providing extension methods off IDbConnection, but otherwise they’re separate & isolated code-bases, your example uses OrmLite to generate the SQL (albeit invalid for selecting 12 tables for usage in QueryMultiple), but Dapper to execute it, please refer to Dapper’s project and docs for its correct usage.

OrmLite uses SelectMulti for selecting multiple tables.

avif · May 23, 2020, 8:06am

SelectMulti is limited to 6 or seven joins, correct?
Thanks

mythz · May 23, 2020, 8:08am

Not limited by joins, but by selecting in 7 tables:

github.com

ServiceStack/ServiceStack.OrmLite/blob/2cb47f40a5f2fc25cf2a34cd6d93954e5165d87b/src/ServiceStack.OrmLite/OrmLiteReadExpressionsApi.cs#L240


public static List<Tuple<T, T2>> SelectMulti<T, T2>(this IDbConnection dbConn, SqlExpression<T> expression) => dbConn.Exec(dbCmd => dbCmd.SelectMulti<T, T2>(expression));


public static List<Tuple<T, T2, T3>> SelectMulti<T, T2, T3>(this IDbConnection dbConn, SqlExpression<T> expression) => dbConn.Exec(dbCmd => dbCmd.SelectMulti<T, T2, T3>(expression));


public static List<Tuple<T, T2, T3, T4>> SelectMulti<T, T2, T3, T4>(this IDbConnection dbConn, SqlExpression<T> expression) => dbConn.Exec(dbCmd => dbCmd.SelectMulti<T, T2, T3, T4>(expression));


public static List<Tuple<T, T2, T3, T4, T5>> SelectMulti<T, T2, T3, T4, T5>(this IDbConnection dbConn, SqlExpression<T> expression) => dbConn.Exec(dbCmd => dbCmd.SelectMulti<T, T2, T3, T4, T5>(expression));


public static List<Tuple<T, T2, T3, T4, T5, T6>> SelectMulti<T, T2, T3, T4, T5, T6>(this IDbConnection dbConn, SqlExpression<T> expression) => dbConn.Exec(dbCmd => dbCmd.SelectMulti<T, T2, T3, T4, T5, T6>(expression));


public static List<Tuple<T, T2, T3, T4, T5, T6, T7>> SelectMulti<T, T2, T3, T4, T5, T6, T7>(this IDbConnection dbConn, SqlExpression<T> expression) => dbConn.Exec(dbCmd => dbCmd.SelectMulti<T, T2, T3, T4, T5, T6, T7>(expression));




public static List<Tuple<T, T2>> SelectMulti<T, T2>(this IDbConnection dbConn, SqlExpression<T> expression, string[] tableSelects) => dbConn.Exec(dbCmd => dbCmd.SelectMulti<T, T2>(expression, tableSelects));


public static List<Tuple<T, T2, T3>> SelectMulti<T, T2, T3>(this IDbConnection dbConn, SqlExpression<T> expression, string[] tableSelects) => dbConn.Exec(dbCmd => dbCmd.SelectMulti<T, T2, T3>(expression, tableSelects));


public static List<Tuple<T, T2, T3, T4>> SelectMulti<T, T2, T3, T4>(this IDbConnection dbConn, SqlExpression<T> expression, string[] tableSelects) => dbConn.Exec(dbCmd => dbCmd.SelectMulti<T, T2, T3, T4>(expression, tableSelects));


public static List<Tuple<T, T2, T3, T4, T5>> SelectMulti<T, T2, T3, T4, T5>(this IDbConnection dbConn, SqlExpression<T> expression, string[] tableSelects) => dbConn.Exec(dbCmd => dbCmd.SelectMulti<T, T2, T3, T4, T5>(expression, tableSelects));

avif · May 23, 2020, 8:19am

Yes this is what i meant…
I can do how many joins that i want, but i can select / present data from 7…
Thank