How to map a column from another table?

c# database-design entity-framework entity-framework-core postgresql

Question

I have two tables articles and content (1-to-1 relation). content table contains an article body in JSON format (PostgreSQL). The tables were split for performance reasons, as the content is too large.

Articles table:

| Column          | Type   |
|-----------------|--------|
| id              | int    |
| title           | string |
| *other columns* | xxx    |

Content table:

| Column    | Type  |
|-----------|-------|
| articleId | int   |
| content   | jsonb |

I'm trying to map this tables into single entity Article:

namespace Domain.Models
{
    [Table("articles")]
    public class Article
    {
        [Column("id")]
        public int Id { get; set; }

        [Column("title")]
        public string Title { get; set; }

        [Column("content")]
        public string Content { get; set; }

        <...> // other columns
    }
}

My DbContext:

namespace Domain
{
    public class MasterDbContext : DbContext
    {
        public DbSet<Article> Articles { get; set; }
    }
}

Question: How to map Content property from content table?


My dumb solution: I just created a non-materialized view and map Article entity to this "table":

CREATE VIEW articles_with_content AS
    SELECT a.*, c.content
    FROM articles AS a
    JOIN content AS c ON c."articleId" = a.id;

Can someone suggest a better solution?

1
0
12/9/2019 12:28:31 PM

Accepted Answer

You need to create a Content class

Then in your Article class have it as:

public Content Content { get; set; }

Then configure your models (in your MasterDbContext class)

protected override void OnModelCreating(ModelBuilder builder)
         {
            builder.Entity<Article>(b =>
            {
                b.HasOne(c => c.Content)

               .WithOne(a => a.Article)

               .HasForeignKey<Content>(d => d.ArticleID);
            });

         }
0
12/9/2019 9:57:21 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow