I have a developed a CRM application that includes Clients, Contacts and Activities. An Activity is when a Salesperson interacts with a Client. Activity records have a date property. I have successfully written a linq query that retrieves all Clients and a single corresponding Activity record, if there is one, which is the most recent Activity, by the ActivityDate property. However, I cannot get the query to sort by the ClientName property.
Here is the linq code:
--------------------------
----------
-------
public ViewResult ComplianceIndex(string city, string status, string salesperson)
{
var entities = from c in db.Clients where c.Salesperson == salesperson && c.Status == status && c.City == city
join a in db.Activities on c.ClientID equals a.ClientID
into temp
from t in temp.OrderByDescending(a => a.ActivityDate).Take(1).De
faultIfEmp
ty()
select new ClientMaxActivityViewModel
{ clientData = c, activityData = t };
return View("ComplianceIndex",ent
ities);
}
--------------------------
----------
------
Here is the data returned:

Here is the Client entity:
--------------------------
----------
-
public class Client
{
private const bool Default_Active = true;
[Key]
[HiddenInput(DisplayValue = false)]
public int ClientID { get; set; }
[Display(Name = "Client Name")]
[Required(ErrorMessage = "Please enter a Client Name")]
[StringLength(120, ErrorMessage = "Please enter 120 characters or less")]
public string ClientName { get; set; }
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
public string Specialty { get; set; }
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
public string Street { get; set; }
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
public string Suite { get; set; }
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
[Required(ErrorMessage="Pl
ease enter a City for this client")]
public string City { get; set; }
[StringLength(2, ErrorMessage = "Please enter 2 characters")]
public string State { get; set; }
[StringLength(10, ErrorMessage = "Please enter 10 characters or less")]
public string ZipCode { get; set; }
[StringLength(20, ErrorMessage = "Please enter 20 characters or less")]
public string Telephone { get; set; }
[StringLength(20, ErrorMessage = "Please enter 20 characters or less")]
public string FAX { get; set; }
[Display(Name = "Visit Frequency")]
[Required(ErrorMessage = "Please enter a Visit Frequency")]
public string VisitFrequency { get; set; }
[Display(Name = "Primary Pathologist")]
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
public string PrimaryPathologistName { get; set; }
[Display(Name = "Images in Reports")]
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
public string ImagesInReports { get; set; }
[Display(Name = "Report Delivery")]
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
public string ReportDelivery { get; set; }
[Display(Name = "Clinical Testing")]
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
public string ClinicalTesting { get; set; }
[Display(Name = "Special Note")]
[DataType(DataType.Multili
neText)]
[StringLength(150, ErrorMessage="Please enter 150 characters or less")]
public string SpecialNote { get; set; }
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
public string EMR { get; set; }
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
public string PAL { get; set; }
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
[Display(Name="PRL Staff")]
[Required(ErrorMessage="Pl
ease enter a PRL Staffer (salesperson)")]
public string Salesperson { get; set; }
[Display(Name = "Acct Mgr")]
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
[Required(ErrorMessage="Pl
ease enter an Account Manager")]
public string AccountManager { get; set; }
public string Status { get; set; }
public bool Active { get; set; }
public string CreatedBy { get; set; }
[DisplayFormat(DataFormatS
tring = "{0:d}")]
public DateTime RecordDate { get; set; }
public virtual ICollection<ClientPersonne
l> ClientPersonnels { get; set; }
public virtual ICollection<Activity> Activities { get; set; }
}
And the Activity entity:
--------------------------
----------
-----
public class Activity
{
[Key]
[HiddenInput(DisplayValue = false)]
public int ActivityID { get; set; }
[HiddenInput(DisplayValue = false)]
public int ClientID { get; set; }
[Display(Name = "Salesperson")]
[Required(ErrorMessage = "Please enter a Salesperson")]
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
public string SalespersonName { get; set; }
[Display(Name = "Activity Contact")]
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
public string PrimaryClientActivityConta
ct { get; set; }
[Display(Name = "Activity Type")]
[Required(ErrorMessage = "Please enter an Activity Type")]
[StringLength(50, ErrorMessage = "Please enter 50 characters or less")]
public string ActivityType { get; set; }
[DataType(DataType.DateTim
e)]
[DisplayFormat(DataFormatS
tring = "{0:MM-dd-yyyy}", ApplyFormatInEditMode = true)]
[Required(ErrorMessage = "Please enter an Activity Date")]
[Display(Name = "Activity Date")]
public DateTime ActivityDate { get; set; }
[DataType(DataType.Text)]
[Required(ErrorMessage = "Please enter an Activity Time")]
[Display(Name = "Activity Time")]
public String ActivityTime { get; set; }
[Display(Name = "Expense")]
[DisplayFormat(DataFormatS
tring = "{0:C}", ApplyFormatInEditMode = false)]
public decimal ExpenseAmount { get; set; }
[DataType(DataType.Multili
neText)]
[Required(ErrorMessage = "Please enter a Note")]
public string Note { get; set; }
public virtual Client Client { get; set; }
}
--------------------------
----------
----------
--
Here is the ViewModel:
--------------------------
----------
-
public class ClientMaxActivityViewModel
{
public Client clientData { get; set; }
public Activity activityData { get; set; }
}
Open in new window
Produces the following output -