-
-
I have a SQL2005 database based on more or less a relational model, except for one column which is of type Xml. The problem with most ORMappers is that they are unaware of the SQL2005 specific (or even Oracle-specific for that matter) features for querying and updating Xml-typed data. ORMappers look at every columns as being a property/field of your domain-object. Good enough, that's what they are for.
But if you need to retreive a domain-object where some property should be matched against an XQuery predicate... you kind of have a problem. Here is one solution I found for this problem:
I am using Paul Wilson's excellent ORMapper with out-of-the-box support for .NET 2.0 features (I can't live without generics). This ORMapper has a specific feature called "Interceptors" (I know that NHibernate also has a feature which basicly does the same). By implementing the IInterceptCommand I can create a plugin that gets called before the command gets sent to the database.
Here is my code to rewrite the Sql statement in the form "where PropertyBag = @Something" to "PropertyBag.value(... @Something)". Keep in mind that this is more or less a sample and won't work for every scenario (especially if you have queries in the form of "FirstName = @Something and PropertyBag = @SomethingElse"). But untill our ORMappers start to get aware of Xml typed data inside the database, this is one way to tackle this problem.
public class EntityLoginNameInterceptor : IInterceptCommand
{
public void InterceptCommand(Guid transactionId, Type entityType, CommandInfo commandInfo, System.Data.IDbCommand dbCommand)
{
if (entityType == typeof(Entity))
{
#region Validate request parameters
string[] queryParts = dbCommand.CommandText.Split(new string[] { "WHERE" }, StringSplitOptions.None);
if (queryParts.Length == 0)
return;
if (queryParts[1].IndexOf("PropertyBag") == -1)
return;
#endregion
// Contains the whereClause of the CommandText
string whereClause = queryParts[1];
// ToDo: this code will stop working if the where clause has any other
// predicate then just 'PropertyBag = ', could fix this by using regexes
string[] parts = whereClause.Split('=');
if (String.IsNullOrEmpty(parts[1]))
{
throw new ArgumentNullException("PropertyBag predicate does not have a value!");
}
// Contains the XQuery to query against the PropertyBag XML column
string xQuery =
String.Format("PropertyBag.value('(/properties/property[@name=\"NTLoginName\"])[1]', 'nvarchar(30)') = {0}", parts[1]);
// Save new sql statement to the CommandText property of the IDbCommand,
// Watch the extra '(', this is required for the query to parse
dbCommand.CommandText = String.Format("{0} where ({1}", queryParts[0], xQuery);
}
}
}
If anybody knows a better way (or a good OR-Mapper that understands this problem), do let me know :-)