I've been recently working on a nifty project in wich the primary purpose is to search for media data in the database. It's a normal ASP.NET application, based on the 3.5 version of the .NET framework - nothing new here. The great news is the Linq to Entities platform I'm building it on. This is indeed a very usefull technology and this is my first project using Linq to Entities.
This is the scenario: We have objects, and objects have properties. The goal of this article is to be able to search the storage for objects by using a global text search affecting their properties. I will give this article a slow start, trying to explain the general methodology of creating entity objects and, generally, our EDM (Entity Data Model).
At this point, I'm working on the SP1 Beta of Visual Studio, wich includes .NET Framework 3.5 SP1 Beta and ADO.NET Entity framework. You can get it here. The included .NET Framework 3.5 Service Pack 1 adds many new features and fixes, including the following:
- .NET Framework Client Release (“Arrowhead”)
- ASP.NET Dynamic Data
- ASP.NET Routing
- ADO.NET Data Services
- ADO.NET Entity Framework
After generating my EDM, from the ADO.NET Entities designer (selected Add -> New Item... -> ADO.NET Entity Data Model, and selected my database objects), my EntityContext was created, containing all my objects, relational attributes, and QueryProperties. Everything went well. We are now able to start querying our store for data.
That said, my goal is to do a generic search over a metadata object called MetaObj. But here's the catch: I don't want a explicit method with object properties using Linq query code like this:
375 IQueryable<MyObject> list =
376 from e in entities
377 where e.Property1.ToUpper().Contains(someText) ||
378 e.Property2.ToUpper().Contains(someText) ||
379 e.Property3.ToUpper().Contains(someText) ||
380 ...
381 select e;
This way, I get stuck with MyObject and its properties. Instead, I want to be able to dinamically generate a Linq text search query based on any object's properties. The solution to this is using a bit of reflection to get the object's characteristics,query them using Expression trees to map my restrictions, and then apply them to my Linq object query.
As you know, Linq was a major inovation in c# 3.0. Its purpose is to improve the way data is manipulated and represented and at the same time facilitate the development procedures and bridge the gap between data fetching and its consumption in many architectural practices. As an example, the following code:
18 SqlConnection conn = new SqlConnection("...");
19 conn.Open();
20
21 SqlCommand cmd = new SqlCommand(@"SELECT p.ProductID, p.ProductName FROM Products p WHERE p.ProductName = @productName", conn);
22
23 cmd.Parameters.AddWithValue("@productName", "XPTO");
24
25 SqlDataReader dr = cmd.ExecuteReader();
26
27 while (dr.Read())
28 {
29 string ID = dr.GetString(0);
30 string ProductName = dr.GetString(1);
31 }
32 dr.Close();
Is now replaces by this:
18 var db = new MyDataContext();
19
20 var myImpliciyObject = from p in db.Products
21 where (p.ProductName == "XPTO")
22 select new { ID = p.ProductID, Name = p.ProductName };
23
24 string ID = myImpliciyObject.ID;
25 string ProductName = myImpliciyObject.Name;
What .NET compiler is doing behind the scenes is to transform this code into an intermediate language representation (IL), which will be rewrited into something like this:
29 var myImpliciyObject = db.Products.Where(p => p.ProductName = "XPTO").Select(p => new { ID = p.ProductID, Name = p.ProductName });
... which is nothing more than two expressions, with their input parameters on the left and expression on the right. This approach is called Method based query, and uses the Where and Select query operators. Their arguments are called Lambda Expressions. A lambda expression is an anonymous function that can contain expressions and statements, and can be used to create delegates or expression tree types. From here, the compiler gets rid of the lamba expressions (they will become anonymous methods) and the anymous type syntax (that is converted into constructor call of an auto-generated class with two properties).
With this type of method construction, we can specify an abstract query to any relational data repositories that can later be translated into their propper domain-specific syntax. Take SQL for instance. The last piece of code would be rendered as:
SELECT p.ProductID, p.ProductName FROM Products AS p WHERE p.ProductName = 'XPTO'
This is where Expression Trees come in handy. An expression tree as the name implies, is a branched relationship between expressions. Each expression can be, for instance, a simple calculation operation, a property member access or a conditional expression.
So, in order to resolve this challenge, lets get started by setting our EntityContext and get our EntityObject's properties list...
1 PropertyInfo[] entityProperties = typeof(MyObject).GetProperties();
2 ObjectQuery<MyObject> entities = MyEntities.MyObject;
...and since our expressions will access our object's properties, we must define our object as a parameter. So, we will declare and initialize a ParameterExpression variable with MyObject as argument:
3 // Define input parameter as MyObject
4 ParameterExpression param1 = Expression.Parameter(typeof(MyObject), typeof(MyObject).Name);
...and since our expressions will access our object's properties, we must define our object as a parameter. So, we will declare and initialize a ParameterExpression variable with MyObject as argument:
5 // Define input parameter as MyObject
6 ParameterExpression param1 = Expression.Parameter(typeof(MyObject), typeof(MyObject).Name);
Next we define List<> collection object that will hold our expressions during the iteration, and general expression objects to hold the member property value call and value comparison:
7 List<Expression<Func<MyObject, bool>>> listExpressions = new List<Expression<Func<MyObject,bool>>>();
14 Expression callExpr;
15 Expression valueExpr;
16 Expression exp = null;
17
18 Expression<Func<MyObject, bool>> lambdaExp = null;
19 Expression<Func<MyObject, bool>> finalExp = null;
During property iteration we will set the callExpr Expression as an access to a property value, and then "join it" with the value expression by calling the Contains() method of string data type. Notice that for each data type we can have different treatments. This is because we want to be able to compare both text and numeric values against our properties. In case of string values we will use Expression.Call to create a MethodCallExpression that represents a call to an instance method by calling the appropriate factory method. In our case, we need it to invoke the Contains() method of the string data type witch, by the way, is later translated into its sql-domain-specific restriction clause, like:
CAST(CHARINDEX(UPPER(@p__linq__1), UPPER([Extent1].[PropertyName])) AS int)) > 0',N'@p__linq__1 nvarchar(1)',@p__linq__1=N'<search_string_to_compare>'
(The @p__linq__1, in bold, is our property value that is to be compared with user value)
As for other property data types we just use the Expression.Equal to create a BinaryExpression that represents an equality comparison:
1 foreach (PropertyInfo prop in entityProperties)
2 {
3 // Reset valid property
4 valid = false;
5
6 // Reset expression
7 exp = null;
8
9 // Left expression will check a MyObject property value
10 // We could also use Expression.Property here
11 callExpr = Expression.MakeMemberAccess(param1, prop);
12
13 Type t = prop.PropertyType;
14
15 if (t == typeof(string))
16 {
17 // Property value will be comprared to the input value
18 valueExpr = Expression.Constant(value);
19 exp = Expression.Call(callExpr, typeof(string).GetMethod("Contains", new Type[] { typeof(string) }), valueExpr);
20
21 valid = true;
22 }
23 else if (t == typeof(int) || t == typeof(Nullable<int>) ||
24 t == typeof(long) || t == typeof(Nullable<long>) ||
25 t == typeof(short) || t == typeof(Nullable<short>))
26 {
27 // Check if input data type matches
28 object num = null;
29 long temp;
30 if (long.TryParse(value, out temp))
31 num = temp;
32
33 if (num != null)
34 {
35 valueExpr = Expression.Constant(num, t);
36 exp = Expression.Equal(callExpr, valueExpr);
37 valid = true;
38 }
39 }
40 else if (t == typeof(DateTime) || t == typeof(Nullable<DateTime>))
41 {
42 DateTime date;
43 if (DateTime.TryParse(value, out date))
44 {
45 valueExpr = Expression.Constant(date, t);
46 exp = Expression.Equal(callExpr, valueExpr);
47 valid = true;
48 }
49 }
50 else if (t == typeof(bool) || t == typeof(Nullable<bool>))
51 {
52 bool val;
53 if (bool.TryParse(value, out val))
54 {
55 valueExpr = Expression.Constant(val, t);
56 exp = Expression.Equal(callExpr, valueExpr);
57 valid = true;
58 }
59 }
60
61 if (valid)
62 {
63 lambdaExp = Expression.Lambda<Func<MyObject, bool>>(exp, param1);
64 listExpressions.Add(lambdaExp);
65 }
66 }
In the end, our listExpression Expression list will hole all the Expressions we need to query all available properties. The final step is to "glue" them together using a bitwise OR operation:
226 // Add up all conditions using an OR expression type
227 foreach (Expression<Func<Entidade, bool>> e in listExpressions)
228 {
229 if (finalExp == null)
230 finalExp = e;
231 else
232 finalExp = Expression.Lambda<Func<Entidade, bool>>(Expression.Or(finalExp.Body, e.Body), param1);
233 }
234
235 // Define query
236 IQueryable<MyObject> listOfMyObjects = entities.Where(finalExp);
And that's it. With this you can query all object properties for a given value whether it's text or numeric. It's a great way to search for object values without writing specific code against them.