This is an old revision of the document!
It's quite common situation when the queries generated with EasyQuery components must have an additional condition(s) not visible to end-users. For example you may need to limit the result set by user ID, department ID or some time frames. EasyQuery provides you with 2 possible ways of resolving this task:
To insert extra conditions into generated SQL statements you can use BuildSQLEx() function of SqlQueryBuilder class (instead of BuildSQL you are using by default) and pass necessary condition(s) in its second parameter. The value of that parameter will be added into result SQL statement at the end of WHERE clause with AND conjunction to conditions, defined by end-users through visual controls.
The only trick here - you may also need to list all tables which take part in that extra condition using ExtraTables property of DbQuery class. It's necessary to ensure that all necessary table joins will be included into result SQL.
Example:
Korzh.EasyQuery.Db.Table table = model.Tables.FindByName("Customers"); query.ExtraTables.Add(table); SqlQueryBuilder builder = new SqlQueryBuilder(query); builder.BuildSQLEx("", "Customers.CustomerID = 'ALFKI'");
Since version 3.5.0 of EasyQuery ASP.NET you can use more simple and convenient way of inserting extra conditions:
query.ExtraConditions.AddSimpleCondition("Customers.CustomerID", "Equal", "ALFKI");
Now there is no need to deal with ExtraTables or use the exact SQL expression for your extra condition.
AddSimpleCondition method will take care about everything.
Discussion
Actually, adding table into ExtraTables list is just a guarantee that the table will be included into generated SQL.
protected override string GenerateQueryStatement(Query query) {
SqlQueryBuilder builder = new SqlQueryBuilder((DbQuery)query);
if (builder.CanBuild) {
builder.BuildSQL();
return builder.Result.SQL;
}
else
return string.Empty;
}
Just change one call
''builder.BuildSQL()''
to something similar to the code listed in this article.
protected override string GenerateQueryStatement(Query query) {
SqlQueryBuilder builder = new SqlQueryBuilder((DbQuery)query);
if (builder.CanBuild) {
Korzh.EasyQuery.Db.Table table = Model.Tables.FindByName("policies");
query.ExtraTables.Add(table);
builder.BuildSQLEx("", "policies.PolicyID = 5");
return builder.Result.SQL;
}
else
return string.Empty;
}
I get the following error:
'Korzh.EasyQuery.Query' does not contain a definition for 'ExtraTables' and no extension method 'ExtraTables' accepting a first argument of type 'Korzh.EasyQuery.Query' could be found (are you missing a using directive or an assembly reference?)
((DbQuery)query).ExtraTables.Add(table);
When I don't include any field of table2 on the query it will rise the error "Cannot find a path between tables", if I include in the query a field of table2 the error won't be rise.
What is missing?
I execute builder.BuildSQLEx("", "Customers.CustomerID = 'ALFKI'");
then I execute builder.BuildSQL();
and there es where the error rise.
Just by executing builder.BuildSQLEx and erasing builder.BuildSQL() does the work.
Send them both to techsupport{at}korzh.com
doesnt work well.
if i use it to add a condition it adds an extra "AND" after condition due to which query execution fails.
This function is used to add "extra" conditions which are not visible to users but it your query must have at least one "normal" condition to use this method.
such as Model Specified in Korzh.EasyQuery.Db.Table table = Model.Tables.FindByName("policies")
Can you please paste the code to get Model, Whenloading entities/Data Model from xml file
DbModel model = (DbModel)query.Model;
Also I have one more query as, How to add extra condition in "ExecuteQuery(string queryJson, string optionsJson)" method of EQMvcDemoEF.NET45 Demo MVC project, Which works on sending json format query to the ExceuteQuery action method. Also can please provide us any idea regrading adding custom paging in the result grid of the tool.
After that you can use this object to add extra conditions.