none
How to Create One to Mapping in relation to Stored Procedure RRS feed

  • Question

  • Goal:
    Make a one to many mapping by using Stored procedure

    Problem:
    How do you create a one to many mapping in relation to 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
    *Using Entity framewor 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


    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]


    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

    Tuesday, July 30, 2019 10:26 PM

All replies

  • Goal:
    Make a one to many mapping by using Stored procedure

    Problem:
    How do you create a one to many mapping in relation to 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
    *Using Entity framewor 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
    
    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]

    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

    Wednesday, July 31, 2019 7:31 AM
  • You should consider what EF is doing in the tutorial  before you try to do something with a Stored Procedure.

    You can get help with EF Core in the EF forum.

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework

    You have an ASP.NET tutorial, and you can get help in the ASP.NET forums too.

    http://forums.asp.net/


    Wednesday, July 31, 2019 8:55 AM
  • Hi,

    To achieve One-to-Many relationship in EF Core, here are the articles you can refer to.

    Configuring One To Many Relationships in Entity Framework Core,

    One-to-Many Relationship Conventions in Entity Framework Core,

    And the official document about "Relationships".

    As to "Stored Procedure" in EF Core, you can refer to this,

    Working with Stored Procedure in Entity Framework Core.

    Regards,

    Kyle

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 31, 2019 9:59 AM
    Moderator
  • Hello,

    My recommendation is not to use stored procedures.

    There are some limitations on the execution of database stored procedures using FromSql or ExecuteSqlCommand methods in EF Core2:

    https://www.entityframeworktutorial.net/efcore/working-with-stored-procedure-in-ef-core.aspx

    • Result must be an entity type. This means that a stored procedure must return all the columns of the corresponding table of an entity.
    • Result cannot contain related data. This means that a stored procedure cannot perform JOINs to formulate the result.
    • Insert, Update and Delete procedures cannot be mapped with the entity, so the SaveChanges method cannot call stored procedures for CUD operations.

    Unless there is a good reason to use SP and to fully understand the above stick with conventional EF Core. And to broaden your perspective follow the link DA924x provided.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, July 31, 2019 10:58 AM
  • Hi Sakura Data,

    Thank you for posting here.

    Since this thread is duplicated with the following link, I will merge it.

    https://social.msdn.microsoft.com/Forums/en-US/ed43d741-bcf1-459c-b8cd-02e9afa8d61f/how-to-create-one-to-mapping-in-relation-to-stored-procedure?forum=adodotnetentityframework

    The Visual C# forum discusses and asks questions about the C# programming language, IDE, libraries, samples, and tools.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 1, 2019 2:24 AM