Database Filtering and Searching
Summary
This section covers advanced filtering techniques, comparison operators, text field filtering, date/datetime filtering, multi-select picklist filtering, and full-text search capabilities.
WHERE Clause Syntax (LINQ Expressions)
LINQ expressions provide type-safe filtering with compile-time checking.
Basic Comparison:
// Equal to
var accounts = Database.Query<Account>()
.Where(f => f.Type == "Partner")
.ToList();
// Not equal to
var accounts = Database.Query<Account>()
.Where(f => f.Type != "Prospect")
.ToList();
Logical Operators:
// AND - All conditions must be true
var accounts = Database.Query<Account>()
.Where(f =>
f.Type == "Partner" &&
f.IsActive == true &&
f.AnnualRevenue > 1000000
)
.ToList();
// OR - At least one condition must be true
var accounts = Database.Query<Account>()
.Where(f =>
f.Type == "Partner" ||
f.Type == "Customer"
)
.ToList();
// Complex combinations with parentheses
var accounts = Database.Query<Account>()
.Where(f =>
(f.Type == "Partner" || f.Type == "Customer") &&
f.IsActive == true &&
f.AnnualRevenue > 500000
)
.ToList();
Multiple Where Clauses:
// Chained Where clauses are combined with AND
var accounts = Database.Query<Account>()
.Where(f => f.Type == "Partner")
.Where(f => f.IsActive == true)
.Where(f => f.AnnualRevenue > 1000000)
.ToList();
// Equivalent to:
var accounts = Database.Query<Account>()
.Where(f =>
f.Type == "Partner" &&
f.IsActive == true &&
f.AnnualRevenue > 1000000
)
.ToList();
Comparison Operators
Numeric Comparisons:
// Greater than
var accounts = Database.Query<Account>()
.Where(f => f.AnnualRevenue > 1000000)
.ToList();
// Greater than or equal
var accounts = Database.Query<Account>()
.Where(f => f.AnnualRevenue >= 1000000)
.ToList();
// Less than
var accounts = Database.Query<Account>()
.Where(f => f.AnnualRevenue < 500000)
.ToList();
// Less than or equal
var accounts = Database.Query<Account>()
.Where(f => f.AnnualRevenue <= 500000)
.ToList();
// Equal to
var accounts = Database.Query<Account>()
.Where(f => f.AnnualRevenue == 1000000)
.ToList();
// Not equal to
var accounts = Database.Query<Account>()
.Where(f => f.AnnualRevenue != 0)
.ToList();
String Comparisons:
// Exact match
var accounts = Database.Query<Account>()
.Where(f => f.Name == "Partner Company")
.ToList();
// Not equal
var accounts = Database.Query<Account>()
.Where(f => f.Name != "Test Account")
.ToList();
// Contains
var accounts = Database.Query<Account>()
.Where(f => f.Name.Contains("Tech"))
.ToList();
// Starts with
var accounts = Database.Query<Account>()
.Where(f => f.Name.StartsWith("Mag"))
.ToList();
// Ends with
var accounts = Database.Query<Account>()
.Where(f => f.Name.EndsWith("Inc"))
.ToList();
Boolean Comparisons:
// Equal to true
var accounts = Database.Query<Account>()
.Where(f => f.IsActive == true)
.ToList();
// Equal to false
var accounts = Database.Query<Account>()
.Where(f => f.IsActive == false)
.ToList();
Null Comparisons:
// Is null
var contacts = Database.Query<Contact>()
.Where(f => f.AccountId == null)
.ToList();
// Is not null
var contacts = Database.Query<Contact>()
.Where(f => f.AccountId != null)
.ToList();
// String null or empty
var contacts = Database.Query<Contact>()
.Where(f => string.IsNullOrEmpty(f.Email))
.ToList();
// String has value
var contacts = Database.Query<Contact>()
.Where(f => !string.IsNullOrEmpty(f.Email))
.ToList();
IN and NOT IN:
// IN clause
var validTypes = new List<string> { "Partner", "Customer", "Vendor" };
var accounts = Database.Query<Account>()
.Where(f => validTypes.Contains(f.Type))
.ToList();
// NOT IN clause
var excludedTypes = new List<string> { "Prospect", "Inactive" };
var accounts = Database.Query<Account>()
.Where(f => !excludedTypes.Contains(f.Type))
.ToList();
Text Field Filtering
Case-Sensitive Matching:
// Exact match (case-sensitive)
var accounts = Database.Query<Account>()
.Where(f => f.Name == "Magentrix")
.ToList();
// Contains (case-sensitive)
var accounts = Database.Query<Account>()
.Where(f => f.Name.Contains("Tech"))
.ToList();
Case-Insensitive Matching:
// Case-insensitive exact match
var accounts = Database.Query<Account>()
.Where(f => f.Name.ToLower() == "magentrix")
.ToList();
// Case-insensitive contains
var accounts = Database.Query<Account>()
.Where(f => f.Name.ToLower().Contains("tech"))
.ToList();
// Case-insensitive starts with
var accounts = Database.Query<Account>()
.Where(f => f.Name.ToLower().StartsWith("mag"))
.ToList();
// Case-insensitive ends with
var accounts = Database.Query<Account>()
.Where(f => f.Email.ToLower().EndsWith("@magentrix.com"))
.ToList();
Multiple Text Field Conditions:
// Search across multiple fields
var searchTerm = "technology";
var accounts = Database.Query<Account>()
.Where(f =>
f.Name.ToLower().Contains(searchTerm) ||
f.Industry.ToLower().Contains(searchTerm) ||
f.Description.ToLower().Contains(searchTerm)
)
.ToList();
Empty String Checks:
// Check for null or empty
var contacts = Database.Query<Contact>()
.Where(f => string.IsNullOrEmpty(f.Email))
.ToList();
// Check for null, empty, or whitespace
var contacts = Database.Query<Contact>()
.Where(f => string.IsNullOrWhiteSpace(f.Description))
.ToList();
// Has value (not null or empty)
var contacts = Database.Query<Contact>()
.Where(f => !string.IsNullOrEmpty(f.Email))
.ToList();
Date/DateTime Filtering
Magentrix provides specialized SQL helper methods for filtering Date and DateTime fields. These methods handle date part extraction, date arithmetic, and date comparisons at the database level for optimal performance.
SQL.AsDate - Convert DateTime to Date
Use SQL.AsDate() to strip the time component from a DateTime field and compare only the date portion.
Basic Date Comparison:
// Records created on a specific date (ignoring time)
var targetDate = new DateTime(2024, 6, 15);
var accounts = Database.Query<Account>()
.Where(f => SQL.AsDate(f.CreatedOn) == targetDate)
.ToList();
// Records created on or after a date
var startDate = new DateTime(2024, 1, 1);
var accounts = Database.Query<Account>()
.Where(f => SQL.AsDate(f.CreatedOn) >= startDate)
.ToList();
// Records created within date range
var startDate = new DateTime(2024, 1, 1);
var endDate = new DateTime(2024, 12, 31);
var accounts = Database.Query<Account>()
.Where(f =>
SQL.AsDate(f.CreatedOn) >= startDate &&
SQL.AsDate(f.CreatedOn) <= endDate
)
.ToList();
Relative Date Filtering:
// Records created in last 30 days
var past30Days = DateTime.UtcNow.Date.AddDays(-30);
var accounts = Database.Query<Account>()
.Where(f => SQL.AsDate(f.CreatedOn) >= past30Days)
.ToList();
// Records created in last 7 days
var past7Days = DateTime.UtcNow.Date.AddDays(-7);
var contacts = Database.Query<Contact>()
.Where(f => SQL.AsDate(f.CreatedOn) >= past7Days)
.ToList();
// Records created in last 6 months
var past6Months = DateTime.UtcNow.Date.AddMonths(-6);
var opportunities = Database.Query<Opportunity>()
.Where(f => SQL.AsDate(f.CreatedOn) >= past6Months)
.ToList();
Current Period Filtering:
// Records created today
var today = DateTime.UtcNow.Date;
var todaysAccounts = Database.Query<Account>()
.Where(f => SQL.AsDate(f.CreatedOn) == today)
.ToList();
// Records created this week
var startOfWeek = DateTime.UtcNow.Date.AddDays(-(int)DateTime.UtcNow.DayOfWeek);
var thisWeekAccounts = Database.Query<Account>()
.Where(f => SQL.AsDate(f.CreatedOn) >= startOfWeek)
.ToList();
// Records created this month
var startOfMonth = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, 1);
var thisMonthAccounts = Database.Query<Account>()
.Where(f => SQL.AsDate(f.CreatedOn) >= startOfMonth)
.ToList();
// Records created this year
var startOfYear = new DateTime(DateTime.UtcNow.Year, 1, 1);
var thisYearAccounts = Database.Query<Account>()
.Where(f => SQL.AsDate(f.CreatedOn) >= startOfYear)
.ToList();
SQL.DatePart - Extract Date Components
Use SQL.DatePart() to extract and filter by specific parts of a date or datetime field.
Supported Date Parts:
"year" - Extract year (e.g., 2024)"month" - Extract month (1-12)"day" - Extract day of month (1-31)"week" - Extract week number (1-53)"weekday" - Extract day of week (1-7, Sunday=1)"hour" - Extract hour (0-23)"minute" - Extract minute (0-59)"second" - Extract second (0-59)"millisecond" - Extract millisecond (0-999)
Filter by Year:
// Records created in 2024
var accounts = Database.Query<Account>()
.Where(f => SQL.DatePart("year", f.CreatedOn) == 2024)
.ToList();
// Records created in 2023 or 2024
var accounts = Database.Query<Account>()
.Where(f =>
SQL.DatePart("year", f.CreatedOn) == 2023 ||
SQL.DatePart("year", f.CreatedOn) == 2024
)
.ToList();
// Records created after 2020
var accounts = Database.Query<Account>()
.Where(f => SQL.DatePart("year", f.CreatedOn) > 2020)
.ToList();
Filter by Month:
// Records created in December (month 12)
var decemberAccounts = Database.Query<Account>()
.Where(f => SQL.DatePart("month", f.CreatedOn) == 12)
.ToList();
// Records created in Q1 (January, February, March)
var q1Accounts = Database.Query<Account>()
.Where(f => SQL.DatePart("month", f.CreatedOn) <= 3)
.ToList();
// Records created in Q4 (October, November, December)
var q4Accounts = Database.Query<Account>()
.Where(f => SQL.DatePart("month", f.CreatedOn) >= 10)
.ToList();
Filter by Day of Month:
// Records created on the 1st of any month
var firstDayAccounts = Database.Query<Account>()
.Where(f => SQL.DatePart("day", f.CreatedOn) == 1)
.ToList();
// Records created on the 15th or later in the month
var midMonthAccounts = Database.Query<Account>()
.Where(f => SQL.DatePart("day", f.CreatedOn) >= 15)
.ToList();
Filter by Day of Week:
// Records created on Mondays (weekday = 2, Sunday=1)
var mondayAccounts = Database.Query<Account>()
.Where(f => SQL.DatePart("weekday", f.CreatedOn) == 2)
.ToList();
// Records created on weekends (Saturday=7, Sunday=1)
var weekendAccounts = Database.Query<Account>()
.Where(f =>
SQL.DatePart("weekday", f.CreatedOn) == 1 ||
SQL.DatePart("weekday", f.CreatedOn) == 7
)
.ToList();
// Records created on weekdays (Monday-Friday)
var weekdayAccounts = Database.Query<Account>()
.Where(f =>
SQL.DatePart("weekday", f.CreatedOn) >= 2 &&
SQL.DatePart("weekday", f.CreatedOn) <= 6
)
.ToList();
Filter by Hour:
// Records created during business hours (9 AM - 5 PM)
var businessHourAccounts = Database.Query<Account>()
.Where(f =>
SQL.DatePart("hour", f.CreatedOn) >= 9 &&
SQL.DatePart("hour", f.CreatedOn) < 17
)
.ToList();
// Records created after hours
var afterHourAccounts = Database.Query<Account>()
.Where(f =>
SQL.DatePart("hour", f.CreatedOn) < 9 ||
SQL.DatePart("hour", f.CreatedOn) >= 17
)
.ToList();
Combine Multiple Date Parts:
// Records created in December 2024
var accounts = Database.Query<Account>()
.Where(f =>
SQL.DatePart("year", f.CreatedOn) == 2024 &&
SQL.DatePart("month", f.CreatedOn) == 12
)
.ToList();
// Records created on Monday mornings in 2024
var accounts = Database.Query<Account>()
.Where(f =>
SQL.DatePart("year", f.CreatedOn) == 2024 &&
SQL.DatePart("weekday", f.CreatedOn) == 2 &&
SQL.DatePart("hour", f.CreatedOn) < 12
)
.ToList();
SQL Date Arithmetic Methods
Use SQL arithmetic methods to add or subtract time intervals from DateTime fields during query execution.
Available Methods:
SQL.AddYears(field, years) - Add/subtract yearsSQL.AddMonths(field, months) - Add/subtract monthsSQL.AddDays(field, days) - Add/subtract daysSQL.AddHours(field, hours) - Add/subtract hoursSQL.AddMinutes(field, minutes) - Add/subtract minutesSQL.AddSeconds(field, seconds) - Add/subtract seconds
Add/Subtract Days:
// Find records expiring within 30 days
var expiringAccounts = Database.Query<Account>()
.Where(f => SQL.AddDays(f.ExpirationDate__c, -30) <= DateTime.UtcNow)
.ToList();
// Find records that expired more than 90 days ago
var expiredAccounts = Database.Query<Account>()
.Where(f => SQL.AddDays(f.ExpirationDate__c, 90) < DateTime.UtcNow)
.ToList();
Add/Subtract Hours and Minutes:
// Find tasks with reminder 30 minutes before due date
var reminderTime = DateTime.UtcNow;
var tasks = Database.Query<Task>()
.Where(f => SQL.AddMinutes(f.DueDate, -30) <= reminderTime)
.ToList();
// Find records created more than 2 hours ago
var twoHoursAgo = DateTime.UtcNow;
var accounts = Database.Query<Account>()
.Where(f => SQL.AddHours(f.CreatedOn, 2) < twoHoursAgo)
.ToList();
Add/Subtract Months and Years:
// Find contracts expiring in next 3 months
var threeMonthsOut = DateTime.UtcNow;
var contracts = Database.Query<Contract>()
.Where(f =>
f.EndDate >= DateTime.UtcNow &&
SQL.AddMonths(f.EndDate, -3) <= threeMonthsOut
)
.ToList();
// Find annual reviews due (created date + 1 year)
var reviewDue = Database.Query<Account>()
.Where(f => SQL.AddYears(f.LastReviewDate__c, 1) <= DateTime.UtcNow)
.ToList();
Complex Date Filtering Examples
Business Days Calculation:
// Find records created on weekdays in last 30 days
var past30Days = DateTime.UtcNow.Date.AddDays(-30);
var accounts = Database.Query<Account>()
.Where(f =>
SQL.AsDate(f.CreatedOn) >= past30Days &&
SQL.DatePart("weekday", f.CreatedOn) >= 2 &&
SQL.DatePart("weekday", f.CreatedOn) <= 6
)
.ToList();
Quarterly Reporting:
// Q1 2024 (January - March)
var q1Accounts = Database.Query<Account>()
.Where(f =>
SQL.DatePart("year", f.CreatedOn) == 2024 &&
SQL.DatePart("month", f.CreatedOn) >= 1 &&
SQL.DatePart("month", f.CreatedOn) <= 3
)
.ToList();
// Q4 2024 (October - December)
var q4Accounts = Database.Query<Account>()
.Where(f =>
SQL.DatePart("year", f.CreatedOn) == 2024 &&
SQL.DatePart("month", f.CreatedOn) >= 10
)
.ToList();
Anniversary Filtering:
// Find accounts with anniversary this month
var currentMonth = DateTime.UtcNow.Month;
var anniversaryAccounts = Database.Query<Account>()
.Where(f => SQL.DatePart("month", f.CreatedOn) == currentMonth)
.ToList();
// Find accounts with 1-year anniversary coming up
var oneYearAgo = DateTime.UtcNow.Date.AddYears(-1);
var anniversaryAccounts = Database.Query<Account>()
.Where(f =>
SQL.DatePart("month", f.CreatedOn) == oneYearAgo.Month &&
SQL.DatePart("day", f.CreatedOn) == oneYearAgo.Day
)
.ToList();
Service Level Agreement (SLA) Monitoring:
// Find cases approaching 4-hour SLA
var cases = Database.Query<Case>()
.Where(f =>
f.Status != "Closed" &&
SQL.AddHours(f.CreatedOn, 4) <= DateTime.UtcNow
)
.ToList();
// Find cases breaching 24-hour SLA
var breachedCases = Database.Query<Case>()
.Where(f =>
f.Status != "Closed" &&
SQL.AddHours(f.CreatedOn, 24) < DateTime.UtcNow
)
.ToList();
Date/DateTime Best Practices
✅ Use SQL.AsDate() for date-only comparisons
// ✅ GOOD - Ignores time component
var accounts = Database.Query<Account>()
.Where(f => SQL.AsDate(f.CreatedOn) == DateTime.UtcNow.Date)
.ToList();
// ❌ BAD - Time component causes issues
var accounts = Database.Query<Account>()
.Where(f => f.CreatedOn.Date == DateTime.UtcNow.Date)
.ToList();
✅ Use SQL.DatePart() for component-based filtering
// ✅ GOOD - Efficient database-level filtering
var accounts = Database.Query<Account>()
.Where(f => SQL.DatePart("year", f.CreatedOn) == 2024)
.ToList();
// ❌ BAD - Cannot use .Year property in queries
var accounts = Database.Query<Account>()
.Where(f => f.CreatedOn.Year == 2024) // This will not work
.ToList();
✅ Use DateTime.UtcNow for server-side time
// ✅ GOOD - Server timezone independent
var recentAccounts = Database.Query<Account>()
.Where(f => SQL.AsDate(f.CreatedOn) >= DateTime.UtcNow.Date.AddDays(-30))
.ToList();
✅ Use SQL arithmetic methods for relative dates
// ✅ GOOD - Database-level calculation
var expiring = Database.Query<Contract>()
.Where(f => SQL.AddMonths(f.EndDate, -3) <= DateTime.UtcNow)
.ToList();
// ❌ BAD - Cannot add/subtract in query
var expiring = Database.Query<Contract>()
.Where(f => f.EndDate.AddMonths(-3) <= DateTime.UtcNow) // This will not work
.ToList();
💡 Note: All SQL date helper methods (SQL.AsDate(), SQL.DatePart(), SQL.AddDays(), etc.) are executed at the database level for optimal performance. Standard .NET DateTime properties and methods cannot be used directly in LINQ queries.
⚠ Important:SQL.DatePart("weekday", ...) returns 1-7 where Sunday=1, Monday=2, ..., Saturday=7. This differs from .NET's DayOfWeek enum which starts at Sunday=0.
Multi-Select Picklist Filtering
Multi-select picklist fields store multiple selected values as a single semicolon-delimited string (e.g., "Value1;Value2;Value3"). Magentrix provides specialized methods SQL.Includes() and SQL.Excludes() for querying these fields with precise control over value combinations.
Understanding Multi-Select Picklist Storage
When a user selects multiple values in a picklist, they are stored internally as:
// Example stored value
Products__c = "Software;Hardware;Support"
// Empty picklist (no selections)
Products__c = null or ""
SQL.Includes - Match Any Combination
Use SQL.Includes() to find records where the multi-select field contains specific value combinations. Multiple parameters are evaluated with OR logic.
Basic Syntax:
SQL.Includes(field, "combination1", "combination2")
Single Value Match:
// Find accounts with "Software" selected
var accounts = Database.Query<Account>()
.Where(f => SQL.Includes(f.Products__c, "Software"))
.ToList();
// This matches:
// - "Software"
// - "Software;Hardware"
// - "Hardware;Software;Support"
// - Any combination containing "Software"
Multiple Value Combinations (OR Logic):
// Find accounts with either "Software" OR "Hardware" selected
var accounts = Database.Query<Account>()
.Where(f => SQL.Includes(f.Products__c, "Software", "Hardware"))
.ToList();
// This matches:
// - "Software"
// - "Hardware"
// - "Software;Support"
// - "Hardware;Consulting"
// - Any combination containing either value
Require Multiple Values Together (AND Logic within Combination):
// Find accounts with BOTH "Software" AND "Support" selected
var accounts = Database.Query<Account>()
.Where(f => SQL.Includes(f.Products__c, "Software;Support"))
.ToList();
// This matches:
// - "Software;Support"
// - "Support;Software"
// - "Software;Hardware;Support"
// - Any combination containing both values
Complex Combination Logic:
// Example: Grades field with possible values: AAA, BBB, CCC, DDD
// Find records with either (AAA AND CCC) OR (DDD)
var records = Database.Query<Student>()
.Where(f => SQL.Includes(f.Grades, "AAA;CCC", "DDD"))
.ToList();
// This matches:
// - "AAA;CCC" (has both AAA and CCC)
// - "AAA;BBB;CCC" (has both AAA and CCC, plus BBB)
// - "DDD" (has DDD)
// - "DDD;BBB" (has DDD, plus BBB)
// Does NOT match:
// - "AAA" (missing CCC)
// - "CCC" (missing AAA)
// - "BBB" (has neither combination)
Multiple Required Combinations:
// Find accounts with (Software AND Support) OR (Hardware AND Consulting)
var accounts = Database.Query<Account>()
.Where(f => SQL.Includes(
f.Products__c,
"Software;Support",
"Hardware;Consulting"
))
.ToList();
SQL.Excludes - Exclude Specific Values
Use SQL.Excludes() to find records that do NOT contain specific values. All parameters must be absent (AND logic).
Basic Syntax:
SQL.Excludes(field, "value1")
Exclude Single Value:
// Find accounts that do NOT have "Hardware" selected
var accounts = Database.Query<Account>()
.Where(f => SQL.Excludes(f.Products__c, "Hardware"))
.ToList();
// This matches:
// - "Software"
// - "Software;Support"
// - "Consulting;Training"
// - null or empty
// - Any combination without "Hardware"
// Does NOT match:
// - "Hardware"
// - "Software;Hardware"
// - Any combination containing "Hardware"
Exclude Multiple Values (AND Logic):
// Find accounts that have neither "Hardware" NOR "Consulting"
var accounts = Database.Query<Account>()
.Where(f => SQL.Excludes(f.Products__c, "Hardware;Consulting"))
.ToList();
// This matches only records that have NONE of the specified values
// - "Software"
// - "Software;Support"
// - "Training"
// - null or empty
// Does NOT match:
// - "Hardware" (contains excluded value)
// - "Consulting" (contains excluded value)
// - "Software;Hardware" (contains excluded value)
// - "Training;Consulting" (contains excluded value)
Example with Grades:
// Find students who do NOT have grade "BBB"
var students = Database.Query<Student>()
.Where(f => SQL.Excludes(f.Grades, "BBB"))
.ToList();
// This matches:
// - "AAA"
// - "CCC;DDD"
// - "AAA;CCC;DDD"
// - null or empty
// Does NOT match:
// - "BBB"
// - "AAA;BBB"
// - "BBB;CCC;DDD"
Combining SQL.Includes and SQL.Excludes
You can combine both methods to create complex filtering logic.
// Find accounts with Software but NOT Hardware
var accounts = Database.Query<Account>()
.Where(f =>
SQL.Includes(f.Products__c, "Software") &&
SQL.Excludes(f.Products__c, "Hardware")
)
.ToList();
// Find accounts with (Software AND Support) but NOT (Hardware OR Consulting)
var accounts = Database.Query<Account>()
.Where(f =>
SQL.Includes(f.Products__c, "Software;Support") &&
SQL.Excludes(f.Products__c, "Hardware", "Consulting")
)
.ToList();
// Complex: Must have Training, and either (Software OR Hardware), but NOT Consulting
var accounts = Database.Query<Account>()
.Where(f =>
SQL.Includes(f.Products__c, "Training") &&
SQL.Includes(f.Products__c, "Software", "Hardware") &&
SQL.Excludes(f.Products__c, "Consulting")
)
.ToList();
Handling Null and Empty Values
Null/Empty Records:
// Find accounts with no products selected OR with Software selected
var accounts = Database.Query<Account>()
.Where(f =>
string.IsNullOrEmpty(f.Products__c)
)
.ToList();
Common Use Cases
Product Filtering:
// Accounts buying software products with support
var accounts = Database.Query<Account>()
.Where(f => SQL.Includes(f.Products__c, "Software;Support"))
.ToList();
// Accounts NOT interested in hardware
var accounts = Database.Query<Account>()
.Where(f => SQL.Excludes(f.Products__c, "Hardware"))
.ToList();
Skills Matching:
// Find candidates with (C# AND SQL) OR (Java AND MySQL)
var candidates = Database.Query<Candidate>()
.Where(f => SQL.Includes(f.Skills__c, "C#;SQL", "Java;MySQL"))
.ToList();
// Exclude candidates with outdated skills
var candidates = Database.Query<Candidate>()
.Where(f => SQL.Excludes(f.Skills__c, "VB6"))
.ToList();
Certification Requirements:
// Partners with required certifications
var partners = Database.Query<Partner>()
.Where(f => SQL.Includes(f.Certifications__c, "Gold;Support"))
.ToList();
// Partners without expired certifications
var partners = Database.Query<Partner>()
.Where(f => SQL.Excludes(f.Certifications__c, "Expired"))
.ToList();
Best Practices
✅ Use semicolon for AND logic within SQL.Includes
// ✅ GOOD - Requires both values
var accounts = Database.Query<Account>()
.Where(f => SQL.Includes(f.Products__c, "Software;Support"))
.ToList();
// ❌ WRONG - Matches either value (OR logic)
var accounts = Database.Query<Account>()
.Where(f => SQL.Includes(f.Products__c, "Software", "Support"))
.ToList();
💡 Note:SQL.Includes() and SQL.Excludes() are specialized methods for multi-select picklists. Do not use regular string methods like .Contains() as they perform substring matching and may produce incorrect results.
Full-Text Search
Full-text search allows searching across multiple searchable fields using keywords.
Basic Full-Text Search:
// Search all searchable fields
var accounts = Database.Search<Account>("technology software")
.ToList();
// Search returns results ranked by relevance
foreach (var account in accounts)
{
SystemInfo.Debug($"Found: {account.Name}");
}
Search Specific Fields:
// Search only specified fields
var accounts = Database.Search<Account>(
"partner",
new List<string> { "Name", "Description" }
).ToList();
// Search multiple fields with keywords
var accounts = Database.Search<Account>(
"technology innovation",
new List<string> { "Name", "Industry", "Description" }
).ToList();
Search with Pagination:
// Paginate search results using Limit
var accounts = Database.Search<Account>(
"technology",
new List<string> { "Name", "Industry" }
)
.OrderBy(f => f.Name)
.Limit(25, 0) // Take 25, skip 0
.ToList();
// Second page
var accounts = Database.Search<Account>(
"technology",
new List<string> { "Name", "Industry" }
)
.OrderBy(f => f.Name)
.Limit(25, 25) // Take 25, skip 25
.ToList();
Search Count:
// Count search results without retrieving records
var count = Database.Search<Account>(
"technology",
new List<string> { "Name", "Industry" }
).Count();
SystemInfo.Debug($"Found {count} accounts matching 'technology'");
Search with Multiple Keywords:
// Multiple keywords (AND logic)
var accounts = Database.Search<Account>("technology software partner")
.ToList();
// Returns accounts containing all three keywords
// Phrase search
var accounts = Database.Search<Account>("\"partner program\"")
.ToList();
// Returns accounts containing exact phrase "partner program"
Search Best Practices:
// ✅ GOOD: Search specific fields when possible
var accounts = Database.Search<Account>(
"partner",
new List<string> { "Name", "Type" }
)
.Limit(50)
.ToList();
// ✅ GOOD: Combine search with filters
var accounts = Database.Search<Account>(
"technology",
"IsActive = @0 AND AnnualRevenue > @1",
new List<object> { true, 1000000 },
null,
new List<string> { "Name", "Industry" }
).ToList();
// ✅ GOOD: Use Limit for large result sets
var accounts = Database.Search<Account>("software")
.Limit(100)
.ToList();
Search Limitations:
💡 Searchable Fields Only: Full-text search only works on fields marked as searchable in the entity definition. Not all fields are searchable.
💡 Minimum Length: Search keywords typically require a minimum length (usually 3 characters).
💡 No Wildcards: Unlike LIKE queries, search does not support wildcard characters (% or _).
💡 Ranking: Results are automatically ranked by relevance. Use .OrderBy() to override the relevance ranking.