Hello,

I need a little help with single entity prefectch paths. The goal is to return one row of data in this case a vendor address and it's type. Here are the tables

Vendors - VendorID (not needed in this example but shown for reference) VendorAddresses - VendorID, AddressID, AddressTypeID (Join Table between Address and Vendor ) AddressTypes - AddressTypeID (List of Address Types - related to the Join table) Address - AddressID

I know the addressId - I want to load the addressEntity with the Address type

I am trying this:

IPrefetchPath addressFetchPath = new PrefetchPath((int)EntityType.AddressEntity); addressFetchPath.Add(AddressEntity.PrefetchPathAddressTypeCollectionViaCustomerAddresses); address = new AddressEntity(Convert.ToInt64(hdnAddressId.Value), addressFetchPath);

This builds - but the question is how do I access the items in the prefeched path? and I realy just want one addressType for this address.

SQL would be:

Select StreetLine1,StreetLine2,City,StateID,PostalCode,at.name from [Address] a,VendorAddresses va,addressType at where a.addressId = va.addressId and va.addressTypeId = at.addressTypeId and a.addressId = 190

Which Returns:

Via Nitti, 1A NULL Castano Primo 23 20022 Physical Address

I am trying this:

address.GetFieldByName("AddressType.Name").ToString();

But I do not see any examples of how to access the prefectched data?

Thanks for your help.

Todd

Ok now I am trying a subpath like this

IPrefetchPath addressFetchPath = new PrefetchPath((int)EntityType.AddressEntity); addressFetchPath.Add(AddressEntity.PrefetchPathVendorAddresses).SubPath.Add(VendorAddressesEntity.PrefetchPathAddressType); address = new AddressEntity(Convert.ToInt64(hdnAddressId.Value), addressFetchPath);

based on what I see in the immediate window my address type is there, but how do access that?

Maybe this is not possible and i am going to have to build a whole collection with predicates just to get at that address type.

[quotenitck="rtbike"]and I realy just want one addressType for this address. The problem is that your schema says that one address can be referenced many times using different address types. For instance, this could be valid on your data (I guess):

VendorAddress data (VendorId - AddressId - AddressType) (1 - 50 - 1) (1 - 50 - 2) (2 - 30 - 2) (3 - 30 - 2)

For instance, above data says that: vendor 1 lives in address 50 (address type 1 = home), he also works in address 50 (address type 2 = work); vendors 2 and 3 work at the same address 30 (address type 2 = work).

Said that, and looking at your approximate SQL, I think you need to approach through VendorAddress:

VendorAddressCollection addressesPerVendor = new VendorAddressCollection;

// the prefetchPath
IPrefetchPath addressFetchPath = new PrefetchPath((int)EntityType.VendorAddressEntity);
addressFetchPath.Add(VendorAddressEntity.PrefetchPathAddress);
addressFetchPath.Add(VendorAddressEntity.PrefetchPathAddressType);

// ... fetch the data

// access the entity fields

foreach (VendorAddressEntity va in addressesPerVendor)
{
     street1 = va.Address.StreetLine1;
     street2 = va.Address.StreetLine2;
     typeOfAddress = va.AddressType.Name;
}

The example uses a collection. That is because the filter you specified may return more than one results (you want addressType but filtering on addressId, and due to your schema that may return more than one result ).

Thanks for the response.

I kind of thought that was the going to be the direction of the answer, though the data model uses many join tables, some are more for separation of objects then actual need for a many to many relationship.

In this case the sql I wrote does return the one row, the reason I am trying to get this into just one entity is that in many cases we are passing an object between user controls, which is great until one needs an Entity like my example made up of several tables but will always return just one row.

I am also realizing that I need to rewrite many of the collections I have that use relations, and includeFields with prefetchpaths.

From what I can tell, relations are used for filtering, and prefetch is used for getting data, is this correct.

This is my first project with LLBLGen Pro so I am learning on the job.

Todd

Mostly correct - relations are really like "JOIN" statements in SQL, so when you add relations to a predicate bucket you are really adding additional related tables to the query - the usual reason for doing this is,as you say, to filter on data in the related tables.

Prefetch paths are indeed for getting related entities.

Of couse to make things complicated there is nothing to stop you adding relations to pre-fetch paths so that you can filter the related entities on data in other related tables as well