It is possible to query a NotMapped property?

Question

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

This is my 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>();

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

And it's throwing

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 is logical.

Is there any way to perform a query like this?

@KennethK. that was what i'm thinking, do you know a better solution to persist a `List` of primitive type? — Juan Salvador Portugal, Oct 23 '18 at 15:43
_"The NotMapped attribute can be applied to properties of an entity class **for which we do not want to create corresponding columns in the database**"_ - so no, you can't query it. — stuartd, Oct 23 '18 at 15:52

Der Kommissar · Accepted Answer · 2018-10-23T16:14:05.257

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.

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

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

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

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

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.

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

score 1 · Answer 2 · answered Oct 23 '18 at 15:58

I was able to do something like this via CompiledExpression.

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

I hope there are better / prettier solutions though ;)

It is possible to query a NotMapped property?

2 Answers2

Linked