title: Linq query for prioirty based email messages
body: <p>I have a email message table which will have email messages and then recipient table will have recipients of that email messages</p>
  
  <p>EmailMessage</p>
  
  <p>Id</p>
  
  <p>Content</p>
  
  <p>Priority</p>
  
  <p>MessageRecipient</p>
  
  <p>EmailMessageFk</p>
  
  <p>RecipientEmailId</p>
  
  <p>LastError</p>
  
  <p>LastTriedAt</p>
  
  <p>NextTryAt</p>
  
  <p>Each message can have 1 to 10 priority where 1 is the lowest and 10 is the highest. I have created a function in C# which will try to resend all the pending email messages after certain interval.</p>
  
  <p>I want to write a single linq query that can run on DB and will return most eligible record according to following criteria</p>
  
  <p>a) If any of the message recipient has been last tried to be sent 12 hours before then it should that email message</p>
  
  <p>b) if there is no recipient is found according (a) criterion then it should return the email message with the highest priority and if there are multiple email messages with the highest priority then it should return the message which has the highest priority and the lowest LastTriedAt date time.</p>
  
  <p>Please remember, i want only one linq query to fulfill all these requirements</p>
  
  <p>I was using following Query</p>
  
  <pre><code> entities.EmailMessageRecipients.Where(
                              p =&gt; p.NextAttemptAt &lt;= DateTime.UtcNow)
                              .Select(p =&gt; p.Message)
                              .Distinct()
                              .OrderByDescending(p =&gt; p.Priority)
                              .ThenBy(p =&gt; p.Id)
                              .FirstOrDefault();
  </code></pre>
  
  <p>but this query has a flaw. if a lot of high priority messages got stuck in table then it will not try the previous low priority messages.</p>