How to get the dependent table data by using stored procedures ?

Question

Hi,

Previously , When we were using EF6 in Asp.Net MVC 5 ,
We were using this approach to get the detail of Franchise_OpeningHours.
It will bring all the data of FranchiseOpeningHours as well as it dependent/child data.

See in the code example:

 public Franchise_OpeningHours OpeningHours(int id)
        {
            return _db.Franchise_OpeningHours.FirstOrDefault(f => f.Franchise_Id == id);
        }

Franchise_OpeningHours get the data of Franchise_WeekPlan from the above statement.

So It seems to fine for the above statement.

Now I am using ASP.NET MVC CORE 6 with EF Core 6 but I am not using Entities now. I am using Stored Procedures. Now What Do you suggest me to achieve the similar as above.

Please see my code

These are my models

public class Franchise_OpeningHours
    {
        [Key]
        public int Id { get; set; }
        public int Franchise_Id { get; set; }
        public Nullable<bool> Weekend1Span { get; set; }
        public string? Weekend1SpanFrom { get; set; }
        public string? Weekend1SpanTo { get; set; }
        public Nullable<System.TimeSpan> Weekend1SpanStart { get; set; }
        public Nullable<System.TimeSpan> Weekend1SpanEnd { get; set; }
        public Nullable<bool> Weekend2Span { get; set; }
        public string? Weekend2SpanFrom { get; set; }
        public string? Weekend2SpanTo { get; set; }
        public Nullable<System.TimeSpan> Weekend2SpanStart { get; set; }
        public Nullable<System.TimeSpan> Weekend2SpanEnd { get; set; }
        public bool FriSurcharge { get; set; }
        public System.TimeSpan FriSurchargeStart { get; set; }
        public System.TimeSpan FriSurchargeEnd { get; set; }
        public decimal FriCarMpv { get; set; }
        public decimal FriVanMiniBus { get; set; }
        public bool SatSurcharge { get; set; }
        public System.TimeSpan SatSurchargeStart { get; set; }
        public System.TimeSpan SatSurchargeEnd { get; set; }
        public decimal SatCarMpv { get; set; }
        public decimal SatVanMiniBus { get; set; }
        public bool SunSurcharge { get; set; }
        public System.TimeSpan SunSurchargeStart { get; set; }
        public System.TimeSpan SunSurchargeEnd { get; set; }
        public decimal SunCarMpv { get; set; }
        public decimal SunVanMiniBus { get; set; }
        public Nullable<int> CreatedBy { get; set; }
        public Nullable<System.DateTime> CreatedOn { get; set; }
        public Nullable<int> ModifiedBy { get; set; }
        public Nullable<System.DateTime> ModifiedOn { get; set; }        
        public virtual ICollection<Franchise_WeekPlan>? Franchise_WeekPlan { get; set; }
        //public virtual Franchise Franchise { get; set; }
    }

 public class Franchise_WeekPlan
    {
        public int Id { get; set; }
        public int OpeningHours_Id { get; set; }
        public string? Day { get; set; }
        public bool Collections { get; set; }
        public System.TimeSpan CollectionStart { get; set; }
        public System.TimeSpan CollectionEnd { get; set; }
        public bool Returns { get; set; }
        public System.TimeSpan ReturnStart { get; set; }
        public System.TimeSpan ReturnEnd { get; set; }
        public decimal OutOfHours { get; set; }
        public bool OutOfHoursAm { get; set; }
        public System.TimeSpan OutOfHoursAmStart { get; set; }
        public System.TimeSpan OutOfHoursAmEnd { get; set; }
        public bool OutOfHoursPm { get; set; }
        public System.TimeSpan OutOfHoursPmStart { get; set; }
        public System.TimeSpan OutOfHoursPmEnd { get; set; }

        public virtual Franchise_OpeningHours? Franchise_OpeningHours { get; set; }
    }

This is my dbcontext.cs

namespace CarRentalWidget.BLL.DB
{
    public class CarRentalContext : DbContext
    {       

        public CarRentalContext(DbContextOptions<CarRentalContext> options)
           : base(options)
        {
        }

        public DbSet<FranchiseWebInquiry>? FranchiseWebInquiries { get; set; }

        public virtual DbSet<Franchise>? Franchises { get; set; }
        public virtual DbSet<Franchise_BankHolidays>? Franchise_BankHolidays { get; set; }



        public virtual Task<Franchise_OpeningHours> GetOpeningHours(int franchiseId)
        {
            SqlParameter[] param = new SqlParameter[] {
                 new SqlParameter() {
                            ParameterName = "@franchiseId",
                            SqlDbType =  System.Data.SqlDbType.Int,
                            //Size = 100,
                            Direction = System.Data.ParameterDirection.Input,
                            Value = franchiseId
                        } };

            return this.Set<Franchise_OpeningHours>()
                .FromSqlRaw("EXECUTE dbo.GetOpeningHoursByFranchiseId @franchiseId ", param)
                        .AsNoTracking()
                        .FirstOrDefaultAsync();
        }
    }
}

This is the simple stored procedure which don't have any columns of Franchise_WeekPlan table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetOpeningHoursByFranchiseId]
 -- Add the parameters for the stored procedure here
 @franchiseId int = NULL

AS
BEGIN
 -- SET NOCOUNT ON added to prevent result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 SELECT foh.* 
 FROM Franchise_OpeningHours (nolock) foh
 Where foh.Franchise_Id = @franchiseId;

END

How can I achieve from it ?

Answer 1

Yes, I have done this but what would be the performance issues ?

public virtual Task<Franchise_OpeningHours?> GetOpeningHours(int franchiseId)
        {
            SqlParameter[] param = new SqlParameter[] {
                 new SqlParameter() {
                            ParameterName = "@franchiseId",
                            SqlDbType =  System.Data.SqlDbType.Int,
                            //Size = 100,
                            Direction = System.Data.ParameterDirection.Input,
                            Value = franchiseId
                        } };         

            return this.Set<Franchise_OpeningHours>()
                .FromSqlInterpolated($"SELECT *  FROM Franchise_OpeningHours(nolock) foh Where foh.Franchise_Id = {franchiseId}")
                        .Include(b => b.Franchise_WeekPlan)
                        .AsNoTracking()
                        .FirstOrDefaultAsync();

        }


 public class Franchise_WeekPlan
    {
        public int Id { get; set; }


        public int OpeningHours_Id { get; set; }
        public string? Day { get; set; }
        public bool Collections { get; set; }
        public System.TimeSpan CollectionStart { get; set; }
        public System.TimeSpan CollectionEnd { get; set; }
        public bool Returns { get; set; }
        public System.TimeSpan ReturnStart { get; set; }
        public System.TimeSpan ReturnEnd { get; set; }
        public decimal OutOfHours { get; set; }
        public bool OutOfHoursAm { get; set; }
        public System.TimeSpan OutOfHoursAmStart { get; set; }
        public System.TimeSpan OutOfHoursAmEnd { get; set; }
        public bool OutOfHoursPm { get; set; }
        public System.TimeSpan OutOfHoursPmStart { get; set; }
        public System.TimeSpan OutOfHoursPmEnd { get; set; }
        [ForeignKey("OpeningHours_Id")]
        public virtual Franchise_OpeningHours? Franchise_OpeningHours { get; set; }
    }

I did following changes and It started to bring the dependent table data.

Can I improve my logic in best best way ?

Thank you