Best practice for joining display properties in Entity Framework 7

Question

We've recently made the switch over to EF Core at our company, and for the last 20+ years we've used stored procedures for our ORM.

I know EF core generates queries that run at the database level rather than enumerating through the data within memory.

I'm not totally sure how to ask this question so let me give you some scenarios!

These code snippets are trimmed down and extracted from a large customer system.

Scenario 1 (the old way):

Take the object 'Membership_InsuranceExtension'. This stores the relationship between a Membership and an InsuranceExtension. Static data regarding the Insurance Extension is stored in another table, named 'tbl_MembershipLevel_InsuranceExtension'

When there is a need to fetch a large list of an object (can be thousands+ of rows returned in the same result set). We create an extra class/object called xxxx_SummaryItem. This contains properties such as StatusName and InsuranceExtensionName which need to be joined... A 'trimmed down' version of the object used purely for display purposes.

This works well, and is pretty fast but there doesn't seem to be a 'native' way of doing this in EF Core without loading the entire 'child object' (unless I am missing something!)

Example stored procedure below:

CREATE PROCEDURE [dbo].[usp_Membership_InsuranceExtension_FetchSummaryList] 
      @MembershipID             uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;

SELECT
    tbl_Membership_InsuranceExtension.MembershipInsuranceExtensionID
  , tbl_Membership_InsuranceExtension.Created
  , tbl_Membership_InsuranceExtension.LastUpdated

  , tbl_Membership_InsuranceExtension.MembershipID
  , tbl_Membership_InsuranceExtension.StatusID
  , tbl_Membership_InsuranceExtension_Status.[Name] AS StatusName

  , tbl_Membership_InsuranceExtension.InsuranceExtensionID
  , tbl_MembershipLevel_InsuranceExtension.[Name] AS InsuranceExtensionName
  , tbl_Membership_InsuranceExtension.ValidFrom
  , tbl_Membership_InsuranceExtension.ValidTo

FROM
    tbl_Membership_InsuranceExtension
    LEFT OUTER JOIN tbl_MembershipLevel_InsuranceExtension ON tbl_Membership_InsuranceExtension.InsuranceExtensionID = tbl_MembershipLevel_InsuranceExtension.InsuranceExtensionID 
    LEFT OUTER JOIN tbl_Membership_InsuranceExtension_Status ON tbl_Membership_InsuranceExtension.StatusID = tbl_Membership_InsuranceExtension_Status.InsuranceExtensionStatusID 


WHERE
    tbl_Membership_InsuranceExtension.MembershipID = @MembershipID

ORDER BY 
    tbl_Membership_InsuranceExtension.Created

Scenario 2 (the EF Core way)

I guess this is less of a scenario and more of a question, but what is the best way to recreate this behaviour within EF without loading the full object (MembershipLevel_InsuranceExtension) in this case for every row returned by the query?

Entities (for example)

BaseEntity contains the PK, Created/LastUpdated fields

Child entities are currently being eager loaded with the .Include method in my repository.

    public class Membership : BaseEntity
    {

        public Membership() : base()
        {
            ValidFrom = DateTime.Today.Date;
            ValidTo = ValidFrom.AddYears(1).AddDays(-1);

        }

        public virtual List<Membership_InsuranceExtension> InsuranceExtensions { get; set; }

        public DateTime ValidFrom { get; set; }
        public DateTime ValidTo { get; set; }


    }


    public class Membership_InsuranceExtension : BaseEntity
    {

        public enum InsuranceExtensionStatusType
        {
            Unknown = 0,
            Unverified = 1,
            Verified = 2,
            Rejected = 3,
            Cancelled = 4,
            Deleted = 100
        }


        public Membership_InsuranceExtension(Membership membership) : base()
        {
            Status = InsuranceExtensionStatusType.Unverified;

            Membership = membership;

            ValidFrom = membership.ValidFrom;
            ValidTo = membership.ValidTo;

        }

        public Guid MembershipID { get; set; }
        public virtual Membership Membership { get; set; }


        [Column("StatusID")]
        public InsuranceExtensionStatusType Status { get; set; }

        public Guid InsuranceExtensionID { get; set; }
        public virtual MembershipLevel_InsuranceExtension InsuranceExtension { get; set; }

        public DateTime ValidFrom { get; set; }
        public DateTime ValidTo { get; set; }


    }



    public class MembershipLevel_InsuranceExtension : BaseEntity
    {



        public MembershipLevel_InsuranceExtension() : base()
        {
            Name = String.Empty;
            Description = String.Empty;

        }

        public string Name { get; set; }
        public string Description { get; set; }


    }

Answer 1

You can create helper class with extension methods which mimic your current functionality. Note that query can be inaccurate because no all fileds amd vagigations present:

public static partial class DatabaseFunctions
{

    public class MembershipInsuranceExtensionResult
    {
        public int MembershipInsuranceExtensionID { get; set; }
        public DateTime Created { get; set; }
        public DateTime LastUpdated { get; set; }
        public int MembershipID { get; set; }
        public int StatusID { get; set; }
        public string StatusName { get; set; }
        public int InsuranceExtensionID { get; set; }
        public string InsuranceExtensionName { get; set; }
        public DateTime ValidFrom { get; set; }
        public DateTime ValidTo { get; set; }
    }
    
    public static IQueryable<MembershipInsuranceExtensionResult> usp_Membership_InsuranceExtension_FetchSummaryList(this MyDbContext context, Guid membershipID)
    {
        var query = 
            from insuranceExtension in context.MembershipInsuranceExtension
            join insuranceStatus in context.MembershipInsuranceExtensionStatus on insuranceExtension.StatusID equals insuranceStatus.InsuranceExtensionStatusID into insuranceStatusJoin
            from insuranceStatus in insuranceStatusJoin.DefaultIfEmpty()
            where insuranceExtension.MembershipID == membershipID
            orderby insuranceExtension.Created
            select new MembershipInsuranceExtensionResult
            {
                MembershipInsuranceExtensionID = insuranceExtension.MembershipInsuranceExtensionID,
                Created = insuranceExtension.Created,
                LastUpdated = insuranceExtension.LastUpdated,
                MembershipID = insuranceExtension.MembershipID,
                StatusID = insuranceExtension.StatusID,
                StatusName = insuranceStatus.Name,
                InsuranceExtensionID = insuranceExtension.InsuranceExtensionID,
                InsuranceExtensionName =  insuranceExtension.InsuranceExtension.Name,
                ValidFrom = insuranceExtension.ValidFrom,
                ValidTo = insuranceExtension.ValidTo
            };
        return query;        
    }
}

In your code, just call:

var result = await context.usp_Membership_InsuranceExtension_FetchSummaryList(someId)
    .ToListAsync();

It also gives another benefits, you can manipulate with query:

var query = context.usp_Membership_InsuranceExtension_FetchSummaryList(someId);

// add additional filter
var query = query.Where(x => x.Created > DateTime.Now.AddDays(-1));

// get only 10 records
var query = query.Take(10);

// finally execute query with applied filter and records limitation
var result = await query.ToListAsync();

EF Core should generate near optimal query without retrieving full objects from database.