Solved

LINQ Sort with MaxDate child record

Posted on 2015-02-19
2
112 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 34

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
Suggested Courses

751 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