I'm doing a rather large query using prefetch paths. I'm fetching an entity with a 1:1 entity and then a bunch of lookup values for those entities.

      IPrefetchPath2 pfPath = new PrefetchPath2( (int)EntityType.JobsEntity );
      pfPath.Add( JobsEntity.PrefetchPathJobFormatIdEntity );
      IPrefetchPathElement2 pfPathElement = JobsEntity.PrefetchPathJobDetailsDigitalPrint;
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathJobIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathLaminationFinishIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathLaminationProcessIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathLaminationThicknessIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathMountToColorIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathMountToIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathMountToThicknessIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathNumSidesLaminatedIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathPackageTypeIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathPrinterTypeIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathShippingTimeFrameIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathSidesMountedIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathSubstrateIdEntity );
      pfPath.Add( pfPathElement );
    
      JobsEntity job = JobManager.Fetch( _jobId, pfPath );

This however only populates the root entity with data, leaving the rest of the sub-entities unpopulated. In checking sql profiler I know that each query being generated is returning the correct data (the generated sql is correct and working), however none of it is being used to populate the entities/sub entities.

Further, if I comment out the subpath code, the 1:1 mapped entity will be populated correctly.

change: pfPath.Add( JobsEntity.PrefetchPathJobFormatIdEntity ); IPrefetchPathElement2 pfPathElement = JobsEntity.PrefetchPathJobDetailsDigitalPrint;

into:

IPrefetchPathElement2 pfPathElement = pfPath.Add( JobsEntity.PrefetchPathJobFormatIdEntity );

Otis wrote:

change: pfPath.Add( JobsEntity.PrefetchPathJobFormatIdEntity ); IPrefetchPathElement2 pfPathElement = JobsEntity.PrefetchPathJobDetailsDigitalPrint;

into:

IPrefetchPathElement2 pfPathElement = pfPath.Add( JobsEntity.PrefetchPathJobFormatIdEntity );

Doesn't work. With your changes I get an error, but adjusting for what I think your trying to do produces the same results as before (no error, but no data either), here is my current code (minus some of the non-relatvent bits):

      IPrefetchPath2 pfPath = new PrefetchPath2( (int)EntityType.JobsEntity );
      IPrefetchPathElement2 pfPathElement = pfPath.Add( JobsEntity.PrefetchPathJobDetailsDigitalPrint);
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathJobIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathLaminationFinishIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathLaminationProcessIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathLaminationThicknessIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathMountToColorIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathMountToIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathMountToThicknessIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathNumSidesLaminatedIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathPackageTypeIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathPrinterTypeIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathShippingTimeFrameIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathSidesMountedIdEntity );
      pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathSubstrateIdEntity );
    
      JobsEntity job = JobManager.Fetch( _jobId, pfPath );

I appologize, it's a confusing sample, I'm trying to do several things at once there and I probably should have simplified it a bit in my first post.

Edit: Fixed a slightly typo in my code.

Cadmium wrote:

Otis wrote:

change: pfPath.Add( JobsEntity.PrefetchPathJobFormatIdEntity ); IPrefetchPathElement2 pfPathElement = JobsEntity.PrefetchPathJobDetailsDigitalPrint;

into:

IPrefetchPathElement2 pfPathElement = pfPath.Add( JobsEntity.PrefetchPathJobFormatIdEntity );

Doesn't work. With your changes I get an error, but adjusting for what I think your trying to do produces the same results as before (no error, but no data either), here is my current code (minus some of the non-relatvent bits):

THe error you got was likely that you added the element twice. Looking closer at your original code, it indeed should work, I misread the first prefetch path node, I thought you were adding the subpath elements to a node which was never added. My mistake.

Your code should work, the initial code as well. The only uncertain factor is the 'Fetch' method, which method is that: an own written routine or one of the manager classes methods (and which one?)

