4.1.14.0327 SD.LLBLGen.Pro.DQE.SqlServer.dll 4.1.14.0327 SD.LLBLGen.Pro.ODataSupportClasses 4.1.14.0327 SD.LLBLGen.Pro.ORMSupportClasses.dll 4.1.14.0327 SD.LLBLGen.Pro.ORMSupportClasses.Web.dll

DotNet 4.0 vs2013 project Adapter template SQL Server 2008 R2

In a previous question (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=22178) we resolved how to add a filter to a collection and its prefetch paths by overriding the FetchEntityCollection method. This is working well now, thanks.

I am now looking how to do the same thing for joined entities and sub clauses that don't use the FetchEntityCollection method.

Is there anywhere else I could get access to the process to add a predicate for these kind of joins/sub clauses? Here is a typical Linq statement:

var q = from r in metaData.RegulationBase
                join i in metaData.IndustryRegulation on r.RegID equals i.RegID
                where r.RegulationCountryRegion.Any(a=>a.CountryCode == countryCode)
                 select r;

The metadata.RegulationBase is now getting the additional predicate from the FetchEntityCollection override, but I also need to add the predicate to both the IndustryRegulation entities and RegulationCountryRegion entities.

I guess I could try and do it in an overridden CreateSelectDQ method, but that looks quite hard as I guess I need to use sophisticated regex to achieve it. Any suggestions?

neilx wrote:

I guess I could try and do it in an overridden CreateSelectDQ method, but that looks quite hard as I guess I need to use sophisticated regex to achieve it. Any suggestions?

You can use the same technique, overriding adapter.FetchEntityCollection. You can add relations into the object parameters.RelationsToUse, and if you have additional filters for the joined entities, add them to the usual parameters.FilterToUse.

I need to detect the entities in the joins, sub clauses and joins in the sub clauses in order to decide whether to add the predicate or not. I am looking for an object in the RelationsToUse collections that would give the entity name and the ability to add a predicate to it, but I don't see it.

Is there source code I can work through to try and see how to do this? Or are there any examples I could read?

UPDATED with example:

Here is a typical example of a Linq statement that I would add a predicate:

            var q =
                (from a in metaData.RegulationBase
                     join a1 in metaData.IndustryRegulation on a.RegID equals a1.RegID
                     where a1.IndCode == 1
                     && metaData.RegulationQn.Any(a2=>a2.RegID == a.RegID)
                     select a)
                    .WithPath(
                        a =>
                            a.Prefetch<RegulationTextEntity>(b => b.RegulationText)
                                .Prefetch<RegulationHeadingEntity>(c => c.RegulationHeading)
                                .SubPath(c1=>c1.Prefetch<HeadingBaseEntity>(c2=>c2.Heading)
                                    .SubPath(c3=>c3.Prefetch<HeadingSetCodeEntity>(c4=>c4.HeadingSetCode)))
                                .Prefetch<RegulationMonitoringReportEntity>(d => d.RegulationMonitoringReports)
                                .SubPath(e => e.Prefetch<TopicBaseEntity>(f => f.TopicBase)));
            return q as IQueryable<T>;

I have already achieved this with the CollectionToFetch and PrefetchPathToUse collections thanks to an earlier forum answer linked above.

The join and the exists sub clause both need && a2.NewResearchDate == ResearchDateFilter to be added also if ResearchDateFilter is not null.

I could go through all our existing application code, modify it to add the predicate where necessary, add tests, and it would work. I am trying to do this in the adapter to avoid that considerable effort on our existing code base and to keep our applications simple even though we are complicating the way data is selected.

Here is what I want to simulate on the above Linq statement:

            var q =
                (from a in metaData.RegulationBase
                     join a1 in metaData.IndustryRegulation on a.RegID equals a1.RegID
                     where a1.IndCode == 1 
&& a1.NewResearchDate == ResearchDateFilter // add this line
                     && metaData.RegulationQn.Any(a2=>a2.RegID == a.RegID
&& a2.NewResearchDate == ResearchDateFilter // add this line
)
                     select a)
                    .WithPath(
                        a =>
                                .SubPath(c1=>c1.Prefetch<HeadingBaseEntity>(c2=>c2.Heading)
                                    .SubPath(c3=>c3.Prefetch<HeadingSetCodeEntity>(c4=>c4.HeadingSetCode)))

                            a.Prefetch<RegulationTextEntity>(b => b.RegulationText)
                                .Prefetch<RegulationHeadingEntity>(c => c.RegulationHeading)                                .Prefetch<RegulationMonitoringReportEntity>(d => d.RegulationMonitoringReports)
                                .SubPath(e => e.Prefetch<TopicBaseEntity>(f => f.TopicBase)));
            return q as IQueryable<T>;

Of course, not shown above is the possibility that nested sub clauses in the CollectionToFetch and PrefetchPathToUse collections might also need this additional filtering.

neilx wrote:

I am looking for an object in the RelationsToUse collections that would give the entity name and the ability to add a predicate to it, but I don't see it.

What about using:

foreach (EntityRelation  rel in parameters.RelationsToUse)
{
    var fkEntity = rel.GetFKEntityFieldCore(1).ActualContainingObjectName;
    var pkEntity = rel.GetPKEntityFieldCore(1).ActualContainingObjectName;      

    ....    
}

daelmo wrote:

What about using:

foreach (EntityRelation  rel in parameters.RelationsToUse)
{
    var fkEntity = rel.GetFKEntityFieldCore(1).ActualContainingObjectName;
    var pkEntity = rel.GetPKEntityFieldCore(1).ActualContainingObjectName;      

    ....    
}

'rel' seems to be something called a DynamicRelation and doesn't cast to EntityRelation I'm afraid.

IDynamicRElation and IEntityRelation both inherits from IRelation. Anyway, check the LeftOperand and RightOperand properties of the DynamicRelation rel.

Please check the Reference manual, on the description of these properties.

Thanks for the guidance. I have ended up with this. It covers joins in the select statement to entities with my custom ITemporalized interface.

It seems to work on a variety of joins but I am worried the relationToUse.AliasRightOperand at the end might break sometime if it should be the left one. Any comments are welcome.

                foreach (DynamicRelation relationToUse in parameters.RelationsToUse)
                {
                    var joinedEntityName = ((EntityField2) relationToUse.RightOperand).ActualContainingObjectName;

                    var entityType = (EntityType) Enum.Parse(typeof (EntityType), joinedEntityName);
                    var entity2 = GeneralEntityFactory.Create(entityType);
                    if (!(entity2 is ITemporalized)) continue;

                    var newResearchDate = EntityFieldFactory.Create(joinedEntityName, "NewResearchDate");
                    ((IPredicateExpression) queryParameters.FilterToUse).AddWithAnd(
                        new FieldCompareValuePredicate(newResearchDate, null,
                            ComparisonOperator.Equal,
                            ResearchDateFilter, relationToUse.AliasRightOperand));
                }

I don't follow exactly your last code. However for what you are asking, you can use these properties:

// or LeftOperandIsDerivedTable
if (rel.RightOperandIsDerivedTable)
{
     ....
}
else
{
     ....
}

This way you should know whether to use AliasLeftOperand or AliasRightOperand.

BTW, if you are doing this kind of generic code, you should download the LLBLGen Pro Runtime Framework Reference Manual. It's on LLBLGen Site -> Customer Area -> Downloads -> YourVersion -> Documentattion.