Link to home
Start Free TrialLog in
Avatar of dyarosh
dyarosh

asked on

How can I OrderBy in my Linq query on a property from an Included table?

I have the following Linq query that I need to add an OrderBy clause to that I can't get to work.  I am using EF.
var dbRecords = _db.REF_Referrals
                                .Include("REF_ReferralStatus")
                                .Where(r => r.REF_ReferralTypeReferralTypeID == referraltypeid && r.ReferralsKeyString == key);

Open in new window


Here are the relevant classes:
    public partial class REF_Referrals
    {
        public REF_Referrals()
        {
            this.REF_ReferralStatus = new HashSet<REF_ReferralStatus>();
        }
    
        public int ReferralsID { get; set; }
        public string ReferralsSynopsis { get; set; }
        public int ReferralsRequesterProfileID { get; set; }
        public System.DateTime ReferralsSubmittedDate { get; set; }
        public int REF_ReferralTypeReferralTypeID { get; set; }
        public System.DateTime LastUpdateTimeStamp { get; set; }
        public string ReferralsKeyString { get; set; }
    
        public virtual REF_ReferralType REF_ReferralType { get; set; }
        public virtual ICollection<REF_ReferralStatus> REF_ReferralStatus { get; set; }
    }

    public partial class REF_ReferralStatus
    {
        public int ReferralStatusID { get; set; }
        public int ReferralStatusRequesterProfileID { get; set; }
        public int REF_StatusStatusID { get; set; }
        public bool ReferralStatusIsCurrentStatus { get; set; }
        public System.DateTime ReferralStatusUpdateDate { get; set; }
        public System.DateTime LastUpdateTimeStamp { get; set; }
        public int REF_ReferralsReferralsID { get; set; }
    
        public virtual REF_Status REF_Status { get; set; }
        public virtual REF_Referrals REF_Referrals { get; set; }
    }

    public partial class REF_Status
    {
        public REF_Status()
        {
            this.REF_ReferralStatus = new HashSet<REF_ReferralStatus>();
        }
    
        public int StatusID { get; set; }
        public string Status { get; set; }
        public System.DateTime LastUpdateTimeStamp { get; set; }
        public int Priority { get; set; }
    
        public virtual ICollection<REF_ReferralStatus> REF_ReferralStatus { get; set; }
    }

Open in new window


I need to sort the referrals by the Referral Status Priority of the ReferralStatus record IsCurrentStatus == true .  The problem I have is by Including the ReferralStatus it retrieves all ReferralStatus records for a referral.

1. How can I retrieve just the ReferralStatus record whose property IsCurrentStatus is true?
2. How can I put an OrderBy clause so the Referrals are sorted by Status Priority, Status ID, Date Submitted?

Any help is greatly appreciated.
ASKER CERTIFIED SOLUTION
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dyarosh
dyarosh

ASKER

You explained why I can't sort the way I want but didn't provide any solution for retrieving the data in the sort order I want.  I ended up writing a sort method to sort the data once I retrieved it.