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; }

    }