It is possible to query a NotMapped property?

c# ef-code-first ef-code-first-mapping entity-framework entity-framework-6

Question

I'm now working with EF6 code, and I mapped a variable using this response.List<stirng> in my opinion.

I'm in this class.

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

My list is nicely stored as JSON, and now that I need to do a linq query, I'm attempting this.

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

It's also flinging

System.NotSupportedException: The specified type member 'ListaEspecifica' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

what makes sense.

Is it possible to run a query like this?

1
1
10/23/2018 6:06:51 PM

Accepted Answer

This situation is problematic because LINQ to Entities is unable to translate your query into the back-end (SQL) language. LINQ attempts to transform your query to SQL itself since you are not materializing (i.e. converting to.NET) the results of the query until you filter it. You get a because it is unsure of how to perform itNotSupportedException .

You first materialize the query (that is, call a.ToList() ) and then filter, everything will be okay. But I have a feeling you don't want this. ( I.e.db.CategoriaAccesorios.ToList().Where(c => c.ListaEspecifica.Contains("Buc")).First(); )

Your problem, as this response describes, is the EF to SQL Conversion. Of course, you want a solution that gets around that.

There are really a few choices here since you are JSON serializing, most notably utilizing a LIKE:

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

If EF Core, as claimedMicrosoft.EntityFrameworkCore.EF.Functions.Like should substituteSqlMethods.Like .

Using SQL Server 2016+, you may force theSubrubrosAbarcados It must be feasible to perform a raw query to specifically query the JSON column in order for anything to be a JSON type.

If you're interested about how such element would seem in SQL Server 2016, here is an example:

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
2
10/23/2018 4:14:05 PM

Popular Answer

Zzz-5-Zzz enabled me to do something like to this.

using Microsoft.Linq.Translations;

// (...) namespace, class, etc

private static readonly CompiledExpression<MyClass, List<string>> _myExpression = DefaultTranslationOf<MyClass>
    .Property(x => x.MyProperty)
    .Is(x => new List<string>());

[NotMapped]
public List<string> MyProperty
{
    get { return _myExpression.Evaluate(this); }
}

Though I do hope there are better or nicer options;)







Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow