4.0.13.523 SD.LLBLGen.Pro.DQE.SqlServer.dll 4.0.13.0725 SD.LLBLGen.Pro.ORMSupportClasses.dll 4.0.13.406 SD.LLBLGen.Pro.ORMSupportClasses.Web.dll DotNet 4.0 vs2010 project Adapter template SQL Server 2008 R2

In a Linq statement (for example) like:

var entity = from a in metadata.MyEntity where a.RowStatus == "Published" select a;

I want to abstract that filter on Published so the application doesn't need to remember to put it in on every database call. There are 160 tables of which 60+ have the RowStatus column

The entities that have RowStatus implement my ITemporalized interface

public interface ITemporalized
{
    string RowStatus {get;set;}
}

I thought I could override the CreateSelectDQ method in the adapter instance:

protected override IRetrievalQuery CreateSelectDQ(QueryParameters parameters)
{
    ICollection<string> entityNamesOfFields = parameters.FieldsForQuery.GetEntityNamesOfFields();
    var entityWrapper = Activator.CreateInstance("BusinessObjects.Adapter", "BusinessObjects.Adapter.EntityClasses." + entityNamesOfFields.First());
    var entity = entityWrapper.Unwrap();
    if (entity is ITemporalized)
        // add the filter RowStatus == "Published" here
    return base.CreateSelectDQ(parameters);
}

but I am having a lot of trouble working out how to create the FilterToUse. Of course, I don't know if the above method would actually work even if I could work out the code needed. Thanks in advance for any suggestions.

I would override FetchEntityCollection method in a partial class:

using NW.LLBL.MSSQL.Adapter.v40.Common;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace NW.LLBL.MSSQL.Adapter.v40.DatabaseSpecific
{
    public partial class DataAccessAdapter
    {
        public override void FetchEntityCollection(QueryParameters parameters)
        {
            var factory = parameters.CollectionToFetch.EntityFactoryToUse;          
            var dummyEntity = factory.Create();

            if (dummyEntity is IAddress)
            {
                var fields = factory.CreateFields();
                var filter = ((IPredicateExpression)parameters.FilterToUse)
                    .Add(new FieldCompareValuePredicate(fields["City"], null, ComparisonOperator.Equal, "GTM"));
            }

            base.FetchEntityCollection(parameters);
        }
    }
}

NW.LLBL.MSSQL.Adapter.v40.Common is a common project that contains nothing but the IAddress interface, and it's used at DBGeneric and DBSpecific projects. This method is also called when fetching collection using LINQ2LLBL.

(Edit) Your method should work as well for a more generic scenario, but you have to take care in the assumptions you make about the query passed to the method.

How can I get as good as you at this

I'll try it ASAP. One question though. Why is the IAddress interface in a completely separate project? My ITemporalized interface resides in my adapter project. Would I gain by moving it out?

neilx wrote:

One question though. Why is the IAddress interface in a completely separate project? My ITemporalized interface resides in my adapter project. Would I gain by moving it out?

ITemporalized is used by your entities, so it must me used in your DBGeneric Project, if you have that interface in you DBSpecific project, then you must add a refenrence to DBSpecific at your DBGeneric project and that is not a good practice because your DBGeneric is supossed to be independent of your data persistence logic.

UPDATED: See end of this post.

I have tried but get a small problem. The code crashes in the override with a mismatch on the reference for the column in the predicate:

    Query: SELECT NULL AS [AffectedOperations], NULL AS [ArchiveReference], NULL AS [AuthorID], NULL AS [AuthorInformation], NULL AS [ChangedBy], NULL AS [ChangeNotes], NULL AS [CreateDate], NULL AS [CreatedBy], NULL AS [DateLastModified], [LPA_L1].[endDate] AS [EndDate], NULL AS [EnforcingAuthority], [LPA_L1].[IDTopic], NULL AS [IssuingAuthority], [LPA_L1].[LanguageCode], NULL AS [LegislationRepealed], NULL AS [Md5], NULL AS [ModifiedBy], NULL AS [NewResearchDate], NULL AS [PrintedforQc], NULL AS [PrintedforQcby], NULL AS [PrintedforQcdate], NULL AS [ProofRead], NULL AS [ProofReadBy], NULL AS [ProofReadDate], NULL AS [Qc], NULL AS [Qcby], NULL AS [Qcdate], NULL AS [Rapid], NULL AS [RapidComments], NULL AS [ReadyforPublication], NULL AS [ReadyforPublicationBy], NULL AS [ReadyforPublicationDate], NULL AS [ReadyforQc], NULL AS [ReadyforQcby], NULL AS [ReadyforQcdate], NULL AS [RegulatoryCitation], NULL AS [RegulatoryText], NULL AS [Reviewed], NULL AS [ReviewedBy], NULL AS [ReviewedDate], NULL AS [RowID], [LPA_L1].[rowStatus] AS [RowStatus], NULL AS [Sent], NULL AS [SentBy], NULL AS [SentDate], NULL AS [StartDate], NULL AS [Summary], NULL AS [Topic], NULL AS [Translated], NULL AS [TranslatedBy], NULL AS [TranslatedDate], [LPA_L1].[UpdateTypeID] FROM [temporal].[v2_Topic_Text]  [LPA_L1]   WHERE ( ( ( [LPA_L1].[newResearchDate] = @p1)) AND [temporal].[v2_Topic_Text].[rowStatus] = @p2)
    Parameter: @p1 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2013-09-07 00:00.
    Parameter: @p2 : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "Published".

