For simplicity sake, I have 3 tables.

Project ProjectId PK int ProjectName varchar

Scheduler scheduleId PK int EventId int EventTypeId FK int ScheduledStart DateTime ScheduledEnd DateTime

EventType EventTypeId int PK Value varchar

The scheduler table is designed to be able to schedule any entity in the database. In this case it will be used to schedule projects. There will always be a weak relation between PK field of Entity being scheduled, in this case Project and and the EventId field of the Scheduler table. Additionally, each entity being scheduled by EventTypeId and in this scenario, the EventType table with have a record for project.

What I need to figure out how to do is have an endpoint that is passed in a Date and build a predicate that gets all EventId's from the Scheduler table that match that date and EventTypeId of 1.

Then return all Projects where projectId = eventId

Looking for how to do this using the built in LLBL Gen pro objects and not linq.

To summarize at a higher level, I need an endpoint that takes in a date, my end point will then use that date to find all records in the Scheduler table which ScheduledDate matches the data passed in and then uses the EventId's out of all the matching records to then generate a query to pull all records back from the Project's table where the ProjectId == EventId's.

Thanks.

That scenario seems a candidate for TargetPerEntityHierarchy

Other thing you can do is fetch the Project EntityCollection with a filter (RelationPredicateBucket). In that filter you can use FieldCompareSetPredicate where you subquery the Schedule items.

daelmo wrote:

That scenario seems a candidate for TargetPerEntityHierarchy

Other thing you can do is fetch the Project EntityCollection with a filter (RelationPredicateBucket). In that filter you can use FieldCompareSetPredicate where you subquery the Schedule items.

Looked at the link and doesn't appear to do what I need. If that's the way could you not post an example of code that would work?

I'm evaluating this product as well as support. Is your support policy just to link to other things instead of taking what somebody has posted and providing them with a solution or saying it doesn't work. Point of purchasing a product like this is to take out the guess work and having to mix and match to try and figure out a solution. I'm ok getting directed to the help files which I've already checked in most cases. If there is a 1 to 1 mapping between what's contained within the link with what is being asked, that's fine. But a shot off on something that "May" work doesn't classify as good support.

With that said, is there anything about what I asked that doesn't make sense in helping with you provide an example of what I asked, if that's the case please let me know.

Thank you.

frosty wrote:

daelmo wrote:

That scenario seems a candidate for TargetPerEntityHierarchy

Other thing you can do is fetch the Project EntityCollection with a filter (RelationPredicateBucket). In that filter you can use FieldCompareSetPredicate where you subquery the Schedule items.

Looked at the link and doesn't appear to do what I need. If that's the way could you not post an example of code that would work?

I'm evaluating this product as well as support. Is your support policy just to link to other things instead of taking what somebody has posted and providing them with a solution or saying it doesn't work. Point of purchasing a product like this is to take out the guess work and having to mix and match to try and figure out a solution. I'm ok getting directed to the help files which I've already checked in most cases. If there is a 1 to 1 mapping between what's contained within the link with what is being asked, that's fine. But a shot off on something that "May" work doesn't classify as good support.

With that said, is there anything about what I asked that doesn't make sense in helping with you provide an example of what I asked, if that's the case please let me know.

Thank you.

Could you please be polite? We handle many support calls a day here, and you can easily see we deliver top notch support. That what you asked for isn't provided with links given to you, we're sorry for that. However, be reasonable please and understand that we can't know from everyone here what they already tried, if that's not provided, and you can't ask from us to write everything for you.

I'll look into your question from the topic start.

frosty wrote:

For simplicity sake, I have 3 tables.

Project ProjectId PK int ProjectName varchar

Scheduler scheduleId PK int EventId int EventTypeId FK int ScheduledStart DateTime ScheduledEnd DateTime

EventType EventTypeId int PK Value varchar

The scheduler table is designed to be able to schedule any entity in the database. In this case it will be used to schedule projects. There will always be a weak relation between PK field of Entity being scheduled, in this case Project and and the EventId field of the Scheduler table.

what does 'weak relation' mean in this case? that 'EventId' points to Project but also to other tables?

Additionally, each entity being scheduled by EventTypeId and in this scenario, the EventType table with have a record for project.

What I need to figure out how to do is have an endpoint that is passed in a Date and build a predicate that gets all EventId's from the Scheduler table that match that date and EventTypeId of 1.

Then return all Projects where projectId = eventId

Looking for how to do this using the built in LLBL Gen pro objects and not linq.

To summarize at a higher level, I need an endpoint that takes in a date, my end point will then use that date to find all records in the Scheduler table which ScheduledDate matches the data passed in and then uses the EventId's out of all the matching records to then generate a query to pull all records back from the Project's table where the ProjectId == EventId's.

I assume the ScheduledStart/End don't have time values and neither is the date passed in. (ScheduledDate isn't a field in the scheduler table)

I'll first refer back to our set of examples on our website. You might not like this answer first, but these examples are there for a reason. For example, the northwind customer order app would give you an idea where to start with your quest how to obtain the data. It will learn you that because you want projects, you should start with Project.

