Hi, I've just started expirimenting with Linq to LLBLGen, and it looks very promising. I'm trying to have a query which select two related entities, without having a nested query or a prefetch (though it's possible to achieve the result using them).

The code i'm trying to execute is:

        
 LinqMetaData metadata = new LinqMetaData(new DataAccessAdapter());
            var q =
                from patient in metadata.Patient
                join practicePatient in metadata.PracticePatient
                    on patient.PatientId equals practicePatient.PatientId
                where patient.PatientId <= 200
                select new { Patient = patient, PracticePatient = practicePatient };

            foreach (var result in q)
            {
                Console.WriteLine(result.ToString());
            }

which fails with the following exception: _ Additional information: Unable to cast object of type 'System.Int64' to type 'TestPmdsLinq.EntityClasses.PatientEntity'._ (on the "foreach (var result in q") statement)

The query itself seems fine, and includes all the fields from both entities:

SELECT "LPA_L1"."PATIENT_ID"                   AS "PatientId",
       [other LPA_L1 fields...]
       "LPA_L2"."PRACTICE_PATIENT_ID"         AS "PracticePatientId",
       [other LPA_L2 fields...]
  FROM ("PMDS"."PATIENT" "LPA_L1" INNER JOIN "PMDS"."PRACTICE_PATIENT"
        "LPA_L2" ON "LPA_L1"."PATIENT_ID" = "LPA_L2"."PATIENT_ID")
 WHERE ((((("LPA_L1"."PATIENT_ID" <= 200)))))

However, if i try to select one of the entities, just replacing the "select new..." line with

select patient;

or

select practicePatient;

or, selecting multiple fields, and not entities:

select new { PatientId = patient.PatientId, PersonalPhysician = practicePatient.PersonalPhysicianUserId };

it works fine (the last one having just the selected fields in the sql query, which is great).

Am i missing something? Is it something that shouldn't logically be supported, or there are just better way to write it?

Thanks, I'll continue toying with the new magical layer over LLBLGen (still overwhelmed), Amitay

(Using: LLBLGen pro 2.6 Beta, May 15th 2008, Oracle 10gR2, windows 2003)

This isn't supported. If you download the latest build of the beta, you have the linq docs complete, it's explained in Remarks and Limitations:

Storing an entity in an anonymous type field isn't supported, as the entity itself isn't materialized so it's not storable as a value inside the anonymous object.

var q = from c in metaData.Customer select new { Foo = c };

A projection projects values from the resultset coming from the DB into a target. Your query stores entities into the target. However, entities aren't coming from the db, a resultset is. So there has to be an intermediate projection to first fill the entities and then store the entities into the final anonymous type. This isn't supported.

Fetching the individual entities is supported, as it then fetches the data into an entity instance.

Understood, thank you for the clearing up this one for me.