Hello,

I'm trying to create some sort of filter, which will be automatically added to all queries. This filter already works more or less - at least in simple scenarios, and it's often enough what it's doing. However I would like to extend it even further and to rely on it even in more complex cases.

Where am I stuck now, is extending relations (JOINs) with my filter. I identified at least three different ways how relations are used in our solution:

1. Relation is IEntityRelation 2a. Relation is IDynamicRelation, with OnClause, which is IPredicateExpression 2b. Relation is IDynamicRelation, with OnClause, which is IPredicate

The most easiest case is 2a and it works for me already. All what I need to do is write:

relation.onClause.AddWithAnd(additionalFilter)

However I'm struggling with 1 and 2b. For instance - I cannot add a filter just doing relation.onClause.AddWithAnd() with 2b case, because it's Predicate and not PredicateExpression.. I also cannot replace the whole OnClause, because IDynamicRelation doesn't have the setter for it. I also tried to fully rewrite and replace Relation with my extended Relation, but it also didn't work..

I'm using latest LLBL 4.2, with Adapter approach.

If possible please illustrate your case with a piece of code you want to rewrite/fix.

Ok, so I have QueryParameters parameters, that are coming from DataAccessAdapter -> CreateSelectDQ() method. I'm iterating through parameters.RelationsToUse to get all relations.

private void ExtendRelations(IRelationCollection relations, IPredicateExpression additionalFilter)
        {
            foreach (var relation in relations)
            {
                if (relation is IEntityRelation)
                {
                    // Extend EntityRelations
                    if (some_logic_to_check_if_filter_should_be_added)
                    {
                        // How to add additional expression to a filter?
                    }
                }
                else if (relation is IDynamicRelation)
                {
                    var relation = relation as IDynamicRelation;

                    if (relation.OnClause is IPredicateExpression)
                    {
                        // Extend for IPredicateExpression
                        if (some_logic_to_check_if_filter_should_be_added)
                        {
                            var clause = (IPredicateExpression)relation.OnClause;
                            clause.AddWithAnd(additionalFilter);
                        }
                    }
                    else
                    {
                        // Extend for IPredicate
                        if (some_logic_to_check_if_filter_should_be_added)
                        {
                            var clause = (IPredicate)relation.OnClause;

                            // 1. Not possible - IPredicate doesn't have Add() methods.
                            // clause.AddWithAnd(additionalFilter)

                            // 2. Not possible - OnClause has no setter
                            // relation.OnClause = new PredicateExpression(clause).AddWithAnd(relation.OnClause);
                        }
                    }
                }
            }
        }

Then I have a following Select method:

       var queryFactory = new QueryFactory();

            var q = queryFactory.Create().Select(() => new
            {
                Id = EmployeeFields.EmployeeId.ToValue<int>(),
                Name = EmployeeFields.Name.ToValue<string>(),
                Company = CompanyFields.Company.ToValue<string>(),
                Phone = EmployeePhone.Phone.ToValue<string>(),
                Address = EmployeeAddress.Address.ToValue<string>()
            })
            .From(queryFactory.Employee
                    // IEntityRelation case
                    .LeftJoin(EmployeeEntity.Relations.CompanyEntityUsingCompanyId)
                    
                    // EmployeePhoneFields.EmployeeId == EmployeeFields.EmployeeId - will be produced to FieldCompareExpressionPredicate, which is IPredicate.
                    .LeftJoin(queryFactory.EmployeePhone).On(EmployeePhoneFields.EmployeeId == EmployeeFields.EmployeeId));

                    // EmployeeAddressFields.EmployeeId != Guid.Empty - is a dummy expression in this case, just to illustrate how to have IPredicateExpression instead of IPredicate
                    .LeftJoin(queryFactory.EmployeeAddress).On(EmployeeAddressFields.EmployeeId == EmployeeFields.EmployeeId & EmployeeAddressFields.EmployeeId != Guid.Empty)

        return FetchQuery(q);

The produced SQL query is following:

SELECT dbo.Employees.EmployeeId, dbo.Employees.Name, dbo.EmployeePhones.Phone, dbo.EmployeeAddresses.Address
FROM (((dbo.Employees

--
    LEFT JOIN dbo.Companies ON dbo.Companies.CompanyId = dbo.Employees.CompanyId) 

-- dbo.EmployeePhones.SomeCommonField = 'something' - is my additional filter

    LEFT JOIN dbo.EmployeePhones ON dbo.EmployeePhones.EmployeeId = dbo.Employees.EmployeeId AND dbo.EmployeePhones.EmployeeId <> @p1 AND dbo.EmployeePhones.SomeCommonField = 'something')
        
--
LEFT JOIN dbo.EmployeeAddresses ON dbo.EmployeeAddresses.EmployeeId = dbo.Employees.EmployeeId)

WHERE dbo.EmployeePhones.SomeCommonField = 'something' AND dbo.Companies.SomeCommonField = 'something' AND dbo.Companies.SomeCommonField = 'something'

