Using something like ToMergedParamsSelectStatement with joined tables

1 
var query =
    db.From<ResearchEntity>()
    .LeftJoin<ResearchEntity, ProfileImageEntity>((x, y) => x.Id == y.ResearchId && y.IsPrimary,new TableOptions { Alias = "ProfileImageEntity" })
   .LeftJoin<ResearchEntity, NameEntity>((x, y) => x.Id == y.ResearchId && y.IsPrimary, new TableOptions { Alias = "NameEntity" })
   .LeftJoin<ResearchEntity, CountryEntity>((x, y) => x.Id == y.ResearchId && y.IsPrimary, new TableOptions { Alias = "CountryEntity" })
   .LeftJoin<ResearchEntity, EmailEntity>((x, y) => x.Id == y.ResearchId && y.IsPrimary, new TableOptions { Alias = "EmailEntity" })
   .LeftJoin<ResearchEntity, PhoneEntity>((x, y) => x.Id == y.ResearchId && y.IsPrimary, new TableOptions { Alias = "PhoneEntity" })
     .OrderByDescending(x => x.Id)
   .Limit(0,5).ToMergedParamsSelectStatement()

Hi,
Is there a similar way to include in the select statement also the joined tables according to their aliases?
For now, only ResearchEntity is included.
Thanks

2

There’s no other API like this, what’s the current generated SQL output?

3 
SELECT `Research`.`LogId`, `Research`.`Description`, `Research`.`Favorite`, `Research`.`CreatedById`, `Research`.`LastUpdatedById`, `Research`.`ParentResearchId`, `Research`.`SearchPointer`, `Research`.`Id`, `Research`.`CreatedAt`, `Research`.`UpdatedAt`, `Research`.`Deleted`
FROM `Research`
LEFT JOIN `ProfileImageEntity` `ProfileImageEntity` ON ((`Research`.`Id` = `ProfileImageEntity`.`ResearchId`) AND `ProfileImageEntity`.`IsPrimary`=1)
LEFT JOIN `NameEntity` `NameEntity` ON ((`Research`.`Id` = `NameEntity`.`ResearchId`) AND `NameEntity`.`IsPrimary`=1)
LEFT JOIN `CountryEntity` `CountryEntity` ON ((`Research`.`Id` = `CountryEntity`.`ResearchId`) AND `CountryEntity`.`IsPrimary`=1)
LEFT JOIN `EmailEntity` `EmailEntity` ON ((`Research`.`Id` = `EmailEntity`.`ResearchId`) AND `EmailEntity`.`IsPrimary`=1)
LEFT JOIN `PhoneEntity` `PhoneEntity` ON ((`Research`.`Id` = `PhoneEntity`.`ResearchId`) AND `PhoneEntity`.`IsPrimary`=1)
ORDER BY `Research`.`Id` DESC
LIMIT 5 OFFSET 0
4

Ok so what’s the issue?

5

I want to read data from the joined tables. this way it is selecting only from the FROM table (ResearchEntity).
I cant really read from ProfileImageEntity for example…

6

I don’t understand, the query is using the table aliases you’ve specified?

7

Nevertheless, a little miscommunication.
After I solved the problem, I wonder why the .Where is generating an sql with param “@0”

var query =
             Db.From<ResearchEntity>(new TableOptions { Alias = "ResearchEntity" })
            .Where(x => x.ParentResearchId == 1)
            query.ToCountStatement();

this will generate:

SELECT count(*)
FROM `Research` `ResearchEntity`
WHERE (`ResearchEntity`.`ParentResearchId` = @0)

Why is it not generating: WHERE (ResearchEntity.ParentResearchId = 1)?
I would love to keep my query strong typed (as much as possible).

Thanks

8

Why wouldn’t it use a parameterized query? which is SqlExpression<T> default behavior.

9

I’m using Db.From which is SqlExpression<T>.
Again, want to run a strong typed query and be able to perform .Where(x => x.ParentResearchId == 1)…
Isn’t parameterized query cancel the strong type thing? can you post very small example?
Thanks

