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.
dyaroshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
In SQL, Order By works on the result set so the items in the Order By clause must be part of the result set.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dyaroshAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.