Note: Sorry about all the NULL AS statements. This seems to have appeared in v4 when I exclude/include. I quite like it, but it makes for a big SQL statement

I can add the generated alias to the predicate expression to fix it, but this seems a bit hit and miss:

                    var filter = ((IPredicateExpression) parameters.FilterToUse)
                        .Add(new FieldCompareValuePredicate(fields["RowStatus"], null, ComparisonOperator.Equal,
                                                            "Published", "[LPA_L1]"));

Is there better way to ensure the appropriate reference for the filtered column gets used?

UPDATE: I can find the alias that is being used from parameters:

                    var filter = ((IPredicateExpression)parameters.FilterToUse)
                        .Add(new FieldCompareValuePredicate(fields["RowStatus"], null, ComparisonOperator.Equal,
                                                            "Published", (parameters.FilterRelationsAsBucket).SelectListAlias));

The filter is only appearing on the first collection though. The collections in the prefetch paths seem to drop the filter. I'll try and see why, but any suggestions will help.

Try to override FetchPrefetchPath as well.

Good idea. It looks more difficult though. I'll try it.

Walaa wrote:

Try to override FetchPrefetchPath as well.

At last I have got around to trying this but when I get an entity collection with a prefetch path, this method isn't called for the PrefetchPath collection. I can't see which method in the DataAccessAdapter is used to get a prefetch path collection. The prefetch path is retrieved, so I wonder how? Can you help me?

Neil

Prefetch paths are fetched using a separate class, so that's not going to help. But there's another way to do it: be sure that the predicate you want to have is already in the prefetch path nodes at the beginning.

So if you override public virtual void FetchEntityCollection(QueryParameters parameters) in a partial class of DataAccessAdapter, you can manipulate the parameters object there, which contains the prefetch path as well.

QueryParameters is a public class and has simple properties for you to update. The ones to focus on are CollectionToFetch, FilterToUse and PrefetchPathToUse

CollectionToFetch is the type of collection which will be filled with the entities fetched. This is your starting point to determine whether you need to append the RowStatus == "Published" predicate. If you have to append the predicate, you have to append it to FilterToUse. FilterToUse is never null, so just call 'AddWithAnd' on its value to append the predicate.

Next is the prefetch path. If PrefetchPathToUse is null, skip it, otherwise you have to traverse it. You're using adapter, so you can assume the PrefetchPathToUse is of type IPrefetchPath2. The prefetch path object itself is the rootnode, and it will contain prefetch path elements for each first level nodes. So say you're fetching customers (CollectionToFetch), Orders and OrderDetails in a prefetch path, then the prefetch path object itself represents 'Customers', and it contains 1 prefetch path element, the one for Orders. That prefetch path element, the one for orders, contains 1 prefetch path element, the one for order details. A prefetch path element contains its nodes in its SubPath object. (which is a PrefetchPath2 object). With me so far?

You can obtain the IPrefetchPathElement2 objects by enumerating the prefetch path object (or the one you get from an element's SubPath object).

The IPrefetchPathElement2 objects are the ones you have to append the predicate to if the entities they're for require the predicate. You have to append the predicate to the Filter property, which is a predicate expression and is never null.

Now, how to determine whether the entity the prefetch path node is for requires the predicate? Each element contains a ToFetchEntityType property, which is an int. This is the int value of the EntityType enum from the DbGeneric project.

So you do require a reference to the DbGeneric project from the DbSpecific project. This is ok. Cast the ToFetchEntityType value to EntityType (the enum from DbGeneric project) and pass the EntityType value to GeneralEntityFactory.Create(). You'll get an entity instance back. With that instance, you can check whether you need to create the predicate or not, and if so, append it to the prefetch path element's Filter property.

When you're done with this, you simply pass your manipulated parameters object to the call of the base' method FetchEntityCollection and you're done! No need for overrides of CreateSelectDQ.

To be on the safe side, it's best to first call parameters.Clone() and manipulate the result of that method and pass that object to the base method, so you can be sure the manipulation doesn't have side effects.

Hope this helps you solve this

Terrific. I hadn't appreciated that I can loop through each prefetch path. I am already doing half the job, I simply need to add the looping through PrefetchPathToUse and clone parameters as you suggest. Thanks for the detailed response. Out of interest, here is what I am currently doing for the top level entity collection:

        public override void FetchEntityCollection(QueryParameters parameters)
        {
            if (ResearchDateFilter != null)
                UsePublished = true;
            if (UsePublished)
            {
                var factory = parameters.CollectionToFetch.EntityFactoryToUse;
                var dummyEntity = factory.Create();

                if (dummyEntity is ITemporalized)
                {
                    var fields = factory.CreateFields();
                    if (ResearchDateFilter != null)
                    {
                        var filterNewResearchDate = ((IPredicateExpression) parameters.FilterToUse)
                            .AddWithAnd(new FieldCompareValuePredicate(fields["NewResearchDate"], null,
                                ComparisonOperator.Equal,
                                ResearchDateFilter,
                                (parameters.FilterRelationsAsBucket).
                                    SelectListAlias));
                    }
                }
            }
            base.FetchEntityCollection(parameters);
        }