CODEWITHSUNDEEP
c#entity-frameworklinq
Tyler Jones
Tyler Jones

Reputation: 1343

How to Accomplish a Where on a joined table with Entity Framework

I have the following 2 tables in my database:

[Schedule](
    [Time] [datetime] NULL,
    [ScheduleID] [bigint] IDENTITY(1,1) NOT NULL,
    [PatientID] [varchar](20) NULL,
 CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED 
(
    [ScheduleID] ASC
)

and:

[Patient](
    [NameLast] [varchar](20) NOT NULL,
    [NameMiddle] [varchar](20) NULL,
    [NameFirst] [varchar](20) NOT NULL,
    [DOB] [varchar](20) NULL,
    [PatientID] [varchar](20) NOT NULL,
 CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED 
(
    [PatientID] ASC
)

And I want to accomplish the following SQL, except using linq methods with Entity Framework:

select NameFirst, NameLast, DOB
from Patient
join Schedule on Schedule.PatientID = Patient.PatientID
where Schedule.Time < GETDATE()

I know how to create a join, using my mappings, so creating a join isn't the problem. I also know how to do the date functionality I need so that isn't the problem.

I need to know how to accomplish (using linq methods) the part that says: where Schedule.Time < SOMETHING

Here's what I have tried, but it threw an error:

var patients = context.Patient
    .Include(x =>
        x.Schedule.Where(y => y.Time < DateTime.Now)
    );

The error it gave me was: "The Include path expression must refer to a navigation property defined on the type."

So, how can I accomplish a "Where" on a joined table, like I can in SQL, using linq methods with Entity Framework?

I can't do context.Patients.Where(x => x.Schedules.Time == DateTime.Now); because Patient.Schedules is a collection, since this is a one-to-many relationship.

Upvotes: 0

Views: 45

Answers (3)

Harald Coppoolse
Harald Coppoolse

Reputation: 30454

First, I'm not sure if it is wise to do the GetDate() inside your query, as an IQueryable I think it wouldn't work, and as an IEnumerable I'm not sure how many times it would be called during the enumeration.

You could first filter the Schedules you want to use and then do the join.

In small steps:

DateTime myDate = GetDate();
var oldSchedules = dbCntext.Schedules.Where(schedule => schedule.Time < myDate);

var requiredResult = dbContext.Patients  // join Patients
    .join(oldSchedules,                  // with the old schedules
    patient => patient.PatientID,        // from patients take PatientID
    schedule => schedule.PatientID<      // from old schedules that PatientID
    (patient, schedule) => new           // when they match, take the recods
    {                                    // to create a new Anonymous type
        NameFirst = patient.NameFirst,   // with the required properties 
        NameLast = patient.NameLast,
        Dob = patient.Dob,
    });

Of course you could put this in one statement (except for GetDate())

DateTime myDate = GetDate();
var result = dbContext.Schedules
    .Where(schedule => schedule.Time < myDate)
    .Join(dbContext.Patients,
    schedule => schedule.PatientId,
    patient => patient.PatientId,
    (schedule, patient) => new
    {
        ...
    });

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89006

Something like

context.Schedule.Where(y => y.Time < DateTime.Now).Select( s => s.Patient);

or

context.Patient.Where( p => p.Schedules.Any( s => s.Time < DateTime.Now) );

Upvotes: 2

Reza ArabQaeni
Reza ArabQaeni

Reputation: 4907

from t1 in db.Patient 
join t2 in db.Schedule on 
t1.PatientId equals t2.PatientId 
where t2.Time<getTime 
select new { t1.NameFirst, t1.NameLast, t1.DOB}

Upvotes: 1

Related Questions