Linq using joins to obtain data unique by latest date

[Mike Askew]

Hi there,

I have two tables in my data entity model accessed by StaffDB.

Staffs contains:
StaffID
FirstName
LastName
Address
Town
County
Postcode
YearJoined
CurrentGrade
Role

Ratings contains:
RatingID
StaffID
Date
Rating1 (automatic to avoid conflict with table name also of rating)
Comments

These are linked by StaffID.

I am trying to select each staff member using their most recent rating date so that I do not get duplicate output as this is then categorised on the web page it outputs.

I have found some code online and adapted it to get the following:

var LatestEmployeeRatings = from s in StaffDB.Staffs
                                        join r in StaffDB.Ratings
                                        on s.StaffID equals r.StaffID
                                        group s by s.StaffID into g
                                        select g.OrderByDescending(r => r.Date).First();

However this code was for using one table not two and therefore I think im going wrong in lines 4 and 5 as line 5 has an error as r.Date does not exist at this point :(

Can anyone enlighten me on how I need to further adapt this to work?

Cheers

[thines01]

Do you want a combination of all of the columns?

[Mike Askew]

Hi Thines01,

I am looking to obtain the StaffID, Name(concat of firstname + last name), grade, rating date, rating and comment.

[thines01]

OK. Here is an interesting twist.
I selected the records into a new collection, then did the grouping

var LatestEmployeeRatings =
         (
            from s in StaffDb.Staffs
            join r in StaffDb.Ratings
            on s.StaffID equals r.StaffID
            
            select new
            {
               StaffID = s.StaffID,
               Name = s.FirstName + ' ' + s.LastName,
               Grade = s.CurrentGrade,
               RatingDate = r.Date,
               Rating1 = r.Rating1,
               Comment = r.Comment
            }

            into ss
            group ss by ss.StaffID into g
            select g.OrderByDescending(r => r.RatingDate).First()
         ).ToList(); // <--easier to see in the debugger

[Mike Askew]

Using this code, ran to check its execution and encountered an exception of type: NotSupportedException.

Unable to create a constant value of type 'System.Object'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Not entirely sure what is causing this error as I've literally copied the code in to check it runs.

[thines01]

This is Linq to Entities?
If so, the problem might be the "select new", because that action happens IN the entity.
You might have to do this in two stages.

[Mike Askew]

Yeap linq to an entity model

[Mike Askew]

StaffRatingSystemDBModel.StaffRatingSystemDBEntities StaffDB = new StaffRatingSystemDBModel.StaffRatingSystemDBEntities();
            var LatestEmployeeRatings = (from s in StaffDB.Staffs 
                                         join r in StaffDB.Ratings 
                                         on s.StaffID equals r.StaffID 
                                         select new { StaffID = s.StaffID, Name = s.FirstName + ' ' + s.LastName, Grade = s.CurrentGrade, RatingDate = r.Date, Rating = r.Rating1, Comment = r.Comments } 
                                         into ss 
                                         group ss by ss.StaffID into g 
                                         select g.OrderByDescending(r => r.RatingDate).First()).ToList();

            lstR1.DataSource = LatestEmployeeRatings;
            lstR1.DataBind();

Error occurs on the linq query itself so doesnt reach the databind

[thines01]

Can you run my test code?
If so, I will modify the linq for the two-stage query.
If not, we will need to look at something else.

...or it could ACTUALLY be the .ToList()

using System;
using System.Collections.Generic;
using System.Linq;

namespace DW_399101
{
   public class Staff
   {
      public int StaffID { get; set; }
      public string FirstName { get; set; }
      public string LastName { get; set; }
      public string Address { get; set; }
      public string Town { get; set; }
      public string PostCode { get; set; }
      public DateTime YearJoined { get; set; }
      public char CurrentGrade { get; set; }
      public string Role { get; set; }

      public Staff()
      {
         StaffID = 0;
         FirstName = "";
         LastName = "";
         Address = "";
         Town = "";
         PostCode = "";
         YearJoined = new DateTime();
         CurrentGrade = '\0';
         Role = "";
      }
   }

   public class Rating
   {
      public int RatingID { get; set; }
      public int StaffID { get; set; }
      public DateTime Date { get; set; }
      public string Rating1 { get; set; }
      public string Comment { get; set; }

      public Rating()
      {
         RatingID = 0;
         StaffID = 0;
         Date = new DateTime();
         Rating1 = "";
         Comment = "";
      }
   }

   public class CStaffDB
   {
      public List<Staff> Staffs { get; set; }
      public List<Rating> Ratings { get; set; }

      public CStaffDB()
      {
         Staffs = new List<Staff>();
         Ratings = new List<Rating>();
      }
   }

   public class CDW_399101
   {
      static void Main(string[] args)
      {
         List<Staff> lstStaff = new List<Staff>()
         {
            new Staff(){StaffID =1, CurrentGrade='E', FirstName = "Joe", LastName = "Jan"},
            new Staff(){StaffID =2, CurrentGrade='C', FirstName = "Abe", LastName = "Arn"},
            new Staff(){StaffID =3, CurrentGrade='J', FirstName = "Ben", LastName = "Bow"}
         };

         List<Rating> lstRatings = new List<Rating>()
         {
            new Rating(){StaffID = 1, Comment="Excellent", Rating1="P", Date=new DateTime(2010, 12, 31)},
            new Rating(){StaffID = 1, Comment="Fair", Rating1="S", Date=new DateTime(2009, 12, 31)},
            new Rating(){StaffID = 1, Comment="Poor", Rating1="U", Date=new DateTime(2008, 12, 31)},
            new Rating(){StaffID = 2, Comment="Fair", Rating1="S", Date=new DateTime(2011, 1, 31)},
            new Rating(){StaffID = 2, Comment="Excellent", Rating1="P", Date=new DateTime(2009, 12, 31)},
            new Rating(){StaffID = 2, Comment="Poor", Rating1="U", Date=new DateTime(2008, 12, 31)},
            new Rating(){StaffID = 3, Comment="Poor", Rating1="U", Date=new DateTime(2011, 2, 27)},
            new Rating(){StaffID = 3, Comment="Excellent", Rating1="P", Date=new DateTime(2009, 12, 31)},
            new Rating(){StaffID = 3, Comment="Fair", Rating1="S", Date=new DateTime(2008, 12, 31)},
         };

         CStaffDB StaffDb = new CStaffDB()
         {
            Staffs = lstStaff,
            Ratings = lstRatings
         };

         var LatestEmployeeRatings =
         (
            from s in StaffDb.Staffs
            join r in StaffDb.Ratings
            on s.StaffID equals r.StaffID
            
            select new
            {
               StaffID = s.StaffID,
               Name = s.FirstName + ' ' + s.LastName,
               Grade = s.CurrentGrade,
               RatingDate = r.Date,
               Rating1 = r.Rating1,
               Comment = r.Comment
            }

            into ss
            group ss by ss.StaffID into g
            select g.OrderByDescending(r => r.RatingDate).First()
         ).ToList(); // <--easier to see in the debugger

         LatestEmployeeRatings.ForEach(ler =>
         {
            Console.WriteLine("{0} {1} {2} {3} {4} {5}",
               ler.StaffID, ler.Name, ler.Grade,
               ler.RatingDate, ler.Rating1, ler.Comment);
         });
      }
   }
}

[Mike Askew]

Just looked at it myself, I believe the issue lay with your use of ' ' to add the space between firstname and lastname instead of " ". The following code is functioning as desired:

var LatestEmployeeRatings = (from s in StaffDB.Staffs
                                         join r in StaffDB.Ratings
                                         on s.StaffID equals r.StaffID
                                         select new { StaffID = s.StaffID, Name = s.FirstName + " " + s.LastName, Grade = s.CurrentGrade, RatingDate = r.Date, Rating = r.Rating1, Comment = r.Comments }
                                         into ss                             
                                         group ss by ss.StaffID into g 
                                         select g.OrderByDescending(r => r.RatingDate).FirstOrDefault()).ToList();

Had to change line 7 to use .FirstOrDefault instead of .First as it threw an error there but checked now and its running and outputting the results as required.

Thank you very much for your help with this, +1

Regards,

Mike

[thines01]

Fantastic! Thanks!