Query WHERE clause

The condition expression in the WHERE clause in a Magentrix Query allows you to filter the records on Magentrix Entities. You can add multiple field expressions to a condition expression by using logical operators: && (AND), || (OR).

var data = Database.Query<Account>()
  .Where(f => f.Type == "Customer"&& f.Name.StartsWith("abc"))
  .ToList();

var data = Database.Query<Contact>()
  .Where(f => f.Type.EndsWith("Client") || f.Email != null)
  .ToList();

You can use parentheses to define the order in which the field expressions are evaluated. In the following example, the result would be true if the first expression is true and either expression 2 or expression 3 are true:

expression1 && (expression2 || expression3)

Multiple operators of the same type do not need to be nested within parentheses.
 

In order to apply for the NOT operator, you can use the "!" sign:

(expr1 != "abc")

!(expr1 == "abc" && expr2 == "xzy")

!string.IsNullOrEmpty(field1)

Comparison Operators

You can use a number of operators to compare expressions/values together:

Filtering on text fields

In order to filter records using TEXT fields, Magentrix provides you with a number of method calls that you can use in your WHERE clause.

// In order to do exact match
.Where(f => f.Name == "David")

// In order to do partial match (LIKE operation)
.Where(f => f.Name.Contains("avi"))

// In order to match with beginning or ending of an expression
.Where(f => f.Name.StartsWith("Da"))
.Where(f => f.Name.EndsWith("vid"))

// In order to compare substring, passing the start index as argument
.Where(f => f.Name.Substring(2)=="vid") 

// In order to convert to uppercase before comprison
.Where(f => f.Name.ToUpper()=="DAVID")

// In order to convert to lowercase before comprison
.Where(f => f.Name.ToLower()=="david")

// In order to trim whitespaces before comprison
.Where(f => f.Name.Trim()=="david")

// In order to filter out null, empty
.Where(f => !string.IsNullOrEmpty(f.Name))

// In order to filter out null, empty and whitepace
.Where(f => !string.IsNullOrWhiteSpace(f.Name))

Filtering on Boolean fields

To filter on a Boolean field, use the following syntax:

.Where(f => f.IsActive == true)

.Where(f => f.IsActive == false)

Filtering on Date or DateTime fields

In order to filter records using Date or DateTime fields, the Magentrix platform provides a number of methods you can use to filter the Entity records:

// Records created during past 30 days
var past30Days = DateTime.UtcNow.Date.AddDays(-30);
var data = Database.Query<Account>()
   .Where(f => SQL.AsDate(f.CreatedOn) >= past30Days)
   .ToList();

// Comparing with Date part, possible values are:
// year, month, day, week, weekday, hour, minute, second, millisecond
.Where(f => SQL.DatePart("year",f.CreatedOn) == 2014)

// you can also alter the date value to compare with certain date times by adding
// years, months, days, hours, minutes or seconds
.Where(f => SQL.AddMinutes(f.CreatedOn, 30))

Filtering on Multi-Select Picklists

In order to filter records by multi-select picklists, you need to use a specific syntax as shown below:

//In this example, possible multi-select values are:
// AAA, BBB, CCC, DDD

.Where(f => SQL.Includes(f.Grades,"AAA;CCC","DDD"))

In the above example, the query filter values in the Grades field include either of these values (OR):

• AAA and CCC selected
• DDD selected

In order to filter records where a specific item is not selected:

.Where(f => SQL.Excludes(f.Grades,"BBB"))

Filter records based on a list of values

You can filter picklists or other fields based on a list of values you have:

List<string> values = new List<string> { "Pending", "Draft", "In Progress" };

// generates a SQL statement such as Status IN ('Pending','Draft','In Progress')
var data = Database.Query<Task>()
   .Where(f => values.Contains(f.Status))
   .ToList();