Entity Framework Core one to many mapping from SQL

Question

I have this simple Blog database structure with 4 tables:

DB Diagram

and some sample data in each table looks like this:

Blogs table:

Blog table

Posts table:

Post table

Tags table:

Tags table

PostTags table:

PostTags table

And I have this SQL script.

SELECT b.Id, 
       b.Title, 
       p.Id, 
       p.Title, 
       p.PostContent, 
       t.Name
FROM dbo.Blogs b
     JOIN Posts p ON p.BlogId = b.Id
     LEFT JOIN PostTags pt ON pt.PostId = p.Id
     LEFT JOIN Tags t ON t.Id = pt.TagId
WHERE b.Id = 1
      AND p.IsDeleted = 0;

There are a few ways to execute this script with EF Core. One is to call this SQL script directly from the code. Another way to create a stored procedure or view and call that from the code.

Supposed I have the followings classes to map the result of executed SQL script by EF Core.

public partial class Blog
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Slogan { get; set; }

    public virtual ICollection<Post> Posts { get; set; }
}

public partial class Post
{
    public int Id { get; set; }
    public int BlogId { get; set; }
    public string Title { get; set; }
    public string PostContent { get; set; }

    public virtual ICollection<PostTag> PostTags { get; set; }
}

public partial class Tag
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<PostTag> PostTags { get; set; }
}   

public partial class PostTag
{
    public int Id { get; set; }
    public int PostId { get; set; }
    public int TagId { get; set; }

    public virtual Post Post { get; set; }
    public virtual Tag Tag { get; set; }
}

This is a method in a controller:

[Route("posts/{blogId}")]
[HttpGet]
public async Task<IActionResult> GetBlogPosts(int blogId)
{
        string sql = @"
                        SELECT b.Id, 
                            b.Title, 
                            p.Id, 
                            p.Title, 
                            p.PostContent, 
                            t.Id,
                            t.Name
                        FROM dbo.Blogs b
                            JOIN Posts p ON p.BlogId = b.Id
                            LEFT JOIN PostTags pt ON pt.PostId = p.Id
                            LEFT JOIN Tags t ON t.Id = pt.TagId
                        WHERE b.Id = 1
                            AND p.IsDeleted = 0;
                ";

    // this is not working
    var result = db.Blogs.FromSql(sql).ToList().FirstOrDefault(); 

    return Ok(result);
}

How I can map the result of the SQL script to the Blog object so that I can have the following result?

{
    "Blog": [
        {
            "Id": 1,
            "Title": "Another .NET Core Guy",
            "Posts": [
                {
                    "Id": 1,
                    "Title": "Post 1",
                    "PostContent": "Content 1 is about EF Core and Razor page",
                    "Tags": [
                        {
                            "Id": 1,
                            "Name": "Razor Page"
                        },
                        {
                            "Id": 2,
                            "Name": "EF Core"
                        }
                    ]
                },
                {
                    "Id": 2,
                    "Title": "Post 2",
                    "PostContent": "Content 2 is about Dapper",
                    "Tags": [
                        {
                            "Id": 3,
                            "Name": "Dapper"
                        }
                    ]
                },
                {
                    "Id": 4,
                    "Title": "Post 4",
                    "PostContent": "Content 4",
                    "Tags": [
                        {
                            "Id": 5,
                            "Name": "SqlKata"
                        }
                    ]
                }
            ]
        }
    ]
}

Update August 13, 2019:

EF Core does not support this kind of feature yet as it has been stated here on EF Core Github page https://github.com/aspnet/EntityFrameworkCore/issues/14525

Any particular reasons that you have to use raw SQL query, and not linq-to-entity? — Lowkey, Aug 12 '19 at 01:53
@Lowkey, no particular reason. It's because there is no option have filter in the "Inlucde" or "ThenInclude". See here https://github.com/aspnet/EntityFrameworkCore/issues/1833 — Tola, Aug 12 '19 at 06:06

score 2 · Answer 1 · answered Aug 12 '19 at 11:23

The result obtained by Fromsql is a flat relationship, not nested.If you insist on using this to get data with a nested relationship , there are two ways : 1. you could customize a sql script to implement ; 2. You could use Include method to load related data in EF Core and select the prorperties that you want with nested relationships and populate it with query results.

Here is a working demo on using Include method to load related data in EF Core , you could refer to :

There is a many-to-many relationship between Post model and Tag model , you should define them like below :

 public class Post
{
    public int Id { get; set; }
    public int BlogId { get; set; }
    public string Title { get; set; }
    public string PostContent { get; set; }
    public bool IsDeleted { get; set; }


    public  ICollection<PostTag> PostTags { get; set; }
}

public class PostTag
{
    //public int Id { get; set; }

    public int PostId { get; set; }
    public int TagId { get; set; }

    public virtual Post Post { get; set; }
    public virtual Tag Tag { get; set; }
}

 public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; }

    public bool IsDeleted { get; set; }

    public virtual ICollection<PostTag> PostTags { get; set; }
}

DbContext :

public class TestDbContext:DbContext
{
    public TestDbContext (DbContextOptions<TestDbContext> options):base(options)
    { }


    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
    public DbSet<Tag> Tags { get; set; }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<PostTag>()
        .HasKey(pt => new { pt.PostId, pt.TagId });

