How to Create One to Many Mapping in relation to stored procedure

[jt4000]

Goal:
Create a one to many mapping by using stored procedure GetData
In other words, call the SP GetData and retrieve the data. And then the data should be added in the class Blog and class Post.
The class Blog contain a data member that is List. Based on the picture (sql code result) this specific blog has two data that is Post. In the end it will be a One to Many Mapping. A blog has many Post.

Problem:
how do you create a one to many mapping in relation to a stored procedure? One Blog has many [Post]

Info:
the source code is from this page: https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/existing-db?toc=%2Faspnet%2Fcore%2Ftoc.json&bc=%2Faspnet%2Fcore%2Fbreadcrumb%2Ftoc.json&view=aspnetcore-2.2

I'm using Entity Framework Core 2 and ASP.NET Core.

CREATE DATABASE [Blogging];
GO

USE [Blogging];
GO

CREATE TABLE [Blog] 
(
    [BlogId] INT NOT NULL IDENTITY,
    [Url] NVARCHAR(MAX) NOT NULL,

    CONSTRAINT [PK_Blog] PRIMARY KEY ([BlogId])
);
GO

CREATE TABLE [Post] 
(
    [PostId] INT NOT NULL IDENTITY,
    [BlogId] INT NOT NULL,
    [Content] NVARCHAR(MAX),
    [Title] NVARCHAR(MAX),

    CONSTRAINT [PK_Post] PRIMARY KEY ([PostId]),

    CONSTRAINT [FK_Post_Blog_BlogId] 
        FOREIGN KEY ([BlogId]) REFERENCES [Blog] ([BlogId]) 
                ON DELETE CASCADE
);
GO

INSERT INTO [Blog] (Url) 
VALUES ('http://blogs.msdn.com/dotnet'),
       ('http://blogs.msdn.com/webdev'),
       ('http://blogs.msdn.com/visualstudio')
GO

INSERT INTO [Post] ([BlogId], [Content], [Title]) 
VALUES (1, 'aa', 'aa'),
       (1, 'bb', 'bb')
GO

CREATE PROCEDURE GetData
AS  
BEGIN  

SELECT
    a.[BlogId],
    a.[Url],
    b.[BlogId],
    b.[PostId],
    b.[Content],
    b.[Title]
FROM 
    [Blogging].[dbo].[Blog] a
INNER JOIN  
    [Blogging].[dbo].[Post] b ON a.[BlogId] = b.[BlogId]

END  
GO

Using C# code from

https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/existing-db?toc=%2Faspnet%2Fcore%2Ftoc.json&bc=%2Faspnet%2Fcore%2Fbreadcrumb%2Ftoc.json&view=aspnetcore-2.2

[ajcvickers]

@jt4000 Can you explain more what you mean by, "using stored procedure?"

[jt4000]

@jt4000 Can you explain more what you mean by, "using stored procedure?"

Hi ajcvickers, I have updated the content.

[ajcvickers]

@jt4000 Do you want to be able to call the GetData procedure and get results back? If so, see https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

[jt4000]

@jt4000 Do you want to be able to call the GetData procedure and get results back? If so, see https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

Call the SP GetData and retrieve the data. And then the data should be added in the class Blog and class Post.

The class Blog contain a data member that is List. Based on the picture (sql code result) this specific blog has two data that is Post. In the end it will be a One to Many Mapping. A blog has many Post.

[ajcvickers]

@jt4000 I'm still having trouble understanding what you want to do. Could you post the code you have to do this so far, and indicate where it's not doing what you want?

[tola]

@jt4000 @ajcvickers

Let's say I have this simple Blog database structure with 4 tables:

and some sample data in each table looks like this:

Blogs table:

Posts table:

Tags 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 Store 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 function 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;
                ";

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

        return Ok(result);
    }

How I can map the result of 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"
                        }
                    ]
                }
            ]
        }
    ]
}

[ajcvickers]

@tola So you want to take a custom flat result set like this:

SELECT b.Id, b.Title, p.Id, p.Title, p.PostContent, t.Id,t.Name

and have EF materialize a graph of entities? If so, EF can't do that. It can only materialize into types that match your result set.

[tola]

@ajcvickers I see. It's clear now. I have been trying many ways but no good result.

I know that in Dapper that's a splitOn option which is to tell Dapper how to split the flat dataset and map it to a model.

Also, there's an Sapper.Dapper solution for Dapper to handle this kind of mapping data set with objects.

Often time, I work with SQL script or Stored Procedure that were given to use in the code from DB admin and I do not have much control of them. If possible, I'd like to use EF Core only without having to add another micro ORM to the project.

Is there any other way that I can implement custom mapping solution to this. If possible, I'd like to see some features of Sapper.Dapper in EF Core in the future.

I'm sure some of other developers are dealing with Stored Procedure, View and SQL Script quite often as well. It's just time consuming to go back to ADO.NET and map the sql result manually.

[smitpatel]

Related #14525

[ajcvickers]

Closing as a duplicate of #14525 with a note added there to consider this specific scenario.