Solved

LINQ Sort with MaxDate child record

Posted on 2015-02-19
2
107 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now