[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

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.
0
dyarosh
Asked:
dyarosh
1 Solution
 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now