LINQ Sort with MaxDate child record

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).DefaultIfEmpty()
select new ClientMaxActivityViewModel { clientData = c, activityData = t };

return View("ComplianceIndex",entities);
}
------------------------------------------

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="Please 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.MultilineText)]
[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="Please 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="Please 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(DataFormatString = "{0:d}")]
public DateTime RecordDate { get; set; }

public virtual ICollection<ClientPersonnel> 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 PrimaryClientActivityContact { 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.DateTime)]
[DisplayFormat(DataFormatString = "{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(DataFormatString = "{0:C}", ApplyFormatInEditMode = false)]
public decimal ExpenseAmount { get; set; }

[DataType(DataType.MultilineText)]
[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; }
}

1 Solution

LVL 36

it_saigeDeveloperCommented: 2015-02-19

You want to apply the order before the join; for example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace EE_Q28620665
{
	class Program
	{
		static void Main(string[] args)
		{
			Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
			Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
			Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
			Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };

			Pet barley = new Pet { Name = "Barley", Owner = terry };
			Pet boots = new Pet { Name = "Boots", Owner = terry };
			Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
			Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry };
			Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

			// Create two lists.
			List<Person> people = new List<Person> { magnus, terry, charlotte, arlene };
			List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };

			var query = from person in people
					  orderby person.FirstName
					  join pet in pets on person equals pet.Owner into gj
					  from subpet in gj.DefaultIfEmpty()
					  select new { person.FirstName, PetName = (subpet == null ? String.Empty : subpet.Name) };

			foreach (var v in query)
				Console.WriteLine("{0,-15}{1}", v.FirstName + ":", v.PetName);

			Console.ReadLine();
		}
	}

	class Person
	{
		public string FirstName { get; set; }
		public string LastName { get; set; }
	}

	class Pet
	{
		public string Name { get; set; }
		public Person Owner { get; set; }
	}
}

Open in new window

Produces the following output -

-saige-

MinirvaTwoAuthor Commented: 2015-02-19

Thank you Saige. I had tried that previously with no luck but I was doing it with a lambda expression when I should have just referenced my object as 'c.ClientName'.
Worked out perfectly.

Thanks.

LINQ Sort with MaxDate child record

Join & Write a Comment Already a member? Login.

Featured Post