10

I’ve no idea what you’re asking, but the behavior of SqlExpression<T> is working exactly as expected which is as Typed SqlExpression builder for OrmLite queries, if it doesn’t do what you want, don’t use it.

11

This is a strong type query converted to SQL using ToCountStatement:

The problem is that .Where(x => x.ParentResearchId == 1) expects a param when it is converted to sql: WHERE (ResearchEntity.ParentResearchId = @0)

  1. How do I pass @0 to Db.Query() when I run the query?
  2. BTW, isn’t it suppose to generate (ResearchEntity.ParentResearchId = 1)?

I hope I am more clear now.
Thanks.

12

That’s not a problem, that’s exactly what it’s designed to do, generates a parameterized query for execution by OrmLite APIs that accept an SqlExpression.

  1. You don’t, the param is already configured in the SqlExpression Params collection
  2. No, it uses parameterized queries by design, as-is the preference of every ORM
13

This is what I thought. but this:

 var query =  db.From<ResearchEntity>(new TableOptions { Alias = "ResearchEntity" })
                         .Where(x => x.ParentResearchId == 1);
            var countQuery = query.ToCountStatement();
            var c = db.QuerySingle<int>(countQuery);

will throw this:

Message: 
    MySql.Data.MySqlClient.MySqlException : Fatal error encountered during command execution.
    ---- MySql.Data.MySqlClient.MySqlException : Parameter '@0' must be defined.
  Stack Trace: 
    MySqlCommand.ExecuteReader(CommandBehavior behavior)
    MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    IDbCommand.ExecuteReader(CommandBehavior behavior)
    SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) line 1051
    SqlMapper.QueryRowImpl[T](IDbConnection cnn, Row row, CommandDefinition& command, Type effectiveType) line 1177
    SqlMapper.QuerySingle[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) line 781
    TestDb.Test() line 91
    --- End of stack trace from previous location where exception was thrown ---
    ----- Inner Stack Trace -----
    Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlPacket packet, String parmName, Int32 parameterIndex)
    Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet)
    Statement.BindParameters()
    Statement.Execute()
    PreparableStatement.Execute()
    MySqlCommand.ExecuteReader(CommandBehavior behavior)
14

Because you’re not passing the DB Parameters into the other ORM’s API you’re using that’s just executing the raw SQL without the DB parameters, i.e. query.Params.

Why aren’t you using OrmLite’s Count API?

var c = db.Count(query);
15

What I presented here is a small subset of the query.
I have more than 12 joins to run in one query (Count query and also regular query).
In my case when I use LoadSelectAsync I actually run 12 queries * rows, which is impossible. and also ormlite is limited to 7 tables

So what I thought to do (and you recommend it a while ago) is to generate SQL and use Dapper for that.
My entire app is using normal ormlite queries, I have 2-4 use cases where I need to join a lot of tables.

So,

This is what I am asking, how to pass the parameters.

16

When you use SqlExpression to create your query, it automatically configures the parameters to the query.Params collection, you don’t pass it later, it’s configured when you create the API.

You’ll need to look at Dapper’s documentation on how to execute raw SQL with db parameters, e.g its Dynamic Parameters Bag lets you configure params from an object dictionary:

var dictionary = new Dictionary<string, object>
{
    { "@ProductId", 1 }
};
var parameters = new DynamicParameters(dictionary);
var sql = "select * from products where ProductId = @ProductId";
using (var connection = new SqlConnection(connString))
{
    var product = connection.QuerySingle<Product>(sql, parameters);
}

Note: executing OrmLite generated SQL with Dapper’s APIs isn’t a design goal or supported scenario.

OrmLite has its own raw SQL + DB Parameterized APIs you could use to execute raw SQL + DB params, e.g:

var  c = db.SqlScalar<int>(query.ToCountStatement(), query.Params);
17

ok. Db.Select with query.Params is working for me.
thanks