代码之家 › 专栏 › 技术社区 › Marco Luzzara

存储为XML的查询字符串属性

linq entity-framework xml sql c#

Marco Luzzara · 技术社区 · 7 年前

#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 ParametersValue 声明为字符串,但作为XML文档存储在数据库中。我知道这与模型不一致,应该更改,但由于某些原因,它是以这种方式管理的,我不允许修改它。

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);
    }
}

关于 TestInit ModelContext 模型上下文 DbContext 是由实现的抽象类 SqlModelContext OracleModelContext (两个都覆盖 OnModelCreating CreateContextWithoutClientId 返回A OracleModelContext

让我们来谈谈黄铜钉: TestMethod1 问题出在 Where

(从现在起,我只考虑属性)

属性值
IQueryable 使用定制的 CAST

属性值 DbModelBuilder 是吗?

我使用的是标准的实体框架6,代码优先方法。

1 回复 | 直到 7 年前

Ivan Stoev 7 年前

幸运的是,EF6支持所谓的 Entity SQL Language CAST

CAST (expression AS data_type)

EntityFramework.Functions Model defined functions

Nuget 并添加以下类(注意:所有代码都需要 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;
}

ModelDefinedFunctionAttribute FunctionConvention Namespace 常量必须等于 typeof(Job).Namespace

需要更多解释的一件事是 '' + CAST CAST WHERE .附加空字符串的技巧可以防止这种行为。

OnModelCreating

modelBuilder.AddFunctions(typeof(JobFunctions));

现在,您可以在Linq to Entities查询中使用它们:

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

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

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