Solved

LINQ Sort with MaxDate child record

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 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