Entity Framework map collection property to one column

Question

I have model:

 public class FlyerPage
{
    public Guid Id { get; set; }
    public string Picture { get; set; }
    public int? Page { get; set; }

    public string Text { get; set; }

    public ICollection<string> Keywords { get; set; }

    public Guid FlyerId { get; set; }

    public virtual Flyer Flyer { get; set; }
}

And I want to map Keywords to one column in FlyerPage table. I think the best way it to split it into values separated by comma like

"one", "two", "three".

How to do it? Via FluentAPI.

EDIT - To another table

The type 'ICollection<string>' must be a non-nullable value type in order to use it as parameter 'T' in the generic type or method 


 modelBuilder.Entity<FlyerPage>().Map(m =>
        {
            m.Property(p => p.Keywords);
            m.ToTable("Keyword", "Flyers.Page");
        });

EDIT 2: ok this work

        modelBuilder.Entity<FlyerPage>().Map(m =>
        {
            m.Properties(p => p.Keywords);
            m.ToTable("FlyerPageKeywords");
        });

But how connect it will many to many?

Why? Because Keywords are inside Page and I dont want to create another table to get it. — Nerf, Apr 24 '17 at 10:06
You REALLY should have a different table. If you are too lazy to create another table, then perhaps programming isn't for you! — DavidG, Apr 24 '17 at 10:10

Sándor Mátyás Márton · Accepted Answer · 2017-04-24T14:59:14.857

2

Depending on your purpose (for example, if you want to search by keywords) you could create two new tables, one for the Keywords (where every record is a keyword) and one for mapping the relationship between a FlyerPage and a Keyword, containing the Id of a FlyerPage and a Keyword as foreign keys.

Searching by a keyword will probably be a lot more efficient (even with all the JOINs) this way, you can avoid storing duplicate values and it will most likely be closer to the logic of your application.

You are going to end up with two tables (and one more that is going to be automatically generated):

public class FlyerPage
{
    public Guid Id { get; set; }
    public string Picture { get; set; }
    public int? Page { get; set; }

    public string Text { get; set; }

    public ICollection<Keyword> Keywords { get; set; }

    public Guid FlyerId { get; set; }

    public virtual Flyer Flyer { get; set; }
}

public class Keyword
{
    public Guid Id { get; set; }
    public string Value { get; set; }

    public virtual ICollection<FlyerPage> FlyerPages { get; set; }
}

And then the many-to-many mapping with FluentAPI:

modelBuilder.Entity<FlyerPage>() 
    .HasMany(t => t.Keywords) 
    .WithMany(t => t.FlyerPages) 
    .Map(m => 
    { 
        m.ToTable("FlyerPageKeywords"); 
        m.MapLeftKey("FlyerPageID"); 
        m.MapRightKey("KeywordID"); 
    });

I think it will solve your problem. I'm not perfectly "fluent" in FluentAPI, you might have to initialize your collections in the classes' constructor to an empty collection.

Also, the reason for making the relationship bidirectional (that is, storing an ICollection<FlyerPage> in your Keyword object) is, according to the documentation:

By convention, Code First always interprets a unidirectional relationship as one-to-many.

edited Apr 24 '17 at 14:59

answered Apr 24 '17 at 10:08

Sándor Mátyás Márton

513
5
14

OP already said they don't want to do this. (but I agree this is the right thing to do) – DavidG Apr 24 '17 at 10:09
I do not want to searching by keywords. Fact, keywords will be same in most of flyerpages so... I shall do not duplicate it. many to many? – Nerf Apr 24 '17 at 10:13
If keywords will be the same for most of the flyerpages, I especially recommend using this method (you avoid duplication). You usually model many-to-many relationships this way. – Sándor Mátyás Márton Apr 24 '17 at 10:22
How to it well? I mean if I add new FlyerPages with keywords that are in DB and some of them not. Then they wont be duplicated and only those new will be added to "Keywords" table. – Nerf Apr 24 '17 at 10:28
I updated my answer with the FluentAPI code you need. To add a new `Keyword` to a `FlyerPage`, you have to check first if the `Keyword` with the given value exists. If it does, just add it to your `FlyerPage` and if it doesn't, create a new `Keyword` and then add it. You can define a UNIQUE constraint on `Keyword.Value` to make sure you avoid duplicate values, though it is not very straightforward in FluentAPI (see http://stackoverflow.com/a/23155759/4353712) – Sándor Mátyás Márton Apr 25 '17 at 07:13
Wah it's okey but how I can efficient check if keyword is or not while adding new Page? – Nerf Apr 25 '17 at 08:58
The search might not be as expensive as you would think. If you experience performance problems, consider creating an index on `Keyword.Value`. – Sándor Mátyás Márton Apr 25 '17 at 09:15

Jins Peter · Answer 2 · 2017-04-24T10:17:05.077

If i understand you requirement correctly, you want to do a bad DB practice to add comma seperated colllection to one column of a record. I dont understand what you are trying to accomplish doing the specified way. Anyway to implement your requirement.

//This is Really a bad practice. I strongly recommend to go for Sándor Mátyás Márton's solution which is the best practice to accomplish your actual use case.

You need to use a Different Entity

 public class FlyerPage
{
    public Guid Id { get; set; }
    public string Picture { get; set; }
    public int? Page { get; set; }

    public string Text { get; set; }

    public string KeywordsCollection { get; set; }

    public Guid FlyerId { get; set; }

    public virtual Flyer Flyer { get; set; }
}

Here you can set

KeywordsCollection = string.Join(",", flierPage.Keywords); 
//flierPage is the viewmodel object and flierPage.Keywords is the collection 

//you got from the viewmodel

Yeah.I have said it in my answer. Go for this guy's Sándor Mátyás Márton solution . Try researching DB Normalisation practices. But Plz vote me up. And if you are quite sure this is what you need, u can accept this as answer — Jins Peter, Apr 24 '17 at 10:20

score 0 · Answer 3 · answered Apr 24 '17 at 10:14

0

If you really must store your normalised data in a single field, then you could achieve this by amending your class as follows:

public class FlyerPage
{
    public ICollection<string> Keywords
    {
        get
        {
            return _keywords.Split(',');
        }
        set
        {
            _keywords = string.Join(",", value);
        }
    }

    private string _keywords;
}

Then in your fluent mappings, set the Keywords property to ignore and map the _keywords field.

answered Apr 24 '17 at 10:14

Chris Pickford

8,642
5
42
73

Well now you have a problem keywords containing commas. – DavidG Apr 24 '17 at 10:19
Indeed, although generally a keyword would be a single word sans punctuation. OPs example was such. The real issue would be that the `ICollection` should be made immutable. – Chris Pickford Apr 24 '17 at 10:22
There's actually also the issue of a client trying to call `page.Keywords.Add("newkeyword")` which will actually do nothing. Oh, and you can't map a private field so you would need a backing property. – DavidG Apr 24 '17 at 10:31

score 0 · Answer 4 · answered Apr 24 '17 at 10:15

0

Create a joined list of keywords.

public class FlyerPage
    {
        .....

        public string JoinedKeywords 
        {
           get
           {
              return String.Join(",", this.Keywords);
           }
        }
        public ICollection<string> Keywords {get; set;}
    }

answered Apr 24 '17 at 10:15

Sajal

4,359
1
19
39

Well now you have a problem keywords containing commas. – DavidG Apr 24 '17 at 10:19

Entity Framework map collection property to one column

EDIT - To another table

EDIT 2: ok this work

4 Answers4