Solved

LINQ Sort with MaxDate child record

Posted on 2015-02-19
2
109 Views
Last Modified: 2015-02-19
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:

Client Activity Sort
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; }
    }
0
Comment
Question by:MinirvaTwo
2 Comments
 
LVL 33

Accepted Solution

by:
it_saige earned 500 total points
ID: 40620145
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 -Capture.JPG-saige-
0
 

Author Comment

by:MinirvaTwo
ID: 40620350
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.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

820 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