Is this possible. I have 3 tables (shown below). The escalationlist and employee are subtypes of the contact entity. I would like to have the escalation list to be a group of contacts. If i create a fourth table EscalationListContact and add the escalationlist pk "contactid" and the contact pk contactid as new fields, llblgen throws an error. "Entity 'EscalationList' has multiple relations in the inheritance hierarchy which have the same field mapped onto them: 'EscalationListContact'. How can i go about this.

Contact

(PK)ContactId Name

Employee

(PK)ContactId EmployeeNumber

EscalationList

(PK)ContactId

Some background information. A user needs to be able to assign a contact of any type (application support, it support, etc.) to specific servers, sites, network devices and applications. Additionally I want them to be able to define their own escalation lists containing multiple contacts and schedules which they can assign also. Since the number of contacts and contact types are unknown a seperate table containing the contact and its type is needed. Instead of having 2 fields one for contact and one for escalation list I thought it would be better to have a single point of entry (contactentity) which defines both singular and plural contacts as employee and escalationlist. If there is a better way to do this let me know, however out of all of the possibilities that i have tried, this one I like the best and seems to take the least amount of queries comming from llblgen.

I see the problem with creating the fourth table. Since it contains a foreign key to the EscalationList table and the EscalationList is a subtype of Contact then it indirectly can contain a foreign key to the supertype and all of its subtypes. So instead I added field in the EscalationList table to point to the identity of another table that contains a list of the contacts however i am still not able to populate the entity relations using prefetch path. The only way i can think of doing this is removing the relationship between this table and the EscalationList however I will not be able to traverse the entity. Any suggestions?

I hope I correctly understood your design.

I think the following can be a valid design:

EmployeeEscalationList

EmployeeNumber (FK to Employee.EmployeeNumber if it was a Unique Key, otherwise use Employee.ContactId) EscalationListId (FK to EscalationList.ContactId)

Is this what you have tried to do?

What are the SQL queries that you want to perform/fetch?

Walaa wrote:

I hope I correctly understood your design.

I think the following can be a valid design:

EmployeeEscalationList

EmployeeNumber (FK to Employee.EmployeeNumber if it was a Unique Key, otherwise use Employee.ContactId) EscalationListId (FK to EscalationList.ContactId)

Is this what you have tried to do?

What are the SQL queries that you want to perform/fetch?

Let me try that relationship you described and i will get back to you. Do you know how I would set the prefetch path because I am prefetching up to contact and contact doesn't have any prefetch paths to escalation or employee?

I attached an image of the actual table design. I will try connecting the EscalationList to he IndividualContact instead of the contact and see if that resolves the problem. I think it originally allowed me to do this but I was unable to prefetch when querying sitecontact.

Thanks Brandt

What you suggested works fine. I believe I was having problems with it because I did not set llblgen to clean up the project which was leaving alot of crud in the compiled project. The only problem I still have is that I am having to actually fetch the EmployeeEscalationList because the prefetch paths aren't working the way I would like them to. I will keep trying though as I found the example in the documentation that says it is suppose to work. The only thing different is that I am not starting the prefetch path at the root of the heiarchy but at an entity that relates to the root of the heiarchy so I am not sure at what depth to add the subtype prefetch path. Even still, when i debug and an EscalationList entity subtype I have to cast it to an EscalationList entity before the entity collection for EmployeeEscalationList will even appear.

The only problem I still have is that I am having to actually fetch the EmployeeEscalationList because the prefetch paths aren't working the way I would like them to. I will keep trying though as I found the example in the documentation that says it is suppose to work. The only thing different is that I am not starting the prefetch path at the root of the heiarchy but at an entity that relates to the root of the heiarchy so I am not sure at what depth to add the subtype prefetch path

Please post a code snippet of your trials, so we can help you construct the prefetchPath and its subPaths.

And explain what results you want out of the prefetchPath

A siteentity has a collection of sitecontactentities which relate to contactentities. A escalationlistentity is a subtype of a contactentity. No subpath that i have tried will link sitecontact to escalationlist. The escalation list contains a collection of escalationcontacts that i would like to prepopulate with this fetch so i can drop the "if" statement in the code below.

