⚠️ IMPORTANT: This page contains the fundamental rules and usage details of the SmartWhere library. All developers must be familiar with these rules.
Let’s consider a project where there are books and their authors, all belonging to a publishing house.
Our entities for the database tables are as follows:
public class Publisher
{
public int Id { get; set; }
public string Name { get; set; }
public List<Book> Books { get; set; } = new();
}
public class Book
{
public int Id { get; set; }
public string Name { get; set; }
public int PublishedYear { get; set; }
public double Price { get; set; }
public DateTime CreatedDate { get; set; }
public Author Author { get; set; }
}
public class Author
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public Country Country { get; set; }
}
public class Country
{
public int Id { get; set; }
public string Name { get; set; }
}
Now, let’s examine how we can intelligently perform a Where operation when fetching data from these tables.
Let’s assume the name of our object is PublisherSearchRequest.
IWhereClause interfacepublic class PublisherSearchRequest : IWhereClause
WhereClause attributepublic class PublisherSearchRequest : IWhereClause
{
[WhereClause(PropertyName = "Id")]
public int? PublisherId { get; set; }
[WhereClause]
public string Name { get; set; }
[WhereClause("Books.Name")]
public string BookName { get; set; }
[WhereClause("Books.Author.Name")]
public string AuthorName { get; set; }
[WhereClause("Book.PublishedYear")]
public int? BookPublishedYear { get; set; }
[WhereClause("Book.Author.Age")]
public int? AuthorAge { get; set; }
[WhereClause("Book.Author.Country.Name")]
public string AuthorCountry { get; set; }
}
Of course, the fields in our entities and DTOs will have different names. Let’s examine how we can define them.
We mentioned that we should mark our properties with WhereClause. Let’s look at the methods to specify which entity field corresponds to the WhereClause property for indicating different field names.
If the property name is the same in both the entity and the DTO:
[WhereClause]
public int Id { get; set; }
If the property name is different in the entity and the DTO:
PropertyName should be the same as the property name in the entity:
[WhereClause(PropertyName = "Name")]
public string PublisherName { get; set; }
// Or short syntax:
[WhereClause("Name")]
public string PublisherName { get; set; }
Properties with WhereClause work with the ‘and’ operator by default in queries. We can set the LogicalOperator property for the ‘or’ operator:
[WhereClause(LogicalOperator.OR)]
public int Id { get; set; }
[WhereClause("Name", LogicalOperator.OR)]
public string PublisherName { get; set; }
[WhereClause(PropertyName = "Name", LogicalOperator = LogicalOperator.AND)]
public string PublisherName { get; set; }
With this attribute, we can move the properties from our request to a class in such a way that they are only used in the where conditions.
You can examine the usage with the following example:
public class BookSearchRequest : IWhereClause
{
public int Start { get; set; }
public int Max { get; set; }
[WhereClauseClass]
public BookSearchDto SearchData { get; set; }
}
By using the WhereClauseClass, we can separate the fields to be used for searching from the necessary parameters for the business logic.
public class BookSearchDto
{
[TextualWhereClause(StringMethod.StartsWith)]
public string Name { get; set; }
[TextualWhereClause("Author.Name", StringMethod.EndsWith)]
public string AuthorName { get; set; }
}
SmartWhere has 2 additional attributes derived from WhereClause: TextualWhereClause and ComparativeWhereClause.
These attributes allow us to perform detailed searches in textual fields. For example, they enable us to search for text that includes the specified content, text that starts with this content, or text that ends with this content, and vice versa.
Supported String methods:
public enum StringMethod
{
Contains, // Contains
NotContains, // Does not contain
StartsWith, // Starts with
NotStartsWith, // Does not start with
EndsWith, // Ends with
NotEndsWith // Does not end with
}
You can examine the usage with the following example:
public class BookSearchDto : IWhereClause
{
[TextualWhereClause(StringMethod.StartsWith)]
public string Name { get; set; }
[TextualWhereClause("Author.Name", StringMethod.EndsWith)]
public string AuthorName { get; set; }
}
public class TextualSearchRequest : IWhereClause
{
[TextualWhereClause(StringMethod.Contains)]
public string Name { get; set; }
[TextualWhereClause("Name", StringMethod.NotContains)]
public string BookName { get; set; }
[TextualWhereClause("Author.Name", StringMethod.NotStartsWith)]
public string AuthorName { get; set; }
[TextualWhereClause("Author.Country.Name", StringMethod.NotEndsWith)]
public string CountryName { get; set; }
}
This attribute allows for detailed searches in comparable fields. For example, it enables searching in data types that are compatible with comparison operators such as greater than, less than, greater than or equal to, and less than or equal to, and vice versa. It supports various data types like Integer, Decimal, Datetime, and many others.
Supported comparison operators:
public enum ComparisonOperator
{
Equal, // Equal to
NotEqual, // Not equal to
GreaterThan, // Greater than
NotGreaterThan, // Not greater than
GreaterThanOrEqual, // Greater than or equal
NotGreaterThanOrEqual, // Not greater than or equal
LessThan, // Less than
NotLessThan, // Not less than
LessThanOrEqual, // Less than or equal
NotLessThanOrEqual // Not less than or equal
}
You can examine the usage with the following example:
public class ComparativeSearchRequest : IWhereClause
{
[ComparativeWhereClause("Author.Age", ComparisonOperator.GreaterThan)]
public int? AuthorAge { get; set; }
[ComparativeWhereClause("PublishedYear", ComparisonOperator.GreaterThanOrEqual)]
public int? PublishedStartYear { get; set; }
[ComparativeWhereClause("PublishedYear", ComparisonOperator.LessThanOrEqual)]
public int? PublishedEndYear { get; set; }
[ComparativeWhereClause("Price", ComparisonOperator.LessThan)]
public decimal? Price { get; set; }
[ComparativeWhereClause("Price", ComparisonOperator.GreaterThanOrEqual)]
public decimal? StartPrice { get; set; }
[ComparativeWhereClause("Price", ComparisonOperator.LessThanOrEqual)]
public decimal? EndPrice { get; set; }
[ComparativeWhereClause("CreatedDate", ComparisonOperator.LessThan)]
public DateTime? BookCreatedDate { get; set; }
[ComparativeWhereClause("CreatedDate", ComparisonOperator.GreaterThanOrEqual)]
public DateTime? StartCreatedDate { get; set; }
[ComparativeWhereClause("CreatedDate", ComparisonOperator.LessThanOrEqual)]
public DateTime? EndCreatedDate { get; set; }
}
TextualWhereClause and ComparativeWhereClause, being derived from WhereClause, encompass all of its features and rules.
If the field we want to query is located in sub-tables, in this case, we have rules for defining Name for WhereClause.
When we want to query for Id and Name from the Publisher table, we make the definitions as explained in the examples above.
public class Publisher
{
public int Id { get; set; }
public string Name { get; set; }
public List<Book> Books { get; set; } = new();
}
public class Book
{
public int Id { get; set; }
public string Name { get; set; }
public int PublishedYear { get; set; }
public double Price { get; set; }
public DateTime CreatedDate { get; set; }
public Author Author { get; set; }
}
public class Author
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public Country Country { get; set; }
}
public class Country
{
public int Id { get; set; }
public string Name { get; set; }
}
If we want to query based on the Name field within the Books list under Publisher:
[WhereClause("Book.Name")]
Here, in the 'Book.Name' definition, ‘Book’ can be the name of an Entity or a Property, for example, 'Books.Name'.
[WhereClause("Books.Name")]
Similarly, if we want to query based on a property in the Author object under Book:
[WhereClause("Book.Author.Name")]
Note: In the previous version (1.0.), it was possible to query entities up to the third level within an entity. In this version (2.0.), I believe this limitation has been overcome, as I have had the opportunity to test queries up to the fourth level. For levels beyond that, it is currently uncertain, but there is hope that it will work. Efforts are ongoing at full speed to clarify this situation.
Last Updated: 2025-01-16
Version: 2.2.3