C# linq2SQL - Filter Data

Having an issue.  The below code is returning two records for an employee.  Employee has two start and end dates.  Two ID's are passed in and I want the max returned.  So

Emp              ID      Start      End
John Doe      123   1/1/15   2/1/15
John Doe      234    1/1/15  3/1/15

I want below to be returned.
John Doe      234    1/1/15  3/1/15

var x =
	(from e in context.en
	let ID = e.en
	where js.Contains(e.jsc.jsp)
	let oe = (from eh in context.enH
		where eh.en == ID
		where dt >= eh.enstart
			  && dt <= eh.end
		orderby eh.pkid descending
		select eh.enstart).FirstOrDefault()
	 where oe != null
	select e);

Open in new window


I thought adding this section

let oe = (from eh in context.enH
            where eh.en == ID
            where dt >= eh.enstart
                    && dt <= eh.end
            orderby eh.pkid descending
            select eh.enstart).FirstOrDefault()

Or is there a way to filter the results by the end date from the result?

would fix it but it didn't.  Any ideas?
LVL 2
CipherISAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

it_saigeDeveloperCommented:
Here is one way to do it -
using System;
using System.Collections.Generic;
using System.Linq;

namespace EE_Q28745390
{
	class Program
	{
		static readonly List<Employee> employees = new List<Employee>() 
		{ 
			new Employee() { ID = 123, Name = "John Doe", Start = new DateTime(2015, 1, 1), End = new DateTime(2015, 2, 1) },
			new Employee() { ID = 234, Name = "John Doe", Start = new DateTime(2015, 1, 1), End = new DateTime(2015, 3, 1) }
		};

		static void Main(string[] args)
		{
			var x = (from e in employees
				    orderby e.End descending
				    group e by e.Name into y
				    where y != null
				    select y.FirstOrDefault());

			foreach (var e in x)
				Console.WriteLine(e);

			Console.ReadLine();
		}
	}

	class Employee
	{
		public int ID { get; set; }
		public string Name { get; set; }
		public DateTime Start { get; set; }
		public DateTime End { get; set; }

		public override string ToString()
		{
			return string.Format("ID: {0}; Name: {1}; Start: {2}; End: {3}", ID, Name, Start.ToShortDateString(), End.ToShortDateString());
		}
	}
}

Open in new window

Which produces the following output -Capture.JPG-saige-
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CipherISAuthor Commented:
I guess I should have iterated that the data example is coming from the joined example which is from my LINQ which has multiple tables.
0
it_saigeDeveloperCommented:
That shouldn't matter the concept is the same.  You order the data [descending] by the value that you want to retrieve (in this case the most recent date), group your data on it's distinct value (assume Name),  check your sanity to ensure that the group is not null and return the First [OrDefault] value.  This will represent the employee with the most recent start date.

-saige-
0
CipherISAuthor Commented:
That did work.  Thank You.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.