Filtering unwanted results from UnitOfWork

Hi,

I am looking for an easy way to filter out results from a query.

In my situation there is a table

[tbCUsers](
[sysUserID] [int] NOT NULL,
[attLogin] [nvarchar](50) NOT NULL)

The table contains logins of users; special users that should not be displayed in application have '!' character at the beginning of their login name (for example "!systemUser").
After reading the documentation and searching in forum I decided to override Find method in UnitOfWork:

public partial class SecurityModelUnitOfWork 
{
  public override void Find(Query query, IList results)
        {
            if (query.EntityType.Name == "TbCUser")
            {
                query.QueryExpression = Entity.Attribute("AttLogin").Like("[^!]%");
            }
            base.Find(query, results);
        }
}

This solution almost works - it filters out strings with '!' at the beginning when below query is run:

 UnitOfWork.TbCUsers.ToList()

but when I run query:

TbCUser user = UnitOfWork.TbCUsers.FirstOrDefault(user => user.Id == x);

where 'x' is number of user, I always get the same user no matter what value of 'x' is.

Can you tell me what I do wrong? Or maybe more robust approach exists to achieve described behavior?

(In my case official version of LightSpeed 5 is used)

Regards, Pawel

Yes, have a look at using "query filters" instead of overriding the UnitOfWork behaviour - see http://www.mindscapehq.com/documentation/lightspeed/Performance-and-Tuning/Intercepting-Queries for more detail about this. Query filters will be applied regardless of what call ends up being used provided its not a Procedure based call or a View being used.

Jeremy

Hi,

Thank you, I managed to use query interception and it works quite well - unwanted results are filtered. There is only one problem - in my case I cannot delete object. I checked how SQL is generated and it seems the problem lies there.

In attachment I am enclosing database schema to show you dependencies between entities.

Filter which I created looks as below, it checks if AttLogin field does not start with '!'.

 public class IsSystemUserFilter : IQueryFilter
    {
        public QueryExpression Filter 
        {             
            get
            {
                return Entity.Attribute("AttLogin").Like("[^!]%");                
            }
        }
    }

[QueryFilter(typeof(IsSystemUserFilter))]
public partial class TbCUser
{
}

User is removed in a standard way:

if (SelectedUser != null)
{        
    UnitOfWork.Remove(SelectedUser);
    UnitOfWork.SaveChanges();
    this.RefreshUsers();
}

Generated SQL; two first operations remove dependencies, these work ok.

DELETE FROM
  toolconfig.TbCRoleUserMap
WHERE
  (TbCRoleUserMap.LinUserId = @p0 AND EXISTS (
    SELECT
      t0.*
    FROM
      security.tbCUsers t0
    WHERE
      t0.sysUserID = TbCRoleUserMap.LinUserId AND
      t0.AttLogin LIKE @p1 ESCAPE '\'
  ));

DELETE FROM
  security.TbCUserGroupMap
WHERE
  (TbCUserGroupMap.LinUserId = @p0 AND EXISTS (
    SELECT
      t0.*
    FROM
      security.tbCUsers t0
    WHERE
      t0.sysUserID = TbCUserGroupMap.LinUserId AND
      t0.AttLogin LIKE @p1 ESCAPE '\'
  ))

But when user is going to be deleted by following query:

  DELETE FROM
  security.tbCUsers
WHERE
  (tbCUsers.sysUserID = 391 AND t0.AttLogin LIKE '[^!]%' ESCAPE '\')

I got an error "The multi-part identifier "t0.AttLogin" could not be bound." - due to 't0.' in generated sql. Is it a bug in Lightspeed? I tried the latest nightly build but the problem also occurs there.

Regards, Pawel

Yes this does sound like a bug - are you able to send through a repro project which triggers this query please and we can use to look into what the issue is here.

Thanks!
Jeremy

Hello,

In attachment is a small repro project.

I am sorry it took me so long but I was out of my office for the last two weeks.

Regards, Pawel

Thanks for sending through the repro project. This was indeed a bug and Ive added a fix which will be available in the next nightly build.

Jeremy