是否可以查询NotMapped属性?

问题描述

我首先使用EF6代码，我使用了此答案来映射我的整个List<stirng>

i'm working with EF6 code first, and i used this answer to map a List<stirng> in my entitie.

这是我的课程

    [Key]
    public string SubRubro { get; set; }
    [Column]
    private string SubrubrosAbarcados
    {
        get
        {
            return ListaEspecifica == null || !ListaEspecifica.Any() ? null : JsonConvert.SerializeObject(ListaEspecifica);
        }
        set
        {
            if (string.IsNullOrWhiteSpace(value))
                ListaEspecifica.Clear();
            else
                ListaEspecifica = JsonConvert.DeserializeObject<List<string>>(value);
        }
    }

    [NotMapped]
    public List<string> ListaEspecifica { get; set; } = new List<string>();

它非常适合以Json的身份存储我的列表，但是现在我需要执行linq查询，而我正在尝试这样做

It works perfectly to storage my list as Json, but now i need to perform a linq query, and i'm trying this

var c = db.CategoriaAccesorios.Where(c => c.ListaEspecifica.Contains("Buc")).First();

它正在扔

什么是合乎逻辑的.

有什么方法可以执行这样的查询吗?

Is there any way to perform a query like this?

推荐答案

此处的问题是LINQ to Entities无法理解如何将查询转换为后端(SQL)语言.因为您没有实现查询直到的结果(即转换为.NET)，所以您对其进行过滤，因此LINQ尝试将查询转换为SQL本身.由于不确定如何执行此操作，因此会得到NotSupportedException.

The problem here is that LINQ to Entities does not understand how to convert your query to the back-end (SQL) language. Because you're not materializing (i.e. converting to .NET) the results of the query until you filter it, LINQ tries to convert your query to SQL itself. Since it's not sure how to do that, you get a NotSupportedException.

如果首先实现查询(即调用.ToList())，然后进行过滤，则一切正常.我怀疑这不是您想要的. (即db.CategoriaAccesorios.ToList().Where(c => c.ListaEspecifica.Contains("Buc")).First();)

If you materialize the query first (I.e. call a .ToList()) then filter, things will work fine. I suspect this isn't what you want, though. (I.e. db.CategoriaAccesorios.ToList().Where(c => c.ListaEspecifica.Contains("Buc")).First();)

此答案解释了 ，您遇到的问题是EF到SQL的转换.显然，您需要某种解决方法.

As this answer explains, your issue is the EF to SQL Conversion. Obviously you want some way to workaround it, though.

因为您要进行JSON序列化，所以实际上这里有几个选项，尤其是使用:

Because you are JSON serializing, there are actually a couple options here, most particularly using a LIKE:

var c =
    (from category
     in db.CategoriaAccessorios
     where SqlMethods.Like(c.SubrubrosAbarcados, "%\"Buc\"%")
     select category).First()

如果是EF Core，则据称Microsoft.EntityFrameworkCore.EF.Functions.Like应该替换SqlMethods.Like.

If EF Core, allegedly Microsoft.EntityFrameworkCore.EF.Functions.Like should replace SqlMethods.Like.

如果您拥有SQL Server 2016+，并强制SubrubrosAbarcados为JSON类型，则应该可以使用原始查询直接直接查询JSON列.

If you have SQL Server 2016+, and force the SubrubrosAbarcados to be a JSON type, it should be possible to use a raw query to directly query the JSON column in particular.

如果您对上述方面感到好奇，请查看以下示例，了解它在SQL Server 2016中的外观:

If you're curious about said aspect, here's a sample of what it could look like in SQL Server 2016:

CREATE TABLE Test (JsonData NVARCHAR(MAX))
INSERT INTO Test (JsonData) VALUES ('["Test"]'), ('["Something"]')
SELECT * FROM Test CROSS APPLY OPENJSON(JsonData, '$') WITH (Value VARCHAR(100) '$') AS n WHERE n.Value = 'Test'
DROP TABLE Test

这篇关于是否可以查询NotMapped属性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！