Introduction
Views in databases are brilliant. It allows for a layer of abstraction over the actual data model and to allow a set of search queries which are used a lot to be reused very quickly. While the Entity Framework has support for using Views, sometimes you want to be able to write the view in the code itself. This can be especially useful when the code does some form of encryption on the data (in case you can't use the database encryption).
This article describes how to setup a basic view with conditional queries.
Background
This article assumes basic knowledge of the Entity Framework and how to run basic queries.
It will also be useful to have a quick look at the brilliant LinqKit (https://github.com/scottksmith95/LINQKit) as this is used for "Or" statements inside the full project.
Knowledge of Expressions is also highly recommended.
Using the Code
The biggest difficulty to writing the Views are the condition. After all, a view is nothing more than:
var query = context.Students.Include(s=>s.Courses.Select(c=>c.Course));
Luckily, the System.Linq.Expression class comes into play here and this provides almost all the logic we need. Using this, it brings us to the following as a base for creating a condition:
var result = Expression.Lambda<Func<T, bool>>
(Expression.LessThan(selector.Body, Expression.Constant(value)), selector.Parameters);
selector.body is the selector of a specific property of an Object. e.g. s=> s.Namevalue is the value we are searching for.selector.Parameters are any parameters in case a parameter Expression is used. We are not using that explicitly for the purpose of this article. (It might be used underneath by the Expression class though).
For numbers and booleans, this is all well and good, but this doesn't really work for string or for DateTime values.
For DateTime values, you need to do some trickery as there is more data in the DateTime than just the Date (as the name implies). As such:
DateTime s1 = DateTime.Now; DateTime s2 = DateTime.Now.AddMilliseconds(1);
s1 == s2 => false;
As DateTime comparisons are on the ticks level, a comparison almost always fails (especially as they come from a Website usually where a user has filled it in).
The best way around this is to do the following:
Expression<Func<T, bool>> result = Expression.Lambda<Func<T, bool>>
(Expression.GreaterThanOrEqual(selector.Body, Expression.Constant(value)), selector.Parameters);
DateTime d2 = ((DateTime)(object)value).AddDays(1);
result = result.And(Expression.Lambda<Func<T, bool>>
(Expression.LessThan(selector.Body, Expression.Constant(d2)), selector.Parameters));
This will do an "IsEqual" check for the DateTime value (with the given value) by making it a check between Today and Tomorrow (where today is the date value given). Of course, this example is useful if a comparison is to be done on Date level, if the time comes into play as well you may want to enhance this logic.
For string, this is even more complicated as a SmallerThen and GreaterThen for a string doesn't really work. For example, the following:
string s1 = "J";
string s2 = "ABC";
Which is smaller? Also s1< s2 doesn't work at all.
As such, we should add some additional logic for strings:
if (typeof(DataType) == typeof(string))
{
result = Expression.Lambda<Func<T, bool>>(
Expression.LessThanOrEqual(
Expression.Call(
selector.Body, typeof(string).GetMethod("CompareTo", new[] { typeof(string) }),
Expression.Constant(value))
, Expression.Constant(0, typeof(int))
)
, selector.Parameters);
}
This allows the Expression to call the CompareTo method (even when accessing the database). We have to do the same for the GreaterThan and the OrEqual variants as well.
Putting this together, you get the following function for building a condition:
public Expression<Func<T, bool>> BuildCondition<T, DataType>
(Expression<Func<T, DataType>> selector, DataType value, ComparisonTypes comparison,
params Expression<Func<T, bool>>[] expressions)
{
Expression<Func<T, bool>> result = null;
switch (comparison)
{
case ComparisonTypes.Equals:
default:
if (typeof(DataType) == typeof(DateTime))
{
result = Expression.Lambda<Func<T, bool>>
(Expression.GreaterThanOrEqual(selector.Body,
Expression.Constant(value)), selector.Parameters);
DateTime d2 = ((DateTime)(object)value).AddDays(1);
result = result.And(Expression.Lambda<Func<T, bool>>
(Expression.LessThan(selector.Body, Expression.Constant(d2)),
selector.Parameters));
}
else
{
result = Expression.Lambda<Func<T, bool>>
(Expression.Equal(selector.Body, Expression.Constant(value)),
selector.Parameters);
}
break;
case ComparisonTypes.NotEquals:
if (typeof(DataType) == typeof(DateTime))
{
result = Expression.Lambda<Func<T, bool>>(Expression.LessThan
(selector.Body, Expression.Constant(value)), selector.Parameters);
DateTime d2 = ((DateTime)(object)value).AddDays(1);
result = result.Or(Expression.Lambda<Func<T, bool>>
(Expression.GreaterThanOrEqual(selector.Body, Expression.Constant(d2)),
selector.Parameters));
}
else
{
result = Expression.Lambda<Func<T, bool>>(Expression.NotEqual
(selector.Body, Expression.Constant(value)), selector.Parameters);
}
break;
case ComparisonTypes.SmallerThan:
if (typeof(DataType) == typeof(string))
{
result = Expression.Lambda<Func<T, bool>>(
Expression.LessThan(
Expression.Call(
selector.Body, typeof(string).GetMethod
("CompareTo", new[] { typeof(string) }),
Expression.Constant(value))
, Expression.Constant(0, typeof(int))
)
, selector.Parameters);
}
else
{
result = Expression.Lambda<Func<T, bool>>(Expression.LessThan
(selector.Body, Expression.Constant(value)), selector.Parameters);
}
break;
case ComparisonTypes.SmallerOrEquals:
if (typeof(DataType) == typeof(DateTime))
{
DateTime d2 = ((DateTime)(object)value).AddDays(1);
result = Expression.Lambda<Func<T, bool>>(Expression.LessThan
(selector.Body, Expression.Constant(d2)), selector.Parameters);
}
else if (typeof(DataType) == typeof(string))
{
result = Expression.Lambda<Func<T, bool>>(
Expression.LessThanOrEqual(
Expression.Call(
selector.Body, typeof(string).GetMethod
("CompareTo", new[] { typeof(string) }),
Expression.Constant(value))
, Expression.Constant(0, typeof(int))
)
, selector.Parameters);
}
else
{
result = Expression.Lambda<Func<T, bool>>(Expression.LessThanOrEqual
(selector.Body, Expression.Constant(value)), selector.Parameters);
}
break;
case ComparisonTypes.GreaterThan:
if (typeof(DataType) == typeof(DateTime))
{
DateTime d2 = ((DateTime)(object)value).AddDays(1);
result = Expression.Lambda<Func<T, bool>>(Expression.GreaterThanOrEqual
(selector.Body, Expression.Constant(d2)), selector.Parameters);
}
else if (typeof(DataType) == typeof(string))
{
result = Expression.Lambda<Func<T, bool>>(
Expression.GreaterThan(
Expression.Call(
selector.Body, typeof(string).GetMethod("CompareTo", new[]
{ typeof(string) }),
Expression.Constant(value))
, Expression.Constant(0, typeof(int))
)
, selector.Parameters);
}
else
{
result = Expression.Lambda<Func<T, bool>>(Expression.GreaterThan
(selector.Body, Expression.Constant(value)), selector.Parameters);
}
break;
case ComparisonTypes.GreaterOrEquals:
if (typeof(DataType) == typeof(string))
{
result = Expression.Lambda<Func<T, bool>>(
Expression.GreaterThanOrEqual(
Expression.Call(
selector.Body, typeof(string).GetMethod("CompareTo",
new[] { typeof(string) }),
Expression.Constant(value))
, Expression.Constant(0, typeof(int))
)
, selector.Parameters);
}
else
{
result = Expression.Lambda<Func<T, bool>>(Expression.GreaterThanOrEqual
(selector.Body, Expression.Constant(value)), selector.Parameters);
}
break;
case ComparisonTypes.Like:
result = Expression.Lambda<Func<T, bool>>(Expression.Call
(selector.Body, "Contains", null,
Expression.Constant(value)), selector.Parameters);
break;
}
if (expressions != null)
{
foreach (var exp in expressions) result = result.And(exp);
}
return result.Expand();
}
This allows for building virtually any condition you want and should cover most (if not all) cases.
You can use this as follows:
this.BuildCondition<Student, string>(s => s.Name, this.Value, this.Comparison);
Putting this together (using the brilliant linqkit), you can use:
var query = context.Students.Include(s=>s.Courses.Select(c=>c.Course));
var condition = this.BuildCondition<Student, string>(s => s.Name, this.Value, this.Comparison);
var pr = PredicateBuilder.New<T>();
pr.And(condition);
query = query.AsExpandable().Where(pr.Expand());
var result = query.ToList();
There you have it, a full working view with conditions. Well, there is a bit more to it to make it reusable for your setup. Please see the attached project for more information and a fully working example. The DB is build in the configuration, so just run "update-database" from the Package Manager Console in Visual Studio.
Points of Interest
The very annoying part is that Linq doesn't support "Or" statements. Luckily, LinqKit provides a way around that. Other than that, this provides a good start for creating your own code based views with the Entity Framework. That said, it´s recommended to use normal Views whenever possible as that is much easier to optimize for performance.
History
- First version with a basic description of how to make the conditions
- Made links to websites actual links
- Updated link to Expression to point to the class rather than the concept
- Some small text enhancements, addition of proper
<code> tags - Added link to source project.