My XManager.Fetch method simply passes the id and prefetch path to the adapter.FetchEntity method, it has some handling if the entity is not found:

    public static JobsEntity Fetch( int entityId, IPrefetchPath2 pfPath) {

      JobsEntity entity = new JobsEntity(entityId);
    
      using( DataAccessAdapter adapter = ConfigurationManager.GetDataAdapterInstance() )  {
        adapter.FetchEntity( entity, pfPath );
      }

      if (  entity.Fields.State != EntityState.Fetched ) {
        entity = new JobsEntity();
        entity.StatusId = (int)JobManager.DEFAULT_STATUS;
        entity.StartDate = DateTime.Today;
        entity.Name = "New Job";
      }

      return entity;
    }

The code should work, but it still doesn't. Like I said, watching the code it generates in sql profiler, it's getting the data, but only the root entity is being populated. If I comment out all the subpath code (leaving the PrefetchPathJobDetailsDigitalPrint, and PrefetchPathJobFormatIdEntity), those two will work, but with the subpath code the PrefetchPathJobDetailsDigitalPrint entity is not populated. The queries work, but there's no data in the entity itself.

I hope that makes sense.

Ok, to be able to setup a testcase here, I'd like to know a few things about the tables and the relations between the entities.

You have: Jobs - 1:1 - JobDetailsDigitalPrint - m:1 - JobId

?

Could you please give me the relevant table SQL, for these 3 tables, and the FK constraints between them? (so I can setup the proper tables here).

Otis wrote:

Ok, to be able to setup a testcase here, I'd like to know a few things about the tables and the relations between the entities.

You have: Jobs - 1:1 - JobDetailsDigitalPrint - m:1 - JobId

?

Could you please give me the relevant table SQL, for these 3 tables, and the FK constraints between them? (so I can setup the proper tables here).

I sent you an email with some scripts.

Got it, thanks. The setup is indeed a bit strange, but nevertheless, it should merge hte right entities into the right root entities. I'll try to reproduce it here.

Otis wrote:

Got it, thanks. The setup is indeed a bit strange, but nevertheless, it should merge hte right entities into the right root entities. I'll try to reproduce it here.

Thanks! (hopefully I'm not just doing something stoopid )

remove this line: pfPathElement.SubPath.Add( JobDetailsDigitalPrintEntity.PrefetchPathJobIdEntity );

... as with this line, you're fetching another Job object (with the same data) and it will set JobDetailsDigitalPrint.Job to that object, which will de-reference job.JobDetailsDigitalPrint you've already fetched (so job.JobDetailsDigitalPrint will be null).

JobDetailsDigitalPrint.Job is already set to the right value, you don't have to fetch that

I've to test it yet with testcode, but I'm pretty sure that's the issue here

(tested) -> that's the issue.

Dammit, I knew it was going to be something stupid Thanks for pointing it out and sorry to trouble you with a silly mistake, I should have spotted that one myself.

As a side note (it's probably been asked), is there anyway to have the prefetch paths return a new or default entity instead of null when a prefetch path fails to return anything?

Cadmium wrote:

Dammit, I knew it was going to be something stupid Thanks for pointing it out and sorry to trouble you with a silly mistake, I should have spotted that one myself.

Everyone makes mistakes , that's why we're human

As a side note (it's probably been asked), is there anyway to have the prefetch paths return a new or default entity instead of null when a prefetch path fails to return anything?

No, that's not possible.

Otis wrote:

No, that's not possible.

I'm sure there is some great reason not to have this, but could this be a requested feature?

Cadmium wrote:

Otis wrote:

No, that's not possible.

I'm sure there is some great reason not to have this, but could this be a requested feature?

The main reason is that it gives problems when saving. If you fetch a graph of say 3 levels deep and on the 3rd level you have m:1 related entities and because they're not there, you get new ones, saving the graph will insert these new ones with no fields, which gives errors. To save this graph then requires you to dereference these new entities.

With a lazy loaded selfservicing fetch, you can overcome this, but not in a multi-level prefetch path.

Otis wrote:

The main reason is that it gives problems when saving. If you fetch a graph of say 3 levels deep and on the 3rd level you have m:1 related entities and because they're not there, you get new ones, saving the graph will insert these new ones with no fields, which gives errors. To save this graph then requires you to dereference these new entities.

With a lazy loaded selfservicing fetch, you can overcome this, but not in a multi-level prefetch path.

Ahh, makes sense. My use for this is read-only so I wouldn't have this problem, but I see what you mean.