LEFT JOINs are not mapping in EFCore 6

Question

I have the following query in an SP. When it gets executed 3 records should be returned. However, EF returns only the 1st record 3 times. Please point out the issue here. Thank you.

SELECT que.QuestionId, que.Question, que.QuestionType, que.IsMandatoryQuestion, que.[File],
       que.QuesVI, ans.AnswerId, ans.AnswerLabel, ans.AnsLblVI
FROM Questions AS que
  LEFT JOIN DefinedAnswers As ans ON que.QuestionId = ans.QuestionId
WHERE que.SectionId = 5
ORDER BY que.Sequence, ans.Sequence

When this gets executed via EFCore (V6.0) on a .NET6 web API project a different result appears.

Please can someone show how to fix this issue?

NOTE: The full SP code is as follows:

ALTER PROCEDURE[dbo].[sp_GetQuestionsAnswers]
  @SectionId INT
  AS
  BEGIN
  SET NOCOUNT ON;
 
 SELECT que.QuestionId, que.Question, que.QuestionType, que.IsMandatoryQuestion, que.[File], que.QuesVI,
 ans.AnswerId, ans.AnswerLabel, ans.AnsLblVI
 FROM Questions AS que
 LEFT JOIN DefinedAnswers As ans ON que.QuestionId = ans.QuestionId
 WHERE que.SectionId = @SectionId
 ORDER BY que.Sequence , ans.Sequence
 END
GO

Mapping model:

public partial class QuestionAnswers
{
    [Key]
    public int QuestionId { get; set; }
    public int? AnswerId { get; set; }
    public string Question { get; set; }
    public byte QuestionType { get; set; }
    public bool IsMandatoryQuestion { get; set; }
    public byte[] File { get; set; }
    public byte QuesVi { get; set; }

    public string AnswerLabel { get; set; }
    public byte? AnsLblVi { get; set; }
}

Answer 1

As I can see, the order of the columns returned by the query don't match the same order in your mapping model. Everything including order, names, types should align. I'd first do that.

Second - I have no idea how you have defined your dbSet but I would do it like this: modelBuilder.Entity<YourType>().HasNoKey().ToFunction("[dbo].[YourStoredProcedureName]").Metadata.SetIsTableExcludedFromMigrations(true);. This basically tells EF Core that you want to execute a query against a SQL entity that doesn't have a PK and also exclude it from migrations (aka creating the table).

Third - I would use .FromSqlInterpolated($"EXEC [dbo].[YourStoredProcedureName] @param= {param}").ToListAsync(); rather than FromSqlRaw as it prevents SQL injection, it's more readable and handles parameterization correctly.