77645

It is possible to query a NotMapped property?

Question:

i'm working with EF6 code first, and i used <a href="https://stackoverflow.com/a/37207034/5213794" rel="nofollow">this</a> 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

<blockquote>

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

</blockquote>

what is logical.

Is there any way to perform a query like this?

Answer1:

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 <em>until</em> 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.

<em>If</em> 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 <a href="https://stackoverflow.com/questions/6919709/only-initializers-entity-members-and-entity-navigation-properties-are-supporte?" rel="nofollow">this answer</a> 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 <a href="https://stackoverflow.com/questions/3913697/how-to-do-a-like-query-with-linq" rel="nofollow">LIKE</a>:

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

Answer2:

I was able to do something like this via <a href="https://github.com/damieng/Linq.Translations/blob/master/Microsoft.Linq.Translations/CompiledExpression.cs" rel="nofollow">CompiledExpression</a>.

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

Recommend

  • Excessive use of If else statements
  • Is there a way to selectively skip silverlight field validation when using wcf ria?
  • Dynamically select and update a column value in LINQ resultset
  • Python Floating Point Formatting
  • Sorting a list based on the contents of another list
  • TextDecorations not being applied to TextBlock during a TreeViewItem Style Trigger
  • Asp Core, How to create Paging?
  • ServiceStack not rendering Razor View, only seeing Snap Shot
  • Linq to Sql Foreign key relationships
  • How can I organize this data into the objects I want with LINQ?
  • How does `std::terminate` know to treat `std::exception`s specially?
  • datatables left join c#
  • Finding All Combinations (cartesian product) of list values in vb.net
  • Cannot use a lambda expression as an argument to a dynamically dispatched operation
  • Handling exceptions in a class library enveloping a device driver
  • Finding parents in a tree hierarchy for a given child LINQ (lambda expression)
  • Why does .addView throw this parent/child exception?
  • API (curl)Command to Approve a promoted build Job in Jenkins
  • How to Divide an array on c#?
  • string.IsNullOrEmpty() Doesn't Seem to Work on a String within a Class within a Class
  • Return null in boolean to checkbox state converter in XAML
  • Azure table query partial partitionkey guid match
  • Many to Many in Linq using Dapper
  • Multiple Left Join LINQ-to-entities
  • SIP API media codecs
  • Exception creating JSON with LINQ
  • Does Apportable support to build library binary (.a/.so)?
  • Linq Merge lists
  • SetWindowsHookEx does not react on media keys
  • output of program is not same as passed argument
  • Timeout for blocking function call, i.e., how to stop waiting for user input after X seconds?
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Proper folder structure for lots of source files
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • Trying to get generic when generic is not available
  • How do I configure my settings file to work with unit tests?
  • How does Linux kernel interrupt the application?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Binding checkboxes to object values in AngularJs