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

Posted on 2014-08-29
Last Modified: 2016-02-16
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
                                .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.
Question by:dyarosh
    LVL 82

    Accepted Solution

    In SQL, Order By works on the result set so the items in the Order By clause must be part of the result set.

    Author Closing Comment

    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.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now