Stack Exchange
How to return multiple SELECT sets from a stored procedure using FromSqlRaw Entity Framework Core 3.1
I am currently having an issue with my returning values from the DB, I am using a SQL Server stored procedure and ASP.NET Core 3.1 to retrieve several SELECT sets.
Here's my stored procedure:
ALTER PROCEDURE [dbo].[MyProc]
    @BusinessModelID int
AS
BEGIN
    SELECT Id, Title 
    FROM [dbo].[Channels] 
    WHERE BusinessModelId = @BusinessModelID;

    SELECT Id, Title 
    FROM [dbo].[Problems] 
    WHERE BusinessModelId = @BusinessModelID;

    SELECT Id, Title 
    FROM [dbo].[CostStructures] 
    WHERE BusinessModelId = @BusinessModelID;

    SELECT Id, Title 
    FROM [dbo].[CustomerSegments] 
    WHERE BusinessModelId = @BusinessModelID;

    SELECT Id, Title 
    FROM [dbo].[KeyMetrics] 
    WHERE BusinessModelId = @BusinessModelID;

    SELECT Id, Title 
    FROM [dbo].[RevenueStreams] 
    WHERE BusinessModelId = @BusinessModelID;

    SELECT Id, Title 
    FROM [dbo].[Solutions] 
    WHERE BusinessModelId = @BusinessModelID;

    SELECT Id, Title 
    FROM [dbo].[Unfairs] 
    WHERE BusinessModelId = @BusinessModelID;

    SELECT Id, Title 
    FROM [dbo].[Values] 
    WHERE BusinessModelId = @BusinessModelID;
END

And here's my C# code:
var modelList = _dataContext.Set<BusinessToGet>()
                        .FromSqlRaw($"EXECUTE MyProc @BusinessModelID={businessModelId}")
                        .ToList();

The problem is that it should return several multiple sets but it is only returning one the Channels. What am I doing wrong?
Thanks in advance.

Update
In this case, there was duplicate primary keys across tables. Even though SQL below worked well in SQL-server, EF Core returned a lot of duplicate instances due to the fact that when EF Core sees an instance with Id = 1, it uses that for all sub-sequent instances with Id = 1.
A work-around is to use a keyless DTO for FromSqlRaw queries.
[Keyless]
public class BlogPostsCount
{
Id, 
    public int Id { get; set; }
    public string Title { get; set; }
}

https://learn.microsoft.com/en-us/ef/core/modeling/keyless-entity-types?tabs=data-annotations

Using UNION should solve this issue. If datatypes varies between tables, you may need to cast column values as well in order to make a uniform result. If you have duplicates across tables, and needs all, use UNION ALL.
SELECT Id, Title FROM [dbo].[Channels] WHERE BusinessModelId = @BusinessModelID
UNION
SELECT Id,Title FROM [dbo].[Problems] WHERE BusinessModelId = @BusinessModelID
UNION
SELECT Id, Title FROM [dbo].[CostStructures] WHERE BusinessModelId = @BusinessModelID
UNION
SELECT Id, Title FROM [dbo].[CustomerSegments] WHERE BusinessModelId = @BusinessModelID
UNION
SELECT Id, Title FROM [dbo].[KeyMetrics] WHERE BusinessModelId = @BusinessModelID
UNION
SELECT Id, Title FROM [dbo].[RevenueStreams] WHERE BusinessModelId = @BusinessModelID
UNION
SELECT Id, Title FROM [dbo].[Solutions] WHERE BusinessModelId = @BusinessModelID
UNION
SELECT Id, Title FROM [dbo].[Unfairs] WHERE BusinessModelId = @BusinessModelID
UNION
SELECT Id, Title FROM [dbo].[Values] WHERE BusinessModelId = @BusinessModelID


I tried with the UNION ALL, but it seems it returns the duplicates anyway, it returns the right number of rows, but not the right data when It gets to the C# code. :(

@RodolfoA.Calvo: UNION ALL will return duplicates, if any. UNION performs implicit a select distinct. Could you be more specific about the problem you experience? BR

For sure! You solution works perfectly in SQL server, but what happens now that when I debug my C# code the data comes with only one result as duplicate but the right number, example: In SQL the results are 1 - My channel 1 - My problem 1- Cost Structure ... and so on which is correct but in C# the results are 1 - My channel 1 - My channel 1 - My channel 1 - My channel 1 - My channel. And I did this in the SQL SP SELECT DISTINCT * FROM [dbo].[Channels] WHERE BusinessModelId = @BusinessModelID UNION ALL for all of them

I think you have to add TableName  property  too in order to know who is who. And add to each select query " 'Chanels' as a TableName" for example

@Serge: I was looking into this related issue that seems to work without any annotations: https://github.com/dotnet/efcore/issues/24078 BR
