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.

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")))))


EntityFramework.Function package

Function

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.