locked
QueryInterceptor with Query Projection Problem RRS feed

  • Question

  • Hi, we are a big user of WCF Data Services and we have a problem with query interceptors. As our API is multi-tenanted we use QueryInterceptor to limit access i.e.

    [QueryInterceptor("Customers")]
    public Expression<Func<Customer, bool>> OnQueryCustomers()
    {
      return p => p.TenantID == GetTenantIDForLoggedInUser();
    }

    [QueryInterceptor("Orders")]
    public Expression<Func<Order, bool>> OnQueryOrders()
    {
      return p => p.TenantID == GetTenantIDForLoggedInUser();
    }

    There is a one to many relationship between Customer and Order - Customer has Many Orders.

    However when you issue a projection query with an $expand statement i.e.

    Orders.Take(1).Select(p=> new {p.OrderName,p.Customer.CustomerId, p.Customer.CustomerName})
    or
    http://localhost/Service.Web.TestApi/TestAPI.svc/Orders()?$top=1&$expand=Customer&$select=OrderName,Customer/CustomerId,Customer/CustomerName

    The query crashes with

    Could not translate expression ..... into SQL and could not treat it as a local expression.

    Either the Expression to be rendered properly or ignore the Query Interceptor if the current entity set is part of an expand statement.

    BTW - this is using Linq2SQL but EF doesn't work either. I have tested on WCF Data Services 4,5.0.2,5.1 & 5.2.0-rc1

    Any suggestions?

    Thanks

    Toby

    Friday, December 7, 2012 1:00 AM

All replies

  • Hi Toby,

    Welcome to the MSDN forum.

    I am trying to involve a senior expert into your thread. Please wait for the response. Sorry for any inconvenience.

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, December 11, 2012 6:54 AM
  • Hi,

    I tried to reproduce your issue with northwind model which has one to many relationship between customer and order:

    1. I defined the following Query Interceptors and wired it to use a known Customer.

            [QueryInterceptor("Orders")]
            public Expression<Func<Order, bool>> OnQueryOrders()
            {
                     return o => o.Customer.CustomerID == "ALFKI";
                    //HttpContext.Current.User.Identity.Name;
            }

            [QueryInterceptor("Customers")]
            public Expression<Func<Customer, bool>> OnQueryCustomers()
            {
                return o => o.CustomerID == "ALFKI";
             }

    2. I tried expanding the Customer Entity as you are:

            <Link>http://localhost:17181/Northwind.svc/Orders()?$top=1&$expand=Customer&select=OrderDate,Customer/CustomerID</Link>

    But, I am not getting any errors/exceptions. Can you share the complete excception/stack trace? Also, can you make sure that you have set your config.SetEntitySetAccessRule correctly for the entities in question?

    HTH!

    Jay



    • Edited by Jay.Akhawri Tuesday, January 1, 2013 3:56 PM
    Tuesday, January 1, 2013 3:54 PM
  • Hey Jay,

    Thanks for the response but after further investigation I built a sample app that uses EF and Linq2SQL and the error only happens with a Linq2SQL provider. I sent over the sample app to Mark Stafford who kindly contacted me.

    We use Linq2SQL as our provider and at the moment we don't have a fix for the problem but it exists in all the versions of WCF Data Services. I'd be happy to send over the sample app to you if you would like a look. Do you have a email address I can send you?

    Thanks

    Toby

    Thursday, January 3, 2013 12:08 PM
  • Toby,

    Apologize for delay. I didn't get a chance to debug this with L2S but going by your explanation, I would suggest that you open a Support case with Microsoft to check if this can be fixed.

    HTH!

    Jay



    • Edited by Jay.Akhawri Wednesday, January 30, 2013 10:09 AM
    Wednesday, January 30, 2013 10:09 AM
  • Let me try to explain what you are doing and make sure I understand that correctly:

    Basically, you want to make sure that for any query targeting the customer set or order set, the user gets to see the customer or order associated with its user Id and nothing else. You need to do this once, because once you have done this for top level sets, you do not need to do this for nested sets in case of expand.

    Here's something you can try: (Not elegant, but might work)

    Your query interceptors are defined on the data service class and there is a new instance of data service class created for each request. You can try to keep track of whether an query interceptor was called, and if its already been called, then do nothing in the query interceptors.

    [QueryInterceptor("Customers")]
    public Expression<Func<Customer, bool>> OnQueryCustomers()
    {
      if (!queryInterceptorCalled)
      {
        queryInterceptorCalled = true;
        return p => p.TenantID == GetTenantIDForLoggedInUser();
      }
    
      return p => true;
    }
    
    [QueryInterceptor("Orders")]
    public Expression<Func<Order, bool>> OnQueryOrders()
    {
      if (!queryInterceptorCalled)
      {
        queryInterceptorCalled = true;
        return p => p.TenantID == GetTenantIDForLoggedInUser();
      }
    
      return p => true;
    }
    

    So basically the query interceptor will only get appended for the first entity set - For nested entity sets (like $expand sets, there is no reason to add a filter since the top level entity sets is already filtered)

    Hope this helps.

    Thanks

    Pratik


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, January 30, 2013 5:49 PM
    Moderator
  • hi Pratik,

    I tried with the logic you provided as mentioned above for the QueryInterceptor expression and I am still seeing the following error on expanded entity. (for the Database: Informix 11.7 fc5). Implementation : Odata V3 using WCF data services 5.x

    NO_DATA [02000] [IBM][IDS/UNIX64] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000&#xD

    Any help on this would be greatly appreciated.

    Thanks



    Tuesday, August 20, 2013 4:04 PM