Solved

LINQ Sort with MaxDate child record

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 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

11 Experts available now in Live!

Get 1:1 Help Now