Query string properties stored as XML

Multi tool use
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.





How could it help me? However, it does not work, just tried. Thanks anyway.
– Marco Luzzara
Jul 2 at 9:19





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.

efK55Sgi0iJ zfJ
eXGZlaV41NBdsJzAt LCtZLBHRk

Popular posts from this blog

PySpark - SparkContext: Error initializing SparkContext File does not exist

django NoReverseMatch Exception

List of Kim Possible characters