Defining comma separated strings in EF and using with Linq

Question

I am trying to define an EF Model containing a list or array of <string>. Using Add-Migration/Update-Database the field does not get added to the database table.

What I want is a field that would contain a comma-separated list of string values. There may be only a single value, or there could be 10 or more:

"Value1, Value2, ..."

I'd like to be able to use this field within my MVC5/EF6 web application as a key. For example:

IEnumerable<Param> params = 
    from u in _context.Params
    where u.MyValues.Contains("Value1") 
    select u;

I know this code is wrong, but here is what I am attempting:

public class Param
{
    public int Id { get; set; }
    public string[] MyValues { get; set; }
}

My DbSet:

public DbSet<Param> Params { get; set; }

The MyValues field doesn't get added to the schema and there are no errors thrown. Here is the Seed():

context.Params.Add(c => c.Id, new Param { MyValues = new[] { "Value1, Value2" } });

How can I add a field that contains a comma-separated list of string values that can then be accessed/queryied?

Do you want to use an array of strings, or a comma-separated list of values? You're trying to use both right now. — Mister Epic
– Mister Epic, Commented Jul 30, 2014 at 12:52
@Chris Hardie, I see that I included some code unintentionally. At first I was using IList<string> MyValues, but apparently EF6 will not create a database field of this type. I tried string[] as you can see, but that didn't generate a field either. I think Pragmateek provided some hints of how I can use a string to make it work. — rwkiii
– rwkiii, Commented Jul 30, 2014 at 23:24

Pragmateek · Accepted Answer · 2014-07-30 13:11:38Z

2

As of now EF does not support custom conversions (maybe EF 7 will...) so you'll have to add some plumbing.

Here is what I typically do (there may be other better ways...):

private string myValuesAsString;
[Column("MyValues")]
public string MyValuesAsString
{
    get { return myValuesAsString; }
    set
    {
        myValuesAsString = value;
        myValues = value.Split(',');
    }
}

private string[] myValues;
[NotMapped]
public string[] MyValues
{
    get { return myValues; }
    set
    {
        myValues= value;
        myValuesAsString = string.Join(",", value);
    }
}

Not tested but you get the idea.

If you don't like the idea of adding public properties to your business entities you can:

inherit from your POCO business entities
use internal properties but mapping must then be done with the fluent API
use interfaces for a perfect abstraction

answered Jul 30, 2014 at 13:11

Pragmateek

13.6k9 gold badges80 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rwkiii Over a year ago

You gave me a hint of how I can accomplish what I need using [NotMapped]. Thank you.

Collectives™ on Stack Overflow

Defining comma separated strings in EF and using with Linq

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related