Contents tagged with LINQ
A long, long time ago, I wrote a NHibernateDataSource control. Back then, it was based in the first LINQ provider for NHibernate, and a long has happened since. Now, I decided to give it another go!
Historically, in ASP.NET, a data control should inherit from DataSourceControl, like ObjectDataSource, LinqDataSource, EntityDataSource, SqlDataSource, etc, and should expose collections for parameters for each of the supported operations (select, update, delete and insert). Since ASP.NET 4, however, a new base class came along: QueryableDataSource. This class is an implementation of IQueryableDataSource, which allows using QueryExtender, also introduced in version 4, to filter and sort the results of a data source that uses LINQ.
I wanted my control to be able to use QueryExtender, but I also wanted to be able to give it an HQL query. It should also be capable of inserting, updating and deleting entities.
So, here’s what I came up with, first, the NHibernateDataSource class:
You can see that it exposes some events:
- Configure: gives developers a chance to build (or return an existing) Configuration instance, that will be used for building the session factory;
- BuildSessionFactory: allows setting parameters on the default session factory or returning an existing one;
- CreateInstance: raised before NHibernate creates a default instance, to allow developers to return one;
- EntityInserting: raised before an entity is inserted, allowing developers to cancel the operations or to set entity parameter;
- EntityUpdating: raised before an entity is updated, allowing developers to cancel the operations or to set entity parameter;
- EntityDeleting: raised before an entity is deleting, allowing its cancellation;
- EntitiesSelecting: raised before a select operation is performed;
- EntityInserted: raised after an entity was inserted;
- EntityUpdated: raised after an entity was updated;
- EntityDeleted: raised after an entity was deleted;
- EntitiesSelected: raised after a select operation was performed;
- OperationCompleted: raised after an operation completes (select, insert, update or delete).
If no handler for CreateInstance is supplied, NHibernateDataSource will try to create an entity using Activator.CreateInstance.
EntitySelecting is raised regardless of the Mode (Hql or Linq), but it will have different values in its argument: a query string plus parameters in the case of Hql and an IQueryable instance for Linq.
EntityInserting, EntityUpdating and EntityDeleting allow the modification of properties of the entity in the parameter, but not the replacing of the entity itself.
OperationCompleted is always called, except in the event of an exception.
It also exposes a couple of properties:
- Mode: one of the two operation modes, Hql or Linq. If Hql is used, then the Hql property must be set; otherwise, it’s EntityName that is required;
- Hql: an NHibernate HQL query string;
- EntityName: the name of an entity that the control will work with; only required for Mode Linq or for inserts, updates or deletes;
- RefreshBeforeUpdate: whether NHibernate should refresh the properties of an entity before updating or deleting it;
- MaximumRecords: the optional maximum number of records to retrieve, if paging is not used (PageSize and PageIndex);
- PageIndex: the page index to retrieve;
- PageSize: the page size to retrieve;
- SessionFactory: a session factory that will be used instead of a default created one;
- SelectParameters: a collection of parameters to be applied to the Hql string;
- InsertParameters: a collection of parameters for the insert operation;
- UpdateParameters: a collection of parameters for the update operation;
- DeleteParameters: a collection of parameters for the delete operation.
And, of course, exposes the basic operations: select is the default, but Insert, Update and Delete methods are available.
NHibernateDataSource will check if the SessionFactory property is set, otherwise, it will build its own Configuration instance and raise the Configure and BuildSessionFactory events. The generated session factory is then stored in the InternalSessionFactory static property for caching.
Then, the NHibernateDataSourceView, which is the responsible for the actual querying, inheriting from QueryableDataSourceView:
And the NHibernateDataSourceMode:
Finally, all of the event arguments:
Now, let’s see concrete examples of its usage. First, using Mode Hql:
You can see that the Hql property has a parameter, price, which is bound to a parameter in SelectParameters with the same name. Each parameter is an instance of the Parameter class, here I am using a parameter with a static value (Parameter) and another that takes a value from the query string (QueryStringParameter), but others exist. To help with NHibernate insert and update operations, I created a new Parameter class, EntityParameter, that knows how to retrieve a en entity or a proxy to an entity:
As for Mode Linq, an example using a QueryExtender is in order:
The LINQ query produced by the NHibernateDataSource is intercepted by the QueryExtender and a where (SearchExpression) and a order by (OrderByExpression) clauses are added to it. Other expressions can be used, inheriting from DataSourceExpression, and some take parameters of type Parameter. Do notice that filtering and sorting is performed server-side, not client-side.
Of course, this can certainly be improved, let me hear your thoughts and questions.
And that’s it. Enjoy!
Like I said in my previous post, it’s not enough to add a DbFunctionAttribute attribute to a method to have it call a database function. If the function is not a built-in one, it will not be registered in the Entity Framework provider manifest for SQL Server, so it will require registration in the model. You might think, just by looking at the methods in SqlFunctions that all that it took was to add this attribute, but you would be wrong.
It is possible to register custom functions that exist in the database so that they can be called by Entity Framework Code First LINQ queries.
If we want this method to be callable by a LINQ query, we need to add the DbFunctionAttribute to it, specifying the name of the database function we wish to call, because the .NET method and the database function names can be different:
And for calling it:
However, for certain database functions, it requires a bit more work to get done. Let us consider now the FORMAT function and a .NET implementation:
Besides adding the DbFunctionAttribute attribute:
it also requires that we register it explicitly in our model, for that, we override the OnModelCreating method and add a custom convention:
The convention being:
Now we have the FORMAT function available to LINQ:
Now, I hear you ask: why for SOUNDEX we just need to add a simple attribute and for FORMAT we need so much more? Well, it just happens that SOUNDEX is defined in the Entity Framework SQL Server provider manifest - see it here. All of the functions in SqlFunctions are present in the manifest, but the opposite is not true - not all functions in the manifest are in SqlFunctions, but that's the way it is! Thanks to @divega for the explanation.
Some things worth mentioning:
You cannot specify two functions with the same name and different parameters.
There is an open request to add the FORMAT function to the list of functions supported out of the box by Entity Framework Code First: https://entityframework.codeplex.com/workitem/2586 through the SqlFunctions class, but in the meantime, this might be useful!
In case you ever want to have a look at the generated SQL before it is actually executed, you can use this extension method:
Parameters such as 100 will be located in the nhLinqExpression.ParameterValuesByName collection.
This is a common request, and really makes sense; we need to use LINQ expressions and a bit of reflection magic. First, an helper function for returning an expression that points to a member:
Then, we call it on the DbContext.OnModelCreating method, as a parameter to StructuralTypeConfiguration<TStructuralType>.Property:And that’s it!
Some days ago I wrote a post on comparing LINQ expressions where I shared my discoveries and general discontent on how difficult it is do right. The reason I was looking into it was because I wanted to write a LINQ query caching mechanism.
There are actually three main problems involved:
Comparing two LINQ expressions;
Caching a LINQ expression for some time;
For cached instances, preventing them from executing when they are enumerated after the first time.
Comparing LINQ Expressions
I ended up writing my own comparer, which involved looking at all possible types of Expressions (around 20 classes). For some it is quite easy, because they have few relevant properties – take ConstantExpression, for instance, I only had to consider the Value property – while for others there are a few – BinaryExpression has Left, Right, Conversion and Method. OK, so here is the code:
This implementation disregards the lambda variable name, so that “x => …” is equal to “y => …”. Also, as you can see, this is not safe for multithreaded usage, because it uses an accumulator field (hashCode), where the hash for the expression currently being calculated is stored. Being an IEqualityComparer<Expression>, it implements both Equals and GetHashCode methods.
As you can see, we are free to supply out own ObjectCache implementation, provided we place a IServiceProvider implementation (UnityServiceLocator will do) on the ObjectCache.Host property and this implementation returns a valid ObjectCache instance. Feel free to replace this by any other similar mechanism!
Preventing Multiple Query Executions
So, when an IQueryable<T> is first executed, it will go to the database, or someplace else (just think WCF Data Services’ DataServiceQuery<T>), and return its results. If we are going to put that query in a cache, we want to prevent it from executing multiple times, otherwise the purpose of the cache would be defaced. For that, I built my own class that just inherits from IQueryable<T> (actually, from IOrderedQueryable<T>, for support of ordered queries) and overrides the IEnumerable<T> (of which IQueryable<T> descends) GetEnumerator method:
Putting It All Together
This allows me to write code as this:
By calling the AsCacheable extension method, our LINQ queries get cached for the specified duration. This will work with any LINQ implementation.
Remember those old posts on Dynamic LINQ? You are probably aware that Microsoft has made its implementation available as a Nuget package, but, like I said, you already have it in your machine, hidden inside the System.Web.Extensions assembly.
In order to make it easier to use, I wrote a simple extension method that works with plain old IQueryable<T>. And here it is:
It even supports parameters! Just two simple examples – I am using Entity Framework, but you can use whatever you like, this is totally generic:
To make it clear, all parameters must be indicated as @0, @1, and so on. It is better to use them, because the database engine can reuse the execution plan.
There’s one limitation, though: you can’t compare each value on its own, you have to specify one of its properties. For example, you can’t have:
The @it parameter is not recognized, which is a pity.
Make sure you have references to both System.Web and System.Web.Extensions, this is required, and won’t affect anything.
As always, glad to be of service!
I recently came upon this problem: how to calculate a hash from a LINQ expression so that I can safely compare two expressions for equality? I had naively assumed that the Expression class – and their descendants – would have implemented GetHashCode in an appropriate way, so as to make developer’s lifes easier, but unfortunately Microsoft thought otherwise.
After looking it up on the Internet, I could see two “solutions”:
Convert the Expression to its string representation and get the hash code of it;
Use an ExpressionVisitor to visit all contained expressions and calculate their individual hash – if this seems recursive to you, that’s because it is!
Comparing the String Representation
The first “solution” doesn’t actually work, because two expressions might represent exactly the same and yet have different string representations. For example, consider:
The only thing that differentiates these two expressions is the name of the lambda parameter, unfortunately it causes their string representations to be different.
One possible solution might be to use a regular expression to get all occurrences of lambda expressions, capture the name of the lambda variables, and then do a search and replace for some well known name:
At first sight – at least, for me! – this seemed to work, however, the replacement pattern – “get me all words composed of only the lambda variable” - might match something that it wasn’t supposed to, for instance:
I might use a different replacement regular expression, for example, I could check for “all lambda variables followed by a dot (.)”:
But this wouldn’t get code like this:
To call it off, I think it might be possible, but it is more complicated than it seems.
Using an ExpressionVisitor
The next approach involves using an ExpressionVisitor to visit all expressions contained in an Expression, something that I have talked before. The thing here is, an Expression is typically composed of several other expressions, of various kinds, so appropriate code must be used for each in order to get an univocal hash out of it. For example, for a ConstantExpression, we might consider its NodeType, Type and Value properties. The problem is, there are more than 20 Expression subclasses, and we need to do this for each.
To cut things short, suppose we have implemented an hash code extractor for each Expression kind. We can’t simply have a big hash code by adding all hash codes calculated for each Expression fragment, because the order by which they appear is also important:
So, each individual hash code must be take in order, and then we must get the hash code of the entire list of hash codes. Uff!
This problem exists since there are LINQ expressions, and, of course, other folks have come up with solutions. The NHibernate project has one, probably LINQ to SQL and Entity Framework have too, just to name those more close to me. However, I don’t think any of these solutions might be ready for external, general purpose usage outside their source projects. It would be great to have one such library that we could pick up and use, but I haven’t so far found any. Might be something to work on, though.
What are your thoughts?
A common request when working with LINQ queries (Entity Framework, NHibernate, etc) is the ability to intercept them, that is, inspect an existing query and possibly modify something in it. This is not extremely difficult to do “by hand”, but Microsoft has a nice class called ExpressionVisitor which makes the job easier. It basically has virtual methods that get called whenever the class visits each expression contained in a greater expression, which may come from a query (the IQueryable interface exposes the underlying Expression in its Expression property). The virtual methods even allow returning a replacement for each expression found, the only problem is that you must subclass ExpressionVisitor to make even the slightest change, so I wrote my own class, which exposes all node traversal as events, one event for each kind of expression, where you can return an alternative expression, thus changing the original query. Here is the code for it:
Yes, I know, I probably should have used properties instead of events, but that’s really not important.
A simple example might be: