How to query in LINQ & Entity Framework the unmapped property

Question

So I partially followed from an SO answer on how to store a property with array datatype in Entity Framework. What I didn't follow on that answer is setting the string InternalData to be private instead of public as I find it a code smell if it is set to public (not enough reputation to comment there yet).

I also managed to map the private property in entity framework from this blog.

When I perform CR (create, read) from that entity, all goes well. However, when my LINQ query has a where clause using that property with array datatype, it says that "System.NotSupportedException: 'The specified type member is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.'".

How to work around on this? Here are the relevant code blocks:

public class ReminderSettings
{
   [Key]
   public string UserID { get; set; }
   [Column("RemindForPaymentStatus")]
   private string _remindForPaymentStatusCSV { get; set; }
   private Status[] _remindForPaymentStatus;
   [NotMapped]
   public Status[] RemindForPaymentStatus
   {
      get 
      {
         return Array.ConvertAll(_remindForPaymentStatusCSV.Split(','), e => (Status)Enum.Parse(typeof(Status), e));
      }
      set
      {
         _remindForPaymentStatus = value;
         _remindForPaymentStatusCSV = String.Join(",", _remindForPaymentStatus.Select(x => x.ToString()).ToArray());
      }
   }
   public static readonly Expression<Func<ReminderSettings, string>> RemindForPaymentStatusExpression = p => p._remindForPaymentStatusCSV;
}

public enum Status
{
  NotPaid = 0,
  PartiallyPaid = 1,
  FullyPaid = 2,
  Overpaid = 3 
}

protected override void OnModelCreating(DbModelBuuilder modelBuilder)
{
   modelBuilder.Entity<ReminderSettings>().Property(ReminderSettings.RemindForPaymentStatusExpression);
}

//This query will cause the error
public IEnumerable<ReminderSettings> GetReminderSettingsByPaymentStatus(Status[] statusArray)
{
   var query = ApplicationDbContext.ReminderSettings.Where(x => x.RemindForPaymentStatus.Intersect(statusArray).Any());
   return query.ToList(); //System.NotSupportedException: 'The specified type member 'RemindForPaymentStatus' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.'
}

Georg Patscheider · Answer 1 · 2018-06-14T07:15:09.167

Entity Framework can not translate LINQ expressions to SQL if they access a property that is annotated as [NotMapped]. (It also can not translate if the property contains custom C# code in its getter/setter).

As a quick (but potentially low performance) workaround, you can execute the part of the query that does not cause problems, then apply additional filtering in-memory.

// execute query on DB server and fetch items into memory
var reminders = dbContext.ReminderSettings.ToList(); 

// now that we work in-memory, LINQ does not need to translate our custom code to SQL anymore
var filtered = reminders.Where(r => r.RemindForPaymentStatus.Contains(Status.NotPaid));

If this causes performance problems, you have to make the backing field of your NotMapped property public and work directly with it.

var filtered = dbContext.ReminderSettings
    .Where(r => r._remindForPaymentStatusCSV.Contains(Status.NotPaid.ToString("D"));

Edit To handle multiple Status as query parameters, you can attach Where clauses in a loop (which behaves like an AND). This works as long as your Status enum values are distinguishable (i.e. there is no Status "11" if there is also a Status "1").

var query = dbContext.ReminderSettings.Select(r => r);
foreach(var statusParam in queryParams.Status) {
    var statusString = statusParam.ToString("D");
    query = query.Where(r => r._remindForPaymentStatusCSV.Contains(statusString));
}
var result = query.ToArray();

Thanks Georg. I opt to choose the latter since it is scalable. Though in the where clause, I want to compare an array of status vs the parameter/inputted array of status. I edited my question for this matter. I tried composing a where clause with this one: but I still get an error. var query = ApplicationDbContext.ReminderSettings .Where(x => Array.ConvertAll(x._remindForPaymentStatusCSV.Split(','), e => (Status)Enum.Parse(typeof(Status), e)).Intersect(statusArray).Any()); return query.ToList(); — ricsierics, Jun 13 '18 at 11:02
You can also do this _without_ first materializing the data-set, provided your data is a good format (not CSV, but JSON would be far better): https://stackoverflow.com/a/52953313/4564272 — Der Kommissar, Oct 23 '18 at 17:52

How to query in LINQ & Entity Framework the unmapped property

1 Answers1