Thanks

            IC.EntityClasses.SiteEntity _site = new SiteEntity("hou-alf");
            PrefetchPath2 _prefetch = new PrefetchPath2((int)IC.EntityType.SiteEntity);
            _prefetch.Add(IC.EntityClasses.SiteEntity.PrefetchPathSiteContact);
            _prefetch[0].SubPath.Add(IC.EntityClasses.SiteContactEntity.PrefetchPathContact);
            
            // This is the prefetch path that doesn't work.  There is no error but it doesn't populate the entitycollection
            //_prefetch[0].SubPath[0].SubPath.Add(IC.EntityClasses.EscalationListEntity.PrefetchPathEscalationContact);
            
            
            using (IC.DatabaseSpecific.DataAccessAdapter _adapter = new IC.DatabaseSpecific.DataAccessAdapter(true))
            {
                _adapter.FetchEntity(_site, _prefetch);
                foreach (SiteContactEntity _sitecontact in _site.SiteContact)
                {
                    

                    if (typeof(EscalationListEntity) == _sitecontact.Contact.GetType())
                    {
                        // The reason I have to cast is that eventhough the _sitecontact.Contact entity is of the EscalationListEntity it does
                        // not have the property EscalationContact.  Only after I cast does it have that property. 

                        EscalationListEntity _contactgroup = (EscalationListEntity)_sitecontact.Contact;
                        _adapter.FetchEntityCollection(_contactgroup.EscalationContact, _contactgroup.GetRelationInfoEscalationContact());


                    }
                }
            }

A siteentity has a collection of sitecontactentities which relate to contactentities. A escalationlistentity is a subtype of a contactentity. No subpath that i have tried will link sitecontact to escalationlist.

Since escalationlistentity is a subtype of a contactentity. From sitecontactentities you can prefetchPath contactentity with a TypeFilter for escalationlistentity type. For more info about Type Filtering, please refer to the section Filtering on entity type in the LLBLGen Pro manual "Using the generated code -> Adapter/SelfServicing -> Filtering and sorting -> Advanced filter usage"

Walaa wrote:

A siteentity has a collection of sitecontactentities which relate to contactentities. A escalationlistentity is a subtype of a contactentity. No subpath that i have tried will link sitecontact to escalationlist.

Since escalationlistentity is a subtype of a contactentity. From sitecontactentities you can prefetchPath contactentity with a TypeFilter for escalationlistentity type. For more info about Type Filtering, please refer to the section Filtering on entity type in the LLBLGen Pro manual "Using the generated code -> Adapter/SelfServicing -> Filtering and sorting -> Advanced filter usage"

Thanks for the tip. That helped in one problem but it didn't solve what i needed. If I have relations defined on subtypes and I fetch a collection of the base type I can not prefetch the subtype relations. Lets say you have a person who owns "Autos". "Autos" are a base type for two subtypes : "Car" and "Truck". "Car" has a foreign relation to "ItemInTrunk" and "Truck" has a foreign relation to "BeerCanTypeInTruckBed"; When I querry the user and prefetch the "Autos" owned, I get a person and a collection containing objects from the "Autos" hierarchy, which is perfect. However, I am unable to prefetch "ItemInTrunk" or "BeerCanTypeInTruckBed" with out type checking and casting to its own type and the issuing another query to get the rest of the data for the object. Is this just the way it is or am I missing a way to have the dataadapter issue the queries for me.

Thanks Brandt

Ok I think I figured out where I am having the problem. If I fetch an entity collection of "Autos", then i am able to get the items in the trunk and the beer types. When I fetch a person and ask it to prefetch "Autos" then I am unable to get the items in the truck and beer types directly even though the "Autos" collection is correctly typed. I can deal with that. Is the senario that doesn't work possible?