        modelBuilder.Entity<PostTag>()
            .HasOne(pt => pt.Post)
            .WithMany(p => p.PostTags)
            .HasForeignKey(pt => pt.PostId);

        modelBuilder.Entity<PostTag>()
            .HasOne(pt => pt.Tag)
            .WithMany(t => t.PostTags)
            .HasForeignKey(pt => pt.TagId);
    }
}

Controller :

[Route("posts/{blogId}")]
    [HttpGet]
    public async Task<IActionResult> GetBlogPosts(int blogId)
    {
        var blogs = db.Blogs
            .Where(b => b.Id == blogId)
            .Include(b => b.Posts)
                .ThenInclude(p => p.PostTags).ThenInclude(pt => pt.Tag)
            .Select(b=>new {
                Id=b.Id,
                Title=b.Title,
                Posts= b.Posts.Select(p => new {
                    Id=p.Id,
                    Title=p.Title,
                    PostContent=p.PostContent,
                    Tags =p.PostTags.Select(pt=> new {
                        Id=pt.Tag.Id,
                        Name=pt.Tag.Name,
                    })
                })
            });

        return Json(blogs);
    }

Reference :

https://learn.microsoft.com/en-us/ef/core/modeling/relationships#many-to-many

https://learn.microsoft.com/en-us/ef/core/querying/related-data

You are right the result from sql script is a flat dataset. I have been trying with some options. I think this is also has some limit with the Include filter or ThenInclude filter. If I need to filter out some columns, let's say I don't need Posts that is deleted andTags that is deleted. Also, this is having N+1 problem instead of one simple Select statement with some joints. In my test, it is producing 7 Select Statements as I have 5 posts, 6 tags. If I have more the Select Statement will be exponentially. — Tola, Aug 12 '19 at 14:03

score 0 · Answer 2 · answered Aug 11 '19 at 23:10

If the version of EF Core can be 2.1 or higher, you can use DbQuery<ClassName> ClassName in DbContext class. Then you can call var result = db.ClassName.FromSql(sql).ToList().FirstOrDefault(); or create view in database and then you can assign in OnModelCreating method to view.

Your class which you create should represent your view which you have. I don't know how ef core parses your SQL query with your sample model which contain a list and then next list. Probably you have to use aliases in SQL query like Blogs as B, Posts as b.Posts, but you have to try it and experiment.

More about DbQuey you can read in Microsoft documentation on https://learn.microsoft.com/en-us/ef/core/modeling/query-types

yes I checked Query Type before I posted the question but doc is very limited in showing the example how to compose what I needed. — Tola, Aug 12 '19 at 06:08

score 0 · Answer 3 · answered Aug 12 '19 at 08:51

wanted to write this as comment, but since i am new here i cannot do that:

EDIT: i wrote this answer just because you said that you have to write it in plain SQL due to the behaviour of .Include() and .ThenInclude()

you don't need .Include() or .ThenInclude() statements if you select everything and don't need it in the further process (just like you did, select and don't do anything with it further).

one way i see would be do two select without linq by adding the entity as navigationproperty and doing two db requests like this:

public partial class Post
{

    public int Id { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
    public string Title { get; set; }
    public string PostContent { get; set; }

    public virtual ICollection<PostTag> PostTags { get; set; }
}

var result = await db.Posts.Where(x => x.BlogId == blogId && x.Blog. ... whatever you may also need).Select(x => whatever you need from here).ToArrayAsync()

and another query for the other entities like above and just join into a new entity

or do it with linq like shown in ASP.NET Core & EntityFramework Core: Left (Outer) Join in Linq

hope this seems reasonable

Thanks for the suggestion. Yes, it can be done is several ways, one way is to do the inverse-selection from the Post first. I'm wondering if there is a solution to map the result straight from the sql script in EF Core. I know this is can be done with Dapper + Slapper.Mapper but it requires me to change the SQL script which something I don't have full control over https://stackoverflow.com/questions/9350467/how-do-i-write-one-to-many-query-in-dapper-net — Tola, Aug 12 '19 at 10:29

score 0 · Answer 4 · answered Aug 13 '19 at 01:19

Given you want an optimised query, and also the need to do additional where if required, you'll probably have to rely on linq-to-sql style to construct your query, and do the mapping to the desired results yourself:

var query = (
from b in db.Blogs
join p in db.Posts on b.Id equals p.BlogId
from pt in db.PostTag.Where(posttag => posttag.PostId == p.Id).DefaultIfEmpty() //LEFT OUTER JOIN
from t in db.Tags.Where(tag => tag.Id == pt.TagId).DefaultIfEmpty() //LEFT OUTER JOIN
where (...) //Your additional conditions
select new 
{
    BlogId = b.Id,
    BlogTitle = b.Title,
    PostId = p.Id,
    PostTitle = p.Title,
    p.PostContent,
    TagId = (int?) t.Id,
    TagName = t.Name
}).ToList();

From here on, you can either write the GroupBy statements yourself, or use some plugins.

Yes, at moment EF Core does not support the feature I needed as it has been stated here https://github.com/aspnet/EntityFrameworkCore/issues/14525 — Tola, Aug 13 '19 at 14:35

Entity Framework Core one to many mapping from SQL

4 Answers4