As you want to filter on Scheduler and yuo want to fetch projects, you've to join Project with Scheduler. In v2.6, you can use a DynamicRelation for that. I'll illustrate the code with adapter.

RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(
    new DynamicRelation(EntityType.ProjectEntity, JoinHint.Inner, EntityType.SchedulerEntity, 
            string.Empty, string.Empty, (ProjectFields.ProjectId==SchedulerFields.EventId)));
// the filter defined at the next 2 lines can also be done with a FieldBetweenPredicate()
filter.PredicateExpression.Add(SchedulerFields.ScheduledStart <= dateValue);
filter.PredicateExpression.AddWithAnd(SchedulerFields.ScheduledEnd >= dateValue);

EntityCollection<ProjectEntity> projects = new EntityCollection<ProjectEntity>();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(projects, filter);
}

Frans,

I'm not trying to be inpolite or polite. I'm evaluating your package which includes support. I've been through 3 products over the past couple of weeks and I've grown tired of running down rabbit holes only to find out the short comings.

I expect support of a product to make sure that if they send a link to something it has an example or such that is relevant as a lot of support techs just send out a link and a prayer without investigating whether their answer is helpful or not.

Anyway, I prefer to find out up front instead of wasting time exchanging pleasantries. Your response is what I would expect to start with.

With that said, thank you very much. That gets me a long a little further. However, I also need the records back from the Scheduler table as well Projex table. I had tried code you posted however, instead of the string.emtpy for the aliasRightOperand, I used "Schedule".

But that throws an exception: {"The multi-part identifier \"Projex.dbo.prjx_scheduleEvent.eventId\" could not be bound.\r\nThe multi-part identifier \"Projex.dbo.prjx_scheduleEvent.eventTypeId\" could not be bound."}

My code looks like this: Please read PrjxProject equal to the Project table we have discussed and PrjxScheduleEvent as being the Scheduler table we discussed.

 DateTime dateValue = DateTime.Parse("10/28/2007");
            RelationPredicateBucket filter = new RelationPredicateBucket();
            filter.Relations.Add(
                new DynamicRelation(EntityType.PrjxProjectEntity, JoinHint.Inner, EntityType.PrjxScheduleEventEntity,
                        string.Empty, "ScheduleEvent", (PrjxProjectFields.ProjectId == PrjxScheduleEventFields.EventId & PrjxScheduleEventFields.EventTypeId == 1)));


 EntityCollection<PrjxProjectEntity> projects = new EntityCollection<PrjxProjectEntity>();
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(projects, filter);
            }

            foreach (PrjxProjectEntity p in projects)
            {
                Debug.WriteLine("ProjectId: " + p.ProjectId);
            }

I'm assumming I'm going to have to integrated a Derived table or something. For now I've just built two endpoints to return records from each so I can move on.

Thanks for the reply.

instead of:

filter.Relations.Add( new DynamicRelation(EntityType.PrjxProjectEntity, JoinHint.Inner, EntityType.PrjxScheduleEventEntity, string.Empty, "ScheduleEvent", (PrjxProjectFields.ProjectId == PrjxScheduleEventFields.EventId & PrjxScheduleEventFields.EventTypeId == 1)));

do: filter.Relations.Add( new DynamicRelation(EntityType.PrjxProjectEntity, JoinHint.Inner, EntityType.PrjxScheduleEventEntity, string.Empty, "ScheduleEvent", (PrjxProjectFields.ProjectId == PrjxScheduleEventFields.EventId.SetObjectAlias("ScheduleEvent") & PrjxScheduleEventFields.EventTypeId.SetObjectAlias("ScheduleEvent") == 1)));

as the alias has to be set in the predicates as well, otherwise it doesn't know which element to target (predicates are generating sql fragments without the knowledge of the rest of the query)

Thank you. That gets me past the exception but I can't seem to figure out how to access the scheduleEvent records that come back.

I need to get back records from the PrjxProject table as well as matching records from the PrjxScheduleEvent table.

I took the sql this generates in SQL profiler and it doesn't appear as if it's doing a subselect or anything of the sort.

I've got a work around and make separate call in my code but if there is a better way usig Dynamic Relations I'd love to figure it out.

Thanks.

To fetch the Schedules related to each fetched Project, you'll have to use PrefetchPaths (please look it up in the manual, I'm not gonna post a link ).

But it needs the relation between Project and Schedule to be defined up front in the LLBLGen Pro Designer before generating the code. For this you either should define a custom relation between them in the Designer. Or create what David has suggested to you a TargetPerEntityHierarchy on the Schedule entity, to end up having another entity (let's call it ProjectSchedule), which inherits from Schedule, and uses the EventTypeID as the discriminator field, with value of 1.

In other words, LLBLGen Pro will recognize any Schedule record with an EventID of value 1 as a ProjectSchedule entity, and by creating this it would be absolutly safe to define the custom relation in the designer between the ProjectEntity and the ProjectScheduleEntity.

And thus at runtime/code you can just fetch the Projects as Frans has showed to you, but this time you should pass a PrefetchPath object (pointing to ProjectSchedule) to the fetch method, to fetch the ProjectSchedule entitis as well.