Lets say you have a person who owns "Autos". "Autos" are a base type for two subtypes : "Car" and "Truck". "Car" has a foreign relation to "ItemInTrunk" and "Truck" has a foreign relation to "BeerCanTypeInTruckBed"; When I querry the user and prefetch the "Autos" owned, I get a person and a collection containing objects from the "Autos" hierarchy, which is perfect. However, I am unable to prefetch "ItemInTrunk" or "BeerCanTypeInTruckBed" with out type checking and casting to its own type and the issuing another query to get the rest of the data for the object. Is this just the way it is or am I missing a way to have the dataadapter issue the queries for me.

Would you please post a code snippet of the preftechPaths you were trying to use?

// This doesn't work

                ActiveDirectoryUserEntity _user = new ActiveDirectoryUserEntity();
                _adapter.FetchEntityUsingUniqueConstraint(_user, new PredicateExpression(IC.HelperClasses.ActiveDirectoryUserFields.UserId == "halamerica\\hbbz733"));
                _contact.ContactGuid = _user.ContactGuid;
                IRelationPredicateBucket _bucket = _contact.GetRelationInfoAssignedContact();
                PrefetchPath2 _prefetch = new PrefetchPath2((int)IC.EntityType.ContactEntity);
                _prefetch.Add(IC.EntityClasses.ContactEntity.PrefetchPathAssignedContact);
                _prefetch.Add(IC.EntityClasses.NetworkDeviceContactEntity.PrefetchPathNetworkDevice);
                _adapter.FetchEntity(_contact, _prefetch);


// This does work

                IC.HelperClasses.EntityCollection<IC.EntityClasses.AssignedContactEntity> _assignedcontact = new IC.HelperClasses.EntityCollection<AssignedContactEntity>();
                //AssignedContactEntity.Relations
                IRelationPredicateBucket _bucket2 = new RelationPredicateBucket(IC.HelperClasses.AssignedContactFields.ContactGuid == _user.ContactGuid);
                IPrefetchPath2 _prefetch2 = new PrefetchPath2((int)IC.EntityType.AssignedContactEntity);
                _prefetch2.Add(IC.EntityClasses.NetworkDeviceContactEntity.PrefetchPathNetworkDevice);
                _adapter.FetchEntityCollection(_assignedcontact, _bucket2, _prefetch2);

The code that doesn't work fetches the user which has a collection of assigned contact entities. The assigned contact entity is a super type for site contact, network contact, etc. The site contact has a field site and the network contact has a field network that point to their respective tables. What I am trying to do is fetch it all in one sweep however it only seems to work when I fetch starting at the assigned contact entity and not the contact entity.

Thanks.

// This doesn't work

            ActiveDirectoryUserEntity _user = new ActiveDirectoryUserEntity();
            _adapter.FetchEntityUsingUniqueConstraint(_user, new PredicateExpression(IC.HelperClasses.ActiveDirectoryUserFields.UserId == "halamerica\\hbbz733"));
            _contact.ContactGuid = _user.ContactGuid;
            IRelationPredicateBucket _bucket = _contact.GetRelationInfoAssignedContact();
            PrefetchPath2 _prefetch = new PrefetchPath2((int)IC.EntityType.ContactEntity);
            _prefetch.Add(IC.EntityClasses.ContactEntity.PrefetchPathAssignedContact);
            _prefetch.Add(IC.EntityClasses.NetworkDeviceContactEntity.PrefetchPathNetworkDevice);
            _adapter.FetchEntity(_contact, _prefetch);

Please Try the following:

                PrefetchPath2 _prefetch = new PrefetchPath2((int)IC.EntityType.ContactEntity);
                _prefetch.Add(IC.EntityClasses.ContactEntity.PrefetchPathAssignedContact).SubPath.Add(IC.EntityClasses.NetworkDeviceContactEntity.PrefetchPathNetworkDevice);

Note: You started your questions using a domain model of Employees, Contacts and EscalationLists, then you used Autos, Cars, Truks...etc & now you are using another different model (Netowrk, Device, Contacts...etc) This makes it hard for anyone to follow up this thread.

Yea, sorry about that. I was trying to make the gist of problem clearer however it resulted in a nebulous thread. However your last post worked. For some reason I thought I already tried adding it as a subpath and getting an error but I guess I was wrong. Thanks for the help.