Ok. How do I use DynamicRelation in code only to get a join of two entities. I am able to join my entities but I only can get the fields for only one side of the join.

HelperClasses.EntityCollection<TInvEntity> allInventory = new EntityCollection<TInvEntity>(new TInvEntityFactory());

DynamicRelation relation = new DynamicRelation(EntityType.TInvEntity, JoinHint.Left, EntityType.TInvLinksEntity, String.Empty, "LinkEntity", HelperClasses.TInvFields.Id == HelperClasses.TInvLinksFields.MasterInvId.SetObjectAlias("LinkEntity"));

        RelationPredicateBucket filter = new RelationPredicateBucket();
        filter.Relations.Add(relation);
        filter.SelectListAlias="LinkEntity";

        daCDBSETUP.FetchEntityCollection(allInventory, filter);

So this is just a simple join between an Inventory and InventoryDetail Entity. It gives me the correct amount of elements returned in the collection, but the only fields I can access of the ones from the TInvEntity entity and none from the TInvLinksEntity. What am I messing? Do you have an example of joining two in memory entities, and being able to access all the fields after the join? I do now want to do the linking in the designer, I want to know how to do it just using DynamicRelation in code only.

Thanks,

Charlie J.

Hi Charlie,

DynamicRelation is used to joins to a subquery like so

SELECT o.*
FROM
(
    SELECT  CustomerId 
    FROM    Customers
    WHERE   Country = @country
) c INNER JOIN Orders o ON
  c.CustomerId = o.CustomerId

Otherwise you just need to use normal relations. However I think you are looking at the wrong place. What you are looking for (I guess) is PrefetchPaths.

When you use prefetch paths, you can fetch a collection and attach related objects to it. For example:

EntityCollection<TInvEntity> allInventory = new EntityCollection<TInvEntity>(new TInvEntityFactory());

IPrefetchPath  path = new PrefetchPath((int) EntityType.TInvEntity);
path.Add(TInvEntity.PrefetchPathTInvLinksEntityUsingTInvId);

daCDBSETUP.FetchEntityCollection(allInventory, null, path);

Now TInvEntity objects are fetched plus the related TInvLinkEntities. If the relation is 1:m then you can use the filled collection like:

foreach(TInvEntity tinv in allInventory)
{
     int linksOnThisInventory = tinv.TInvLinks;
     /// ... access other things
}

So, its easy: - If you want to fetch related objects into Entity objects, use PrefetchPaths. - If you are filtering/sorting on related entities, use relations. - If you want all fields mixed in one resulset. Go for TypedViews, TypedLists or DynamicLists. - If you want to filter on a special relation and such relation involves a subquery, use DynamicRelation.

Thanks for that information. But the two Entities are not related in the database. I know I could add a virtual relation using the designer, but I would like to know how to do it all in code for Two Entities that are not related in the database. So they is what I thought DynamicRelation was suppose to do. So can I do the following and how?

1.) Make two Entities related in code (They are not related in the database or using the designer).

2.) Use PrefetchPath on the Dynamic relationship from Step #1

3.) Get a custom resultset containing both Entities

Thanks,

Charlie J.

cjbiggs wrote:

Thanks for that information. But the two Entities are not related in the database.

Ohh, I see.

cjbiggs wrote:

1.) Make two Entities related in code (They are not related in the database or using the designer).

Create a new EntityRelation at code and specify the two entity/fields that involve the relation.

EntityRelation newRelation = new EntityRelation(
    SalesOrderFields.SalesOrderId, SalesOrderDetailFields.SalesOrderId, RelationType.OneToMany);

cjbiggs wrote:

2.) Use PrefetchPath on the Dynamic relationship from Step #1

No. No in entity fetches. See, relation is for filtering or sorting on related entities. PrefetchPaths is fetch related objects and is inherent to Entity objects. So you can not create a prefetchpath on the fly.

cjbiggs wrote:

3.) Get a custom resultset containing both Entities

No targeting entities. But you can use DynamcLiist that targets DataTable (see my liink in my previous post). Look for instance, suppose I dont have SalesOrder -> SalesOrderDetail relation and I want to fetch fields from both entities. I can use DynamicList and a new custom relation:

EntityFields2 fields = new EntityFields2(2);
fields.DefineField(SalesOrderDetailFields.LineTotal, 0);
fields.DefineField(SalesOrderFields.CustomerId, 1);

EntityRelation newRelation = new EntityRelation(
    SalesOrderFields.SalesOrderId, SalesOrderDetailFields.SalesOrderId, RelationType.OneToMany);

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(newRelation);

DataTable results = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())         
{
    adapter.FetchTypedList(fields, results, bucket);
}

Hope helpful

Great Thanks.