EF 4: Referencing Non-Scalar Variables Not Supported

I'm using code first and trying to do a simple query, on a List property to see if it contains a string in the filtering list. However I am running into problems. For simplicit开发者_JAVA百科y assume the following.

public class Person
{
   public List<string> FavoriteColors { get; set; }
}

//Now some code. Create and add to DbContext
var person = new Person{ FavoriteColors = new List<string>{ "Green", "Blue"} };
dbContext.Persons.Add(person);
myDataBaseContext.SaveChanges();

//Build 
var filterBy = new List<string>{ "Purple", "Green" };
var matches = dbContext.Persons.AsQueryable();
matches = from p in matches
          from color in p.FavoriteColors 
          where filterBy.Contains(color)
          select p;

The option I am considering is transforming this to a json serialized string since I can perform a Contains call if FavoriteColors is a string. Alternatively, I can go overboard and create a "Color" entity but thats fairly heavy weight. Unfortunately enums are also not supported.

---

I think the problem is not the collection, but the reference to matches.

var matches = dbContext.Persons.AsQueryable();
matches = from p in matches
          from color in p.FavoriteColors 
          where filterBy.Contains(color)
          select p;

If you check out the Known Issues and Considerations for EF4 this is more or less exactly the case mentioned.

Referencing a non-scalar variables, such as an entity, in a query is not supported. When such a query executes, a NotSupportedException exception is thrown with a message that states "Unable to create a constant value of type EntityType.

Also note that it specifically says that referencing a collection of scalar variables is supported (that's new in EF 4 imo).

Having said that the following should work (can't try it out right now):

matches = from p in dbContext.Persons
          from color in p.FavoriteColors 
          where filterBy.Contains(color)
          select p;

---

I decided to experiment by creating a "StringEntity" class to overcome this limitation, and used implicit operators to make nice easy transformations to and from strings. See below for solution:

public class MyClass
{
    [Key, DatabaseGenerated(DatabaseGenerationOption.Identity)]
    public Guid Id { get; set; }
    public List<StringEntity> Animals { get; set; }
    public MyClass()
    {
        List<StringEntity> Animals = List<StringEntity>();
    }
}
public class StringEntity
{
    [Key, DatabaseGenerated(DatabaseGenerationOption.Identity)]
    public Guid Id { get; set; }
    public string Value { get; set; }

    public StringEntity(string value) { Value = value; }
    public static implicit operator string(StringEntity se) { return se.Value; }
    public static implicit operator StringEntity(string value) { return new StringEntity(value);  }
}
public class MyDbContext : DbContext
{           
    public DbSet<MyClass> MyClasses { get; set; }       

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyClass>()
            .HasMany(x => x.Animals)
            .WithMany()
            .Map(x =>
            {
                x.MapLeftKey(l => l.Id, "MyClassId");
                x.MapRightKey(r => r.Id, "StringEntityId");
            });
    }
}

...Everything looked like it was working perfectly with some testing(Albeit heavy), and then I implemented for its original purpose, a Multiselect ListBox in an MVC3 view. For reasons unknown to me, IF the ListBox is assigned the same NAME as an Entity Collection Property, none of your selected items will be loaded.

To demonstrate the following did NOT work: //Razor View Code

string[] animalOptions = new string[] {"Dog", "Cat", "Goat"};
string[] animalSelections = new string[] {"Dog", "Cat"};
Html.ListBox("Animals", Multiselect(animalOptions, animalSelections));

To get around this limitation, I needed to do four things:

//#1 Unpluralize the ListBox name so that is doesn't match the name Model.Animals
var animalOptions = new string[] {"Dog", "Cat", "Goat"};
@Html.ListBox("Animal", new MultiSelectList(animalOptions, Model.Animals.Select(x => x.Value)))

//#2 Use JQuery to replace the id and name attribute, so that binding can occur on the form post
<script type="text/javascript">
   jQuery(function ($) {
     $("select#Animal").attr("name", "Animals").attr("id", "Animals");
    });
</script>

//#3 Create a model binder class to handle List<StringEntity> objects 
public class StringEntityListBinder : IModelBinder
{
  public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
  {
     var stringArray = controllerContext.HttpContext.Request.Params.GetValues(bindingContext.ModelName);
     return stringArray.Select(x => new StringEntity(x)).ToList();
  }
}

//#4 Initialize the binder in your Global.asax setup.
ModelBinders.Binders.Add(typeof(List<StringEntity>), new StringEntityListBinder ());

Note, that the Listbox bug did NOT occur when the property was a List of strings, it just didn't like it when it was a List of entities.