EF Core use stored procedure to load related entities

Question

I have a data model like this:

public class Parent {
  public int ID {get; set;}
  public string Name {get; set;}
  public int ChildID {get; set;}
  public Child Child {get; set;}
}

public class Child {
  public int ID {get; set;}
  public string Name {get; set;}
  public string AnotherProp {get; set;}
}

I also created a stored procedure to load the data for performance reasons:

CREATE PROCEDURE dbo.GetAllParents
AS
BEGIN
 SELECT p.ID, p.Name, p.ChildID, c.ID, c.Name
 FROM dbo.Parent p
 INNER JOIN dbo.Child c on c.ID = p.Child.ID
END

And I use EF Core to call it and load all Parents with their Child:

var parents = await _context.Parent.FromSqlRaw("exec GetAllParents").AsNoTracking().ToListAsync();

This works fine but it doesn't load the related data e.g. the child property. Is there a way to tell EF how to map it columns to the related entities?

Thanks!

Answer 1

As SvyatoslavDanyliv suggested it needs less code to use EF classes. But if you need to use stored procedure you will have to add some more code.

Change the SP

CREATE PROCEDURE dbo.GetAllParents
AS
BEGIN
 SELECT p.ID as ParentId, p.Name as ParentName, c.ID as ChilId, c.Name as ChildName
 FROM dbo.Parent p
 INNER JOIN dbo.Child c on c.ID = p.ChildID
END

and create a class for a sp result

public class SpResult
{
public int ParentId {get;set;}
public string ParentName {get; set;}
public int ChilId {get;set;}
public string ChildName {get; set;}
}

add to db context

 public virtual DbSet<SpResult> SpResults { get; set; }

 modelBuilder.Entity<SpResult>(e =>
   {
  e.HasNoKey();
   });

and finally code

var spResult = await _context.SpResults.FromSqlRaw("exec GetAllParents").ToListAsync();

var parents=sp.Result.Select( i=> new Parent 
{
 ID = i.ParentId,
 Name =i.ParentName,
 ChildID =i.ChildId,
 Child= new  Child { ID= i.ChildId, Name=i.ChildName}
}).ToList();