Injecting expressions into UnitOfWork Query

Hello,

Consider the following: http://msdn.microsoft.com/en-us/library/dd744842.aspx

Is there a way in LightSpeed where I can add an interceptor to a UnitOfWork so I can add additional expressions to a query for any given entity? For example, I have numerous entities that have a ValidFrom and ValidTo field, indicating that the data of the entity is valid for a specific time. By default, I want to return only valid entities. However, I have to write a query for every entity type which is time consuming and error prone.

Another example would be that an entity is securable and I should only return entities the user is allowed to see. In this case, I have to inject a rather complex expression to look at the ACL and current logged in user to see whether they can perform the operation or not.

Due to the complexities of these expressions, I'd rather write them once.

Thanks, Werner

Overriding Find(Query, IList) in your unit of work class will handle most cases:

partial class ModelUnitOfWork {
  public override void Find(Query query, IList results) {
    var restrictedQuery =
      query.QueryExpression
        && Entity.Attribute("ValidFrom") <= DateTime.Now
        && Entity.Attribute("ValidTo") >= DateTime.Now;
    query.QueryExpression = restrictedQuery;
    base.Find(query, results);
  }
}

One thing to watch out for is FindById queries. FindById queries are precompiled, so execution does not pass through the Find method and the query cannot be intercepted or modified. This will have an impact on associations: when you traverse A.B, we do a FindById, so if a current A is associated with a non-current B, you won't be able to intercept the load of the non-current B. (Of course you can work around this in application logic e.g. implement checking in the A.B getter.)

Another problem will be eager loaded associations. Although you can modify the query expression to return only permitted top-level items, the generation of the eager load SQL statements does not go recursively through Find -- the structure of an eager load statement is baked in and there is no way to add clauses to it. So if a current top-level item eager-loaded a non-current child then you would have to detect and hide this in application logic.

