How to setup entity relations in Entity Framework Core

Question

Here we are on EF Core and got 3 tables:

News
Items
Links

And more (except News, Items): Content, Post, Form, etc.

And my model definitions

public class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public Link Link { get; set; }
}

public class News
{
    public int Id { get; set; }
    public string Header { get; set; }
    public string Content { get; set; }
    public Link Link { get; set; }
}

public class Link
{
    public int Id { get; set; }
    public string Type { get; set; }
    public int RowId  { get; set; }
    public string Url { get; set; }
}

Table Links describe URL for every News and every Item. This means that Links has 4 columns:

Id
Type - news or item
RowId - contains ID of Item or News (depends on the Type)
URL

How to setup the relationships? Keep in mind that we need to resolve Entity by URL in Links table.

Please use a more describing title next time and use the appropriate tags. [tag:entity-framework] is for the old EntityFramework (1 to 6.x). [tag:entity-framework-core] is for the new, from scratch rewritten EF Core and last but not least, before asking this type of question it's faster and easier to consume the documentation https://learn.microsoft.com/en-us/ef/core/modeling/relationships instead of waiting for an answer ;) — Tseng, Mar 25 '17 at 13:03
EF Core . The main problem that is an existing database and undesirable to modify DB, because alot of data and alot of entities with this links (content, catalog and more) — Alex, Mar 25 '17 at 15:37
What are you describing is a database model which cannot be described with FK constraints, thus cannot be mapped in any EF version, including Core. — Ivan Stoev, Apr 05 '17 at 08:32
You've got [*polymorphic associations*](http://stackoverflow.com/a/2003042/861716) here. If you want to turn it into a model with FK constraints you *have* to change the database schema. — Gert Arnold, Apr 05 '17 at 09:06
You can further normalize the schema and can add two new tables Link_New:{LinkId, NewId} Link_Item: {LinkId, ItemId} Removing RowId from Link table and Type telling whether it is Item or News — Murtaza Tahir Ali, Apr 10 '17 at 14:19

score 1 · Accepted Answer · answered Jun 04 '17 at 14:24

1

Dapper. Just Dapper that allows write custom queries.

answered Jun 04 '17 at 14:24

Alex

311
2
14

Masoud Andalibi · Answer 2 · 2017-03-25T16:30:05.057

0

Update

I read your question once more and well to be honest you don't have to create another table for Links, just add a Column to News and Item and so on.. and you will be fine.

public class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Url { get; set; }
}

public class News
{
    public int Id { get; set; }
    public string Header { get; set; }
    public string Content { get; set; }
    public string Url { get; set; }
}

edited Mar 25 '17 at 16:30

answered Mar 25 '17 at 13:14

Masoud Andalibi

3,168
5
18
44

Good idea for News and Item. But won't modify Links table and add columns, because except news and items there are other entities like content, etc. So it will be alot of NULL. That's a reason why here's `RowId` and `Type`. Could I use something like `HasDiscriminator` or another trick? – Alex Mar 25 '17 at 15:41
how to build sitemap then? `foreach` every tables is not good idea. Btw, we have a Menu table that describes tree menu. – Alex Mar 25 '17 at 16:55
You want to generate a link for one record, its the best if you put it in same table. also for sitemap you can add a colum named ordering and manage it that way. – Masoud Andalibi Mar 26 '17 at 03:07
Here is a problem to find needed URL. In request you have URL and need to find what is it: Item, News, Content, Catalog or some form. That is a reason why we have only one Links table. So we can't add URL to each table. – Alex Mar 26 '17 at 07:34

score 0 · Answer 3 · answered Apr 05 '17 at 05:12

0

I'd change Link to use nullable int, and separate out the foreign keys for these tables:

public class Link
{
    public int Id { get; set; }
    public string Type { get; set; }
    public int? NewsId  { get; set; }
    public int? ItemId  { get; set; }
    public string Url { get; set; }
}

answered Apr 05 '17 at 05:12

K Scandrett

16,390
4
40
65

No, because here's alot of entities except news and items. – Alex Apr 05 '17 at 07:29

score 0 · Answer 4 · answered Apr 09 '17 at 02:12

I think you can remove the rowid from the links table and add a foreign key column in the other 2 tables referencing the id column in the links table.

Now, with you having the URL, you have the type and id, you can just query the content from respective table.

score 0 · Answer 5 · answered Apr 11 '17 at 19:24

In order to have the property NewRow, and the constraints to the other two tables, you cold implement it like this:

public int RowId { 
    public get {
        return this.Type.equals("news") ? NewsId.Value : ItemId.Value;
    };   
    private set {
        if(this.Type.equals("news")){
          NewsId = value;
        }
        else{
          ItemId = value;
        }
    }
}

And then set this property as not mapped on the ContextDB.

How to setup entity relations in Entity Framework Core

5 Answers5