Entity Framework Core one to many mapping from SQL

asp.net-core-2.0 c# entity-framework-core

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

1
0
8/18/2019 5:12:08 PM

Popular Answer

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.

0
8/13/2019 1:19:02 AM


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