The Project, calculation (including Count) and Search methods also do not go through the Find method and would have to be handled separately. (Same with stored procedures but if you are using a sproc then obviously you would build the restriction logic in there directly, so this isn't an issue.)

Is this enough info to get you started?

Not sure this solves the problem. It still requires the expressions to be written all over the place to deal with all the places where its not taken into account. Its not just complicated, but also time consuming to get right.

How does this work for DeletedOn? It seems rather transparent irrespective of the queries. Any chance I can inject additional query at the time you evaluate and inject the expression for DeletedOn?

Not sure this solves the problem. It still requires the expressions to be written all over the place to deal with all the places where its not taken into account. Its not just complicated, but also time consuming to get right.

How does this work for DeletedOn? It seems rather transparent irrespective of the queries. Any chance I can inject additional query at the time you evaluate and inject the expression for DeletedOn?

Another question: In the case of overriding the Find method, how do I know which type I'm dealing with and whether it actually has a ValidFrom and ValidTo field. Not all entities have these fields.

Yes, I'm exploring whether we can provide a hook at a deeper level similar to where we handle soft-delete. That would also take care of the eager load issue. But it will take a bit longer to get you a response on that.

Regarding the Find approach, use Query.EntityType to get the type being queried. You can then use a custom attribute, or the Metadata API, or whatever, to determine whether to perform filtering.

Ivan,

Given the complexity, I'd be happy to wait a couple of days.

One concern with overriding methods of an UnitOfWork is that it scatters business logic across several classes. It would be appreciated if I can implement the hooks via attributes on the entities (or its base classes), such as:

[Interceptor(typeof(SecurableQueryInterceptor)]
public interface ISecurable 
{
    // properties, if any
}

[Interceptor(typeof(TemporalQueryInterceptor)]
public interface ITemporal
{
    public DateTime ValidFrom { get; set; }
    public DateTime ValidTo { get; set; }
}

public class TemporalQueryInterceptor : IInterceptor
{
     // not sure what this signature would look like
     public void OnQuery(Query query)
     {
         // add an expression to the query so only valid objects are returned.
     }

     // other queries
}    

public class SecurableQueryInterceptor : IInterceptor
{
     // not sure what this signature would look like
     public void OnQuery(Query query)
     {
         // add an expression to the query to return only objects current logged in user has 
         // access to.
     }

     // other queries
}    

public class Address : Entity<long>, ITemporal
{
   // implement address
}

public class Coupon : Entity<long>, ITemporal, ISecurable
{
   // coupon implementation
}

The use case for me is that whenever I query Coupon as follows:

IUnitOfWork unitOfWork = context.Create();
var coupons = unitOfWork.Query<Coupon>();
var coupon = unitOfWork.FindById<Coupon>(10);

that both interceptors are executed and the query to the database includes both expressions to return valid coupons and only those the current logged in user may see. For me, the key is that the queries are written only once.

I understand that this is rather complicated, however it will simplify implementing systems that involve security, multi-tenancy etc.

Taking a look at this now, and a couple of thoughts come to mind.

1. Do you envisage circumstances in which you want to modify a query, or only circumstances where you want to perform additional filtering? The former obviously permits the latter, but requires the developer to write something like query.QueryExpression &= Entity.Attribute("Owner") == activeUser;, whereas a filtering-specific API allows you to just write return Entity.Attribute("Owner") == activeUser; and we can take care of and-ing it with any existing query expression. Trading off flexibility for convenience.

2. How do you see parameterising interceptors/filters? For example, consider the "securable object" scenario: the interceptor/filter will need the identity of the active user. How would you like to be able to do this? Do you expect to get required info from a service locator, or should we pass some additional info to the OnQuery call?

Okay, there is a preliminary implementation of this going into tonight's nightly build. Apply the new QueryFilterAttribute to an entity class or an interface, passing a type which implements IQueryFilter. IQueryFilter contains just one property, Filter: LightSpeed will 'and' the returned filter with the user-specified query.

Example (not a realistic one but should give you the idea):

// Model

[QueryFilter(typeof(OnlyTheYoung))]  // can also be applied directly to entity classes
public interface IAged
{
  int Age { get; }
}

public class Penguin : Entity<int>, IAged { /* ... */ }

public class OnlyTheYoung : IQueryFilter
{
  public QueryExpression Filter
  {
    get { return Entity.Attribute("Age") < 10; }
  }
}

// Usage

_unitOfWork.Find<Penguin>(Entity.Attribute("Name") == "Gloria");
// resulting SQL: ...WHERE (Penguins.Name = 'PPOG' AND Penguins.Age < 10)

I stress that this is a preliminary implementation and we are definitely open to discussing the design and the API to ensure it works nicely with situations like multi-language or multi-tenant applications. In fact from initial testing I expect we will need to change it to support some kind of parameterisation, but I wanted to get it out to you for a bit more real-world feedback before we went any further. You should also treat it as beta in terms of testing -- don't be too surprised if there are some bugs in there, and please let us know! (I know your real model is quite complex, but if you are able to create simple repros for any bugs you hit, that would be really useful.)

Hi Ivan,

Thanks for looking into this.

As for the questions, what happens if a single entity implements two or more interfaces each with their own QueryFilter? Are they appended? I do see a value to inject IoC into the QueryFilter because, as in your example, the age "10" may be configurable.

Considering your example, how would the IQueryFilter look like for cases where what's young may be configurable based on the locale of the current logged in user. Something like:

class User : Entity<long> { /* ... */ public Locale Locale { get;set; } }
class Locale : Entity<long> { /* ... */ public int YoungAge { get;set; } }

An example of where this is applicable is the drinking age that differs from one country to another, from one state to another or even from one city to another.

As for queries, implementing IQueryFilter means that through code I'll never be able to get access to Penguins 10 or older. Can we have something similar to IncludeDeleted(). Assume I call this IncludeAllAges() which may look as follows:

public static class LightSpeedExtensions
{
    // not sure what this actually look like
    public static IQueryable<T> IncludeAllAges<T>(this IQueryable<T> query)
    {
        query.IgnoreQueryFilter(typeof(OnlyTheYoung));
        return query;
    }
}

Then for the "only" case where for reporting I need to return all penguins, even those considered "old", I may be able to write something like this:

var allPenguins = _unitOfWork.Query<Penguin>().IncludeAllAges().OrderBy(c => c.CreatedOn);

This means it has a similar feel to handling soft deletes. Is something like this possible?

Werner

At the moment, we "and" together all filters specified on the class or any interfaces it implements. I currently have a check in there that you don't specify the same filter type twice, but on reflection I don't think this is useful and will probably remove it.

Yes, we probably do need a way to turn off filters for admin-type scenarios. However, it might be better handled by having the filter itself check for a 'suspend filtering' situation. Otherwise you end up with code like:

var query = _unitOfWork.Penguins;
if (admin)
  query = query.IncludeAllAges().IncludeNonPermitted().IncludeNonCurrent();

potentially in several places and needing to be updated every time you add a new filter. Whereas if each filter made a self-contained decision to not filter, you wouldn't need to explicitly turn it off at the calling site. Thoughts?

I'd still like to understand better how you would want to inject parameters such as the user id or locale into the filter. At the moment we instantiate the filter globally, so the only way to pass per-query parameters would be via an argument i.e. changing the API to:

QueryExpression GetFilter(Something queryContext);  // but what would Something be?

Alternatively interested filters could reach up into context themselves via a service locator:

public class PermittedOnly : IQueryFilter {
  public QueryExpression Filter {
    get { return Entity.Attribute("AccessCode").In(ServiceLocator.Get<UserContext>().AccessCodes); }
  }
}

(where ServiceLocator and UserContext are application components, not LightSpeed components). Obviously the downside of this is that it makes the filter dependent on a particular global service locator which might impact testing or reuse, though I am not sure this is really significant.

Possibly the compromise is that the Something in option 1 could be an IServiceProvider supplied by the application, though I am not sure how the application would provide it to us. Open to discussion on this!

Hi Ivan,

What about specifying a factory class,

interface IFactory
{
    object Create(Type type);
}

When I configure the LightSpeedContext, then it may look as follows:

LightSpeedContext context = new LightSpeedContext();
context.DefaultFactory = new ServiceLocatorFactory();

You can use the factory to create the instances of IQueryFilter and my implementation will use an IoC container to create the object. This will allow for a IQueryFilter to be written as follows:

public class PermittedOnly : IQueryFilter {
  IIdentityProvider _identityProvider;
  public PermittedOnly(IIdentityProvider identityProvider) { _identityProvider = identityProvider; }

  public QueryExpression Filter {
    get { return Entity.Attribute("CreatedBy") ==  _identityProvider.UserId}
  }
}

I can then inject types into the IQueryFilter subclass as I see fit. Obviously care should be taken not to cache the IQueryFilter beyond the lifetime of the IUnitOfWork otherwise there may be issues that the IQueryFilter instance may hold on to classes (such as IIdentityProvider) that itself is dependent upon an IUnitOfWork.

This model will be consistent with the way entities and other objects are created.

Werner

As for the "IncludeXXX" examples, well the user may often drive that on the administrator user interface. So its not too bad to specify the IncludeXXX on the query. For scenarios where such flexibility isn't required, simply have a property on a LightSpeedContext named "DisableQueryFilters" which is false by default. If true, you ignore all query filters.

Just an idea.

Werner

Okay, there will be a change going in tonight. I've done it a bit differently to your proposal but I think it should still allow you to do what you need.

The change is a new member on the Interceptor class:

public virtual IQueryFilter CreateQueryFilter(Type entityType, Type filterType, Func<IQueryFilter> baseCreator)

This serves as your factory method so your proposed factory class gets rolled into Interceptor (of course if you feel strongly about separation of concerns, or have very complex creation logic, you can create a factory class and have your CreateQueryFilter implementation just call this).

The base class implementation just calls baseCreator, which in turn calls the public default constructor of the filterType class. (The reason for having baseCreator is that it is more efficient than Activator.CreateInstance.) So if you have filters that aren't parameterised, like my original OnlyTheYoung filter, then you don't need to do anything -- everything will work the same as last night's build. If you have a filter such as PermittedOnly which needs to be parameterised, then you can override the creation for that specific type:

public class MyInterceptor : Interceptor {
  public override IQueryFilter CreateQueryFilter(Type entityType, Type filterType, Func<IQueryFilter> baseCreator) {
    if (filterType == typeof(PermittedOnly)) {
      return CreatePermittedOnlyInstanceUsingIoCOrWhatever();
    }
    return baseCreator();
  }
}

If efficiency is a huge concern then you can probably return a singleton rather than calling baseCreator though in my testing it didn't seem to be worth the candle. (CreateQueryFilter is called on every query, so efficiency is important, but the baseCreator delegate is pretty efficient. If creation is expensive for your filter, you may want to cache filter instances in your factory.)

To install your interceptor/factory, set LightSpeedContext.Interceptor.

As always we welcome your feedback on this API.

(Regarding turning off filtering in a Query object, I'm still mulling approaches to this. However, returning null from IQueryFilter.Filter will be treated as 'no filter,' so you can use this plus parameterisation as a way of turning off a filter for now.)

Hi Ivan. This is awesome.

I'd like to suggest other interceptors to simplify business logic. Considering my earlier example of ITemporal

interface ITemporal
{
   DateTime ValidFrom { get;set;}
   DateTime ValidTo { get; set; }
}

I'd like to be able to specify a type to intercept events on the entity, such as save and validate. I'm currently overloading SaveChanges (and Validate) on entities and IUnitOfWork. As a result business logic is scattered (and somewhat duplicated) across numerous classes. If I can specify say a validator interceptor on the ITemporal interface, then I can write the validation logic (that ValidTo is always after ValidFrom) once. In the case of intercepting the SaveChanges event, I can write the code once to initialize ValidFrom with DateTime.Now and ValidTo with DateTime.Now.AddDays(1) when the entity is new. It gets more complicated than this.

Any thoughts? I appreciate the quick response.

Werner

This is a very interesting idea and it's something I'd like to follow up, but unfortunately we are very tight on resources at the moment. I'll try to have a look and see if it can be done quickly, but I can't make any promises.

I appreciate that. It will reduce and simplify my database code a lot.

Hi Werner,

Just to let you know, we are now adding an option to turn off query filtering without needing to go through the interceptor/IoC infrastructure. The next nightly build will have a Query.IncludeFiltered property and LINQ IncludeFiltered() operator. These work in a similar way to IncludeDeleted but apply to user-defined filters.

I initially considered a way of turning off or passing control data to individual filters, but decided that if you wanted to go down to that level of detail you might as well just turn off all filters and write the query explicitly. I am open to feedback on this -- you are probably the most in-depth user of this feature and I'd be interested to hear if you have come across use cases for fine-grained filter control which are not well served by 'in that case write a custom query.'

Note that such a feature would probably require a change to at least one of the existing APIs but if that's going to happen we'd rather get it out of the way now before too many people are using the feature!

(And no, we haven't forgotten about the interface-driven validation proposal, just need to make the time!)

I was just about to ask about this.

Thanks mate.

Werner

Ran into a gotcha: these aren't being applied during a Join yet. I need this for the "SELECT blah INNER JOIN member ON member.DeletedOn IS NULL AND member.IsActive" case. Thanks!

Thanks for alerting us to this. A candidate fix will be in tonight's nightly build. It's been tested with simple cases; if you still see problems, could you provide us with a test case in the form of a minimal but complete and buildable console or NUnit project please? Thanks!

Everything looks great on the join fix, thank you.

I currently have a check in there that you don't specify the same filter type twice, but on reflection I don't think this is useful and will probably remove it.

Don't think this is related but if I put:

[QueryFilter(typeof(QueryFilterOne))]
[QueryFilter(typeof(QueryFilterTwo))]
public partial class Member ...

I get "Duplicate 'QueryFilter' attribute." The old AllowMultiple=true problem:

[AttributeUsage(AttributeTargets.Class | AttributeTargets.Interface) Inherited = false, AllowMultiple = true)]
public sealed class QueryFilterAttribute : Attribute ...

Also, in practice the FindById limitation is a little unsettling. I realize you may not want to change that architecture for performance reasons. But would it be possible to throw an exception if I do a FindById on a filtered entity (possibly with IncludeDeleted and IncludeFiltered overrides)? I think the exception prevents some bad things from happening and also preserves some compatibility wiggle room for you in the future.

FindById<T>(int Id, bool IncludeDeleted=false, bool IncludeFiltered=false)

We don't currently enable multiple QueryFilterAttributes because we assume that if you're applying a query filter directly to the class then it will be written for that class; if you need to compose filters then we assume they will be inherited from interfaces that represent the composed behaviours. However we could certainly enable this if you want to compose filters directly on the class. What's your use case?

What is "the FindById limitation"? I can find only one reference to FindById in this thread, and that refers to my initial suggestion to Werner of overriding the Find method. Using query filters obviates this issue: query filters are applied to FindById queries including those arising through association resolution.

You cannot apply IncludeDeleted or IncludeFiltered to FindById. If you want to perform an Id-based lookup but admit deleted or filtered entities, use a normal query and specify the Id as part of the query (e.g. Where(e => e.Id == 123)).

I implemented a modified soft delete with one filter over a series of classes; they share a common filter. One needed an additional filter. I already handled this locally so there's no need for a change if there's actual code required on your site as opposed to just adding a flag.

I thought I didn't see the filter on one query and assumed (incorrectly) after reading the above that FindById didn't honor the query filter. I purposely use FindById when possible so this had me concerned I wouldn't catch all the necessary cases.

Thanks as always for the clarification and fast responses.

Nope, no code needed on our side; I've flipped the AllowMultiple bit and it will be included in the next nightly build.

After some more coding using the filter expressions, consider:

from invoice in uow.Invoices
where invoice.CustomerId = id

versus

from invoice in uow.Invoices
where invoice.Customer.Id = id

The second one explicitly joins the customer table and gets the filter; the first one does not. I don't have a strong opinion here other than to note that I haven't found the first case useful.

Both means the same, however have different outcomes. I think that may cause some issues in terms of maintenance without anyone spotting a ".". Reminds me of programming in COBOL and having a "." in the wrong place, causing the program to behave differently.

Ivan,

Is there a chance we can use the same infrastructure to overwrite events such as saving and deleting. Here is my use case:

public interface ITemporal
{
    DateTime ValidFrom { get; set; }
    DateTime ValidTo { get; set; }
}

public class Registration : Entity<long>, ITemporal
{
    // implementation
}

public class PasswordResetRequest : Entity<long>, ITemporal
{
   // implementation
}

public class TelephoneNumber : Entity<long>, ITemporal
{
   // implementation
}

Now I have tens of entities implementing ITemporal. The IQueryFilter interface does a great job to permit me to filter the those entities and only return those that are current. However, I need also to initialize those values when a new entity is saved. Further, when the entity is deleted, I'd like to change it so its updated and change the ValidTo field to the current time. This is similar to soft deletes, except it is closer aligned to how the business may see temporal objects.

I also have a number of entities implementing ISecurable, which simply implies that the entity has permissions on it and it should be checked whenever I want to create, modify, delete or retrieve objects. I'd like to see if its possible to implement those checks using this scheme, making it simpler to reuse amongst tens of entities. This would allow me to code against IUnitOfWork while resting assure that my business logic is implemented correctly.

The alternative requires a lot of additional code and requires more code.

Werner

I forgot to add that I also need to intercept validation (like the ValidFrom can't be after ValidTo).

Werner

Both means the same, however have different outcomes.

Yeah, I think it should force a join in both cases if there's a filter on the entity. FindById does it, so should this.

I just did a LINQ query with a join and a complicated where clause. The equivalent of:

from invoice in uow.Invoices
where invoice.Customer.Id = id

Got simplified/optimized into invoice.CustomerId on the where clause and the filters weren't applied.

Ivan: I'm sure you getting a bit sick of the Filter stuff by now, but it fundamentally reshapes the way you can build things with LightSpeed. Today I was able to convert an app to be multi-tenant in about ten minutes. Added a table for the tenants, added a membership table linking members to tenants, added TenantId to every table, and then slapped a QueryFilter on the base class. Just worked! Except...

Do you envisage circumstances in which you want to modify a query, or only circumstances where you want to perform additional filtering? The former obviously permits the latter, but requires the developer to write something like query.QueryExpression &= Entity.Attribute("Owner") == activeUser...

I wasn't part of that original discussion, but here's your scenario. 36 out of 37 tables work with just the pure filter, but I also need to make sure that not only Member.IsActive == true but also that Tenants.Contains { Member.Id, CurrentTenantId }.

Just following up on this and wanted to clarify what was outstanding here as there seems to be a few things being discussed on the thread.

Thanks!
Jeremy

I cannot speak for bloudraak (he got the discussion started with Ivan). I merely saw the new functionality and jumped on it as an early adopter.

I have two issues:

First, sometimes a WHERE or JOIN does not apply the filter. I initially worked around it by changing something like:

from invoice in uow.Invoices
where invoice.CustomerId = id

to

from invoice in uow.Invoices
where invoice.Customer.Id = id

But even that doesn't guarantee the filter gets applied. Sometimes LightSpeed is smart enough to realize it doesn't need any extra fields, and optimizes back to CustomerId. (Maybe if eager load is turned off?)

Regardless, the first code block just needs to work and then everything after should be fine.

My second issue is that the API is based around query.QueryExpression. This was debated briefly before I came in (see Jan 18 post from Ivan) and the first thought was to provide the simplest possible API. But I found a pretty common case where I need to modify the query itself.

That's the state of IQueryFilter as I see it!

Hi Jeremy,

Now that I have used it sucessfully for filtering, I find myself duplicating code for interfaces in other places.

Consider an interface that represents an entity that should be confirmed. For security reasons, only certain people can create these objects. The confirmation code is generated when the entity is created and remains immutable until the entity is purged from the database. When it is saved, an email address should be sent to the recipient with the confirmation code.

It may look like this:

interface IConfirmable
{
    // immutable after creation
    string ConfirmationCode { get; set; }

    // immutable after creation
    string Recipient { get; set; }

    // true if confirmed, false if not
    bool IsConfirmed { get; set; }

    // when it was confirmed
    DateTime ConfirmedOn { get; set; }

    // called when the entity was confirmed
    void Confirm();
}

class AccountRegistration : Entity<long>, IConfirmable
{
}

class PasswordResetRequest : Entity<long>, IConfirmable
{
}

class EmailAddressConfirmation : Entity<long>, IConfirmable
{
}

It simplify my code if I could specify attributes which are called for certain events during the lifetime of the entity. For example:

[EventListener(typeof(EventHandler1))]
interface IConfirmable
{
    /* entities */
}

class EventHandler1 : DefaultEventListener
{
    public void OnCreating() {}
    public void OnCreated() {} 
    public void OnUpdating() {}  
    public void OnUpdated() {}
    public void OnDeleting() {}  
    public void OnDelete() {}
    public void OnValidate() {}
}

The aim here is to generate the confirmation code in the "OnCreate" event, and send the email during the "OnCreated" event. In the "OnModify" event I can write code preventing someong from changing the ConfirmationCode and Recipient fields. In the "OnValidate" event, I can validate that Recipient is an email address and required. I can use the OnModified event to check if the "IsConfirmed" property was set to true and call "Confirm" method.

With this scheme I only have to impelement it once and I don't have to subclass IUnitOfWork. It facilitates reuse.

I can easily implement audting if an entity inherits from "IAuditable" for almost all entities in my object model. If an entity implements "ISecurable", I can use the events to validate that the logged in user has permissions to create, modify or delete the entity. I can also populate the security information when the object is created.

Combining the Query filters with events may cut down my database code by a magnitude while keeping it maintainable, secure and consistent. The idea isn't new, I'm essentially adapting it from NHibernate events for interfaces.

Werner

@chadw:

For your first point, you are using the LINQ provider which does indeed has an optimization to try and avoid unnecessary traversals where you are say joining on Customer.Id to just use the FK field on the source table. Query filters are implemented in the LightSpeed querying API and the LINQ provider merely translates your LINQ expressions into a Query object, so that is what is occurring in these cases. Offhand Im not too sure what you could do about this other than to think that explicitly joining may avoid the problem (maybe).

In regards to the QueryFilter interface, we dont have any plan to change this, but can you give me an example of why you would need access to the Query object?

Jeremy

@bloudraak:

That definitely sounds like a reasonable idea, I will have a think about adding this in.

Jeremy

The QueryFilters need to be predictable and deterministic in order to be useful. "WHERE Customer.Id=..." doesn't pull up records marked soft delete for example. So if I'm excluding records based on a different criteria in the filter (say, Member.Active == true) I need it to behave just like soft delete. I'm pretty sure all of bloudraak's scenarios require this as well.

I'm not sure you can punt on this. One alternative would be to flip it around and make an explicit .WithFilter instead.

The second scenario is pretty common: the field I need to filter on is in a different table. So I need to join.

It is actually being applied like SoftDelete in that it is being appended as criteria for the query. The problem you are running in to is that you are not actually joining the table because the LINQ expression isn't being translated exactly as you think it should be, if you were dealing with a SoftDelete scenario on your joined table it would behave in the same way (assuming you had an invalid FK value to a soft deleted entity in there). If you want to guarantee the behavior you need to use the Querying API. I will have a look back at that optimization since that seems to be the actual issue here (confusion about how things are getting translated) and possibly remove it if it is less relevant now.

For the second scenario you can specify a traversal in the QueryExpression e.g. Entity.Attribute("Association.Property") == Value - presumably that would cover what you are looking to achieve?

Jeremy

@jeremy,

Consider this example involving a ACL (security). I omitted as much as I can to keep it concise.

class Identity : Entity<long> {
    public string Name { get; set;}
    public Principal Principal { get; set;}
}

class Principal : Entity<long> {
    public string Identifier { get; set;}
    public EntityCollection<Identity> Identities { get;}
}

class Permission : Entity<long> {
    string Name { get; set;}
    Principal Principal { get; set;}
    string Discriminator { get; set; }
}

[QueryFilter(typeof(SecurityQueryFilter))]
interface ISecurable {
    Principal Owner { get; set;}
    EntityCollection<Permission> Permissions { get; set;}
}

class MyEntity1 : Entity<long>, ISecurable {
}

class MyEntity2: Entity<long>, ISecurable {
}

class MyEntity3: Entity<long>, ISecurable {
}

class MyEntity4: Entity<long>, ISecurable {
}

class SecurityQueryFilter : IQueryFilter {
    public QueryExpression Filter
    {
        get
        {
            // get the principal from somewhere.
            string name = Thread.CurrentPrincipal.Identity.Name;

            var unitOfWork = ServiceLocator.Current.GetInstance<IUnitOfWork>();

            // Find the identity in the database for the given name.
            var currentIdenity = unitOfWork.Query<Identity>().SingleOrDefault(identity => identity.Name == name);
            var currentPrincipal = currentIdentity.Principal;

            // get permissions
            var permissions = unitOfWork.Query<Permission>().Where(permission => permission.Name == "READ" && permission.Discriminator == "?????");

            // Find  the principals with read permissions
            var principals = permissions.Select(p => p.Principal).Distinct();

            // Create a query where the current principal is either the owner of the resource, or is in the list
            // of principals with "read" access to the resource.      

            return ????;
        }
    }
}

The challenge here is how to implement SecurityQueryFilter such that it can be reused for MyEntity1, MyEntity2, MyEntity3 and MyEntity4. Notice that I have added "????" in places I don't know what to do. The idea being to return entities for which the current principal is the owner or the current principal has "read" permissions for the entity. Its a classic security query and its this type of complex filter that makes this feature invaluable.

Perhaps this is what @chadw had in mind too.

Werner

PS. For these queries to work, one has to "inject" common logic whenever the entities are created, deleted or updated. You may see the value of combing event listeners with query filters to encapsulate the requirements of ISecurable.

More details: I was doing a join and suddenly inactive members showed up. I checked the SQL and there was no query filter. I've never seen deleted members show up in such a query (nor would I expect to) so I was surprised by the behavior. I changed the join from CustomerId to Customer.Id and it worked. Then I hit another query where even that didn't work. I don't think you can expect users to go this deep with diagnosis. The filters need to work predictably. If they only work for the Query API then, well, ok ... but you should bypass them always from LINQ and document as such. It's just too weird otherwise. Hopefully you can enable LINQ support.

On the other issue, some specifics: I used the query filters to convert an app to be multi-tenant. I added a Sites table, and added a SiteId to my base entity class. By applying a query filter to the base class so that Site.Id == CurrentSideId, 36 out of 37 tables worked. Great start! Now, members:

A member can belong to multiple sites. So there's a Memberships table: Id, MemberId, SiteId. I need to join that table to filter members that don't belong to the current site.

Can I do that solely via QueryExpression? If not, is there an alternate table structure that would work?

As a followup (Werner posted nearly simultaneously) what we have now isn't really a query filter but an entity filter. In the proposed event model, it would be an OnFilter event, passing the Entity.

Werner and I outlined scenarios where what we really want is more like an OnQuery event, passing the Query.

Id be keen to get a repro of the case where its missing the query filter on the join, it would be good to understand why this has not been applied and get that fixed up.

The notion of raising an OnQuery event is fine, but as with the earlier suggestion Ill need to have a think about how this will work in practice. At the moment Im keen to get the query filtering as its currently implemented bedded down before we consider extending this concept further.

Jeremy

What we have today has really been beneficial for simple filtering (which is what I originally requested, and Ivan kindly implemented). Thank you for considering this feature in the first place and then implementing it. If this was another company and another framework, we'd be lucky to have it in 2016 and working as we'd expect it to work.

We'd like to expand the feature so we can use it in additional use cases that we didn't foresee in the beginning.

Werner

I'm not evangelizing the event model (I don't think I'd use it) ... I just wanted to clarify that QueryFilters aren't really query filters. :)

I'll dig through my code and see if I can come up with a repro where an entity is being checked for DeletedOn IS NOT NULL yet the filter isn't applied. Maybe you could rig something onto your soft delete unit tests and chase it that way as well?

Attached is a spike I did a while back to see if I can implement eventing. It even works for queries. However, I didn't pursue it because I couldn't intercept validation, when object are created (Entity.GeneratedId), canceling save changes (to indicate that certain changes are not permitted) and ultimately limited reuse.

I hope it helps.

I tried to run these tests in LINQPad, but it didn't seem to honor soft deletes or query filters. Am I doing something wrong?

Here's what I found on the soft delete vs. query filter issue. Assuming the query filter is on "Customer":

from invoice ... join customer on invoice.CustomerId ... select invoice ... where invoice.CustomerId == id

That does not check if {customer}.DeletedOn IS NULL (as expected).

from invoice ... join customer on invoice.Customer.Id ... select invoice ... where invoice.CustomerId == id

That does check if {customer}.DeletedOn IS NULL. However the query filter is in the WHERE, not the JOIN.

If you flip it around:

from customer ... join invoice

Then you get different behaviors depending on how you specify the join (invoice.CustomerId vs invoice.Customer.Id) and the where. At least one of these seems to be the LINQ optimization case you mention above.

I'm not sure if more information would help, but I'm happy to provide -- especially if you can tell me how to make LINQPad spit out the filters.