How to add an extra condition to your query

It's a quite common situation when the queries generated by 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 two possible ways of resolving this task:

1. ExtraConditions property of Query class

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 additional condition. AddSimpleCondition method will take care of everything.

ASP.NET Core project

Here is an example how to add an additional filter by current user in ASP.NET Core project:

In Startup.Configure:

endpoints.MapEasyQuery(options => {
   .    .    .    .    .
   options.AddPreFetchTunerWithHttpContext((manager, httpContext) => {
        string userId = context.User.FindFirst(ClaimTypes.NameIdentifier)?.Value;
        manager.Query.ExtraConditions.AddSimpleCondition("Employees.EmployeeID", "Equal", userId);
   });
});

ASP.NET MVC

The same task in ASP.NET MVC 5 project can be solved the following way:

public class AdvancedSearchController : EasyQueryApiController
{
    protected override void ConfigureEasyQueryOptions(EasyQueryOptions options)
    {
        .     .     .     .     .
        options.AddPreFetchTuner(new MyEasyQueryManagerTuner());
    }
}

public class MyEasyQueryManagerTuner : IEasyQueryManagerTuner
{
    public bool Tune(EasyQueryManager manager)
    {
        manager.Query.ExtraConditions.AddSimpleCondition("Users.ID", "Equals", HttpContext.Current.User.Identity.Name);
        return true;
    }
}

2. ''BuildSQLEx()'' method of ''SqlQueryBuilder''

To insert additional 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 to a 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 additional condition using ExtraTables property of DbQuery class. It's necessary to ensure that all necessary table joins will be included into result SQL.

Example:

var table = model.Tables.FindByName("Customers");
query.ExtraTables.Add(table);
 
var builder = new SqlQueryBuilder(query);
builder.BuildSQLEx("", "Customers.CustomerID = 'ALFKI'");