This query:

var appointments = from a in    db.Appointment
       let Mp = (from mpAt in db.Attendee
             where mpAt.FkAppointment == a.PkAppointment &&
                    mpAt.FkAccount == pkMp
             select new
             {
                 mpAt.FkAccount,
                 mpAt.FkAppointment,
                 mpAt.AppointmentLength,
                 mpAt.AppointmentStartTimeOffset
             }).First()
       let Pa = (from paAt in db.Attendee
             join pa in db.Person on paAt.FkAccount equals pa.PfkPerson
             where paAt.FkAppointment == a.PkAppointment
             select new
             {
                 paAt.FkAppointment,
                 AccountType = paAt.Account.FkAccountType,
                 Name = pa.Title.Name + " " +paAt.Account.Name,
                 NewPerson = pa.NewPerson
             }).FirstOrDefault()
       where
        a.Start >= fromTime &&
        a.Start < toTime &&
        a.Status == false &&
        a.FkSite == site
       select new AppointmentDetails
       (
           a.PkAppointment,
           a.FkLocation,
           a.Arrived,
           a.Noshow,
           a.ScheduleServicesCache,
           Mp.AppointmentLength,
           Mp.AppointmentStartTimeOffset,
           Pa.Name,
           Pa.NewPerson,
           a.Start,
           a.Finish,
           a.Subject
       );

Is throwing this error: "A nested query in the projection has no correlation filter to tie its set to the containing parent row. Please add a correlation filter to the where clause of this query to tie the nested query to the parent."

A bit of background:

Tables: Account Person Mp Appointment Attendee

A Person is a type of Account. An Mp (Medical Person) is a type of Account. Each Appointment can have several Attendees. An Attendee could be a Person or an Mp.

I am looking to get all the appointments for a particular Medical Person between the dates provided, and on the site provided. I also need to know the patient (Person) involved in that appointment. There may be none (meeting).

The error appears to infer that there's no correlation between the query and subquery(s). Surely it correlates if I return zero or one result for each subquery, and it's matched to the primary-key of the appointment table? Or am I missing something?

I'm running 2.6 released May 15th 2009.

Thanks

Paul

Hi Paul,

Please post the buildnr of runtime libraries (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=12769), or please make sure you are using the latest one.

Now, it may be the "let" statements. you are fetching subqueries in let statements. this is not really that great (and likely the cause of the error) because 'let' statements are converted to scalar queries in projections otherwise they're not convertable to SQL (as SQL doesn't support this kind of construct). So please try to rework your query with joins.

daelmo wrote:

Hi Paul,

Please post the buildnr of runtime libraries (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=12769), or please make sure you are using the latest one.

Now, it may be the "let" statements. you are fetching subqueries in let statements. this is not really that great (and likely the cause of the error) because 'let' statements are converted to scalar queries in projections otherwise they're not convertable to SQL (as SQL doesn't support this kind of construct). So please try to rework your query with joins.

SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll - 2.6.8.1114 SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll - 2.6.9.622 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll - 2.6.9.616

If 'let' isn't supported by LinqToLLBLGen, what alternative do you suggest? A join isn't really appropriate here.

I don't think I have the latest libs, I'll get those installed and report back.

Ok, the new libs didn't help. I get the same error message. I tried a slightly different approach and still received the same error message:

                    var appointments = from a in db.Appointment
       join at in db.Attendee on a.PkAppointment equals at.FkAppointment into attendees
       where
    a.Start >= fromTime &&
    a.Start < toTime &&
    a.Status == false &&
    a.FkSite == site
                                       select new AppointmentDetails
       (
       a.PkAppointment,
       a.FkLocation,
       a.Arrived,
       a.Noshow,
       a.ScheduleServicesCache,
       a.Start,
       a.Finish,
       a.Subject,
       (from at in attendees 
        where at.FkAccount == mp.PfkMp 
        select new MpAttendeeDetails( 
            at.AppointmentLength, 
            at.AppointmentStartTimeOffset 
            )).First(),
       (from at2 in attendees 
        where at2.Account.FkAccountType == "PA" 
        select new PaAttendeeDetails(
            at2.Account.Person.Title.Name + " " + at2.Account.Name, 
            at2.Account.Person.NewPerson
            )).First()
       );

Tables: Account Person Mp Appointment Attendee

A Person is a type of Account. An Mp (Medical Person) is a type of Account. Each Appointment can have several Attendees. An Attendee could be a Person or an Mp.

I am looking to get all the appointments for a particular Medical Person between the dates provided, and on the site provided. I also need to know the patient (Person) involved in that appointment. There may be none (meeting).

I think you can fetch the appointments by using a normal linq implementation of the following SQL query:

SELECT *
FROM Appointment ap
INNER JOIN Attendee at ON...
INNER JOIN Account ac ON...
INNER JOIN Mp mp ON...
WHERE ....

And to get the patient if exists, you can use a prefetchPath (withPath), to fetch related Accounts via the attendees table, and these can joined to the person table or filtered by the type.

Walaa wrote:

Tables: Account Person Mp Appointment Attendee

A Person is a type of Account. An Mp (Medical Person) is a type of Account. Each Appointment can have several Attendees. An Attendee could be a Person or an Mp.

I am looking to get all the appointments for a particular Medical Person between the dates provided, and on the site provided. I also need to know the patient (Person) involved in that appointment. There may be none (meeting).

I think you can fetch the appointments by using a normal linq implementation of the following SQL query:

SELECT *
FROM Appointment ap
INNER JOIN Attendee at ON...
INNER JOIN Account ac ON...
INNER JOIN Mp mp ON...
WHERE ....

And to get the patient if exists, you can use a prefetchPath (withPath), to fetch related Accounts via the attendees table, and these can joined to the person table or filtered by the type.

Could you expand on that with Linq?

joins are a standard LINQ feature - usually just listing the tables/entities will cause the LLBLGen LINQ provider to translate them to the relevant SQL Inner Joins.

Prefetch patch are covered here. http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/Linq/gencode_linq_prefetchpaths.htm

Matt