Query string properties stored as XML

Multi tool use
Query string properties stored as XML
I am using Entity Framework to query a db which is defined by a model: inside this model I have several classes having a #region dynamic values
:
#region dynamic values
[DataContract]
public class Job : AbstractEntity, IJob
{
[DataMember]
public virtual Guid Id { get; set; }
...
#region dynamic values
[DataMember]
public virtual string MetadataValue { get; set; }
[DataMember]
public virtual string ParametersValue { get; set; }
[DataMember]
public virtual string AttributesValue { get; set; }
#endregion
#region links
...
#endregion
}
AttributesValue
, MetadataValue
and ParametersValue
are declared as string but are stored inside the db as XML documents. I am aware that this is not consistent with the model and should be changed, but for some reasons it has been managed this way and I am not allowed to modify it.
I have created a Unit Test in order to better handle the problem, and here is the code:
AttributesValue
MetadataValue
ParametersValue
public class UnitTest1
{
private ModelContext mc;
[TestInitialize]
public void TestInit()
{
IModelContextFactory mfactory = ModelContextFactory.GetFactory();
mc = mfactory.CreateContextWithoutClientId();
}
[TestMethod]
public void TestMethod1()
{
DbSet<Job> jobs = mc.Job;
IQueryable<string> query = jobs
.Where(elem => elem.AttributesValue == "<coll><item><key>ids:ui:description</key><value>Session Test</value></item><item><key>ids:all:type</key><value>signature</value></item></coll>")
.Select(elem => elem.AttributesValue);
List<string> attrs = new List<string>(query);
foreach (string av in attrs)
{
Console.WriteLine(av ?? "null");
}
Assert.AreEqual(1, 1);
}
}
A quick explanation about the TestInit
and ModelContext
:ModelContext
inherit from DbContext
and is an abstract class implemented by SqlModelContext
and OracleModelContext
(both override OnModelCreating
). Depending on the connection string, CreateContextWithoutClientId
return a SqlModelContext
or an OracleModelContext
. Summary: a Factory pattern.
TestInit
ModelContext
ModelContext
DbContext
SqlModelContext
OracleModelContext
OnModelCreating
CreateContextWithoutClientId
SqlModelContext
OracleModelContext
Let's get down to brass tacks: the TestMethod1
.
The problem here is in the Where
method and the error returned is, as expected:
TestMethod1
Where
SqlException: The data types nvarchar and xml are incompatible in the equal to operator.
(From now on I will only consider the AttributesValue
property)
AttributesValue
I thought of some possible solutions, which are:
Creating a new property inside the model (but not mapped to the db) and use it as a "proxy" instead of accessing directly AttributesValue
. However only mapped properties can be used in Linq, so I discarded it.
AttributesValue
Operating directly on the inner SQL query generated by the IQueryable
and using a customized CAST
for Oracle and Sql Server db. I'd rather avoid go for this for obvious reasons.
IQueryable
CAST
Is there a way to specify a custom Property Getter so that I can cast AttributesValue
to string before it is accessed? Or maybe some configuration on the DbModelBuilder
?
AttributesValue
DbModelBuilder
I'm using standard Entity Framework 6, Code-First approach.
EF6 or EF Core? If EF6, code first model or edmx?
– Ivan Stoev
Jul 2 at 16:08
@IvanStoev I completely forgot to specify it, thanks.
– Marco Luzzara
Jul 2 at 16:36
1 Answer
1
There is no standard xml data type or standard canonical function for converting string to xml or vice versa.
Fortunately EF6 supports the so called Entity SQL Language which supports an useful construct called CAST:
CAST (expression AS data_type)
The cast expression has similar semantics to the Transact-SQL CONVERT expression. The cast expression is used to convert a value of one type into a value of another type.
It can be utilized with the help of the EntityFramework.Functions package and Model defined functions.
Model defined functions allow you to associate Entity SQL expression with user defined function. The requirement is that the function argument must be an entity.
The good thing about Entity SQL operators is that they are database independent (similar to canonical functions), so the final SQL is still generated by the database provider, hence you don't need to write separate implementations for SqlServer and Oracle.
Install the EntityFramework.Functions package through Nuget and add the following class (note: all the code requires using EntityFramework.Functions;
):
using EntityFramework.Functions;
public static class JobFunctions
{
const string Namespace = "EFTest";
[ModelDefinedFunction(nameof(MetadataValueXml), Namespace, "'' + CAST(Job.MetadataValue AS String)")]
public static string MetadataValueXml(this Job job) => job.MetadataValue;
[ModelDefinedFunction(nameof(ParametersValueXml), Namespace, "'' + CAST(Job.ParametersValue AS String)")]
public static string ParametersValueXml(this Job job) => job.ParametersValue;
[ModelDefinedFunction(nameof(AttributesValueXml), Namespace, "'' + CAST(Job.AttributesValue AS String)")]
public static string AttributesValueXml(this Job job) => job.AttributesValue;
}
Basically we add simple extension method for each xml property. The body of the methods doesn't do something useful - the whole purpose of these methods is not to be called directly, but to be translated to SQL when used inside LINQ to Entities query. The required mapping is provided through ModelDefinedFunctionAttribute
and applied via package implemented custom FunctionConvention
. The Namespace
constant must be equal to typeof(Job).Namespace
. Unfortunately due to the requirement that attributes can use only constants, we can't avoid that hardcoded string as well as the entity class / property names inside the Entity SQL string.
ModelDefinedFunctionAttribute
FunctionConvention
Namespace
typeof(Job).Namespace
One thing that needs more explanation is the usage of '' + CAST
. I wish we could use simply CAST
, but my tests show that SqlServer is "too smart" (or buggy?) and removes the CAST
from expression when used inside WHERE
. The trick with appending the empty string prevents that behavior.
'' + CAST
CAST
CAST
WHERE
Then you need to add these functions to entity model by adding the following line to your db context OnModelCreating
override:
OnModelCreating
modelBuilder.AddFunctions(typeof(JobFunctions));
Now you can use them inside your LINQ to Entities query:
IQueryable<string> query = jobs
.Where(elem => elem.AttributesValueXml() == "<coll><item><key>ids:ui:description</key><value>Session Test</value></item><item><key>ids:all:type</key><value>signature</value></item></coll>")
.Select(elem => elem.AttributesValue);
which translates to something like this in SqlServer:
SELECT
[Extent1].[AttributesValue] AS [AttributesValue]
FROM [dbo].[Jobs] AS [Extent1]
WHERE N'<coll><item><key>ids:ui:description</key><value>Session Test</value></item><item><key>ids:all:type</key><value>signature</value></item></coll>'
= ('' + CAST( [Extent1].[AttributesValue] AS nvarchar(max)))
and in Oracle:
SELECT
"Extent1"."AttributesValue" AS "AttributesValue"
FROM "ORATST"."Jobs" "Extent1"
WHERE ('<coll><item><key>ids:ui:description</key><value>Session Test</value></item><item><key>ids:all:type</key><value>signature</value></item></coll>'
= ((('')||(TO_NCLOB("Extent1"."AttributesValue")))))
Right this morning I found out about the existence of
EntityFramework.Function package
. However, really good explanation. If it works, tomorrow I am going to accept it. Thank you.– Marco Luzzara
Jul 2 at 19:04
EntityFramework.Function package
Nice piece of work Ivan! One caveat though. As you know, with the XML datatype the preferred way of querying is to use the database's built-in XPath query operators. Of course, there is no support for those operators in EF, so from a performance POV it may be better to run SQL statements with, for example, Dapper. I don't consider that an answer to this question though, as yours is.
– Gert Arnold
Jul 3 at 8:42
It works perfectly. Only one thing seems pretty weird: the namespace I have to use is CodeFirstNamespace even though there is not even a match of it inside my solution.
– Marco Luzzara
Jul 3 at 8:53
Yeah, that's strange. And I cannot use that,
Function
code throws exception that I should use "EFTest" in my case. Looking at the package source code, it validates the namespace against the namespace of the first entity type in the conceptual model. "CodeFirstNamespace" sounds like there is an entity automatically generated by EF, but I have no idea how and when.– Ivan Stoev
Jul 3 at 10:24
Function
Good point as always @Gert
– Ivan Stoev
Jul 3 at 10:27
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
How could it help me? However, it does not work, just tried. Thanks anyway.
– Marco Luzzara
Jul 2 at 9:19