So.. I need somehow to add my filter to two other JOINs. For now only one case is working as needed.

What's the type of filter you're trying to add? A soft-delete one? (so what is the intend of the filter)

Otis wrote:

What's the type of filter you're trying to add? A soft-delete one? (so what is the intend of the filter)

Not just soft-delete. It's more like a filter, which can be applied automatically on lower level - so even if developer is not aware such filter should be applied, it would be applied anyway. Or if repository method calls are wrapped in some sort of scope - filter should be applied to queries called in this scope. The purpose is to reduce uncertainty caused by different implementations (big distributed development team, different approaches, etc) and reduce amount of manual checks or other logic.

Not sure if it answers your question, but I was asked to give an illustration, and I think I gave quite explanatory example..

I'm still confused with different ways how data can be queried with LLBL, and with dozen similar-different classes/interfaces - that can be used in same places and replace each other, but at the same time - they cannot be abstracted or interchanged. For example IRelationCollection can contain both IEntityRelation and IDynamicRelation in same list, but Remove method is only - Remove(IEntityRelation relationToRemove).. why it cannot be IRelation here? Why in one place I can extend OnClause extension and in another - I cannot, because it's similar, but a bit different object (IPredicate vs IPredicateExpression). I believe there are explanations for that, but anyway.. It's good to have such flexibility when you are using it on a higher level, but I'm struggling with it when I want to dig a bit deeper.

Ksandor wrote:

Otis wrote:

What's the type of filter you're trying to add? A soft-delete one? (so what is the intend of the filter)

Not just soft-delete. It's more like a filter, which can be applied automatically on lower level - so even if developer is not aware such filter should be applied, it would be applied anyway. Or if repository method calls are wrapped in some sort of scope - filter should be applied to queries called in this scope. The purpose is to reduce uncertainty caused by different implementations (big distributed development team, different approaches, etc) and reduce amount of manual checks or other logic.

Not sure if it answers your question, but I was asked to give an illustration, and I think I gave quite explanatory example..

The scenario is clear

I'm still confused with different ways how data can be queried with LLBL, and with dozen similar-different classes/interfaces - that can be used in same places and replace each other, but at the same time - they cannot be abstracted or interchanged. For example IRelationCollection can contain both IEntityRelation and IDynamicRelation in same list, but Remove method is only - Remove(IEntityRelation relationToRemove).. why it cannot be IRelation here? Why in one place I can extend OnClause extension and in another - I cannot, because it's similar, but a bit different object (IPredicate vs IPredicateExpression). I believe there are explanations for that, but anyway.. It's good to have such flexibility when you are using it on a higher level, but I'm struggling with it when I want to dig a bit deeper.

A lot of the things which might look odd are a result of things being added later on (the first version was released in 2003, the framework has been adjusted a lot since then) without breaking a lot of things along the way so sometimes changes are added differently than when we would have added it from the start.

Remove on IRelationCollection isn't really used, one adds relationships to the collection, it's used in a query, and it's tossed away. It's not kept around like a container onto which maintenance is done. Dynamic relations were added later on (in v2.6 I think) than IEntityRelation which was there since the beginning. This caused some things not to be written for IRelation but for IEntityRelation. Where needed we added the IRelation interface but otherwise we left it as-is. The IPredicate vs. IPredicateExpression situation is indeed one of those things where it would have been better if things would have been all there from the start. We started with selfservicing, adapter was added later on. By then we realized IPredicate (which was used mostly in selfservicing's interfaces) was too broad in some cases as alot of times one was passing in a predicate expression (which also is a predicate btw) so in adapter things were mostly focused on IPredicateExpression.

The OnClause situation you're referring to, could you give an example of that?

It's hard to add 'mandatory' filters for queries, as the element filtered might be deeply buried inside a derived table somewhere. We have graph traversers in the framework based on the QueryApiObjectTraverser, which can collect information for you when you e.g. pass in a predicate or a set of fields or a relationcollection. We use them to find inheritance fields and append the query elements with filters for subtypes for example. You can do that too, but I can give you up front the caution that it won't be easy. The main issue is that you have to deeply analyze the query whether there is a filter needed (e.g. your developer might already have appended the filter properly), and if so, where to append it and with the proper aliases. Especially that last bit is important, as it might be there are several references to an entity in the query, all aliased differently (e.g. an entity is joined multiple times) and you then have to append the filter for one of them, which one?

The DataAccessAdapter class (forget selfservicing for this) offers you points where you can intercept a query creation call, by creating a partial class of DataAccessAdapter and then overriding e.g. CreateSelectDQ. There you'll get all elements which are used to build the query. You can then analyze in your overriding method the elements with your traversers and act accordingly (e.g. append a filter here or there) and then call the base method to produce the sql. IMHO that's the best spot to do it. But it won't be easy to cover all bases.