locked
How To Convert Standard WCF Query To WCF Data Services Query RRS feed

  • Question

  • Hi,

    If my explanation is not clear, please let me know.

    I have created an WCF Data Service and would like to use it to retrive records from the DB instead of a standard WCF query (see at the bottom of this message).

    public class CodeGeneratorDataService : DataService<CodelogEntities1>
        {
           
            public static void InitializeService(DataServiceConfiguration config)
            {
                config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
                //config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);
                config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
            }
        }

    This returns all the entities I have in my entity framework model.

    I need to return Product entities and all Option child entities based on the user being Admin or not. This is an extract of the full query I use in the standard WCF query:

    var products = (from p in context.Products
                                    where p.AdminOnly == false || p.AdminOnly == viewAdmin && p.Display == true
                                    orderby p.ProductName
                                    select new Product
                                    {
                                        ProductId = p.ProductId,
                                        ProductName = p.ProductName,
                                        SendEmailAllowed = viewAdmin,
                                        NoExpiryWarningDisplay = p.Options.Where(x => x.OptionKey == "NoExpiryWarning" && (x.AdminOnly == false || x.AdminOnly == viewAdmin)).Count() > 0,
                                        NeverExpireDisplay = p.Options.Where(x => x.OptionKey == "NeverExpire" && (x.AdminOnly == false || x.AdminOnly == viewAdmin)).Count() > 0,
                                        Options = (from o in p.Options
                                                   where o.AdminOnly == false || o.AdminOnly == viewAdmin
                                                   select new Option
                                                   {
                                                       OptionId = o.OptionId,
                                                       OptionName = o.OptionName,
                                                       AdminOnly = o.AdminOnly,

    I think I will have to create Operation methods and Interceptors, something like this but I am not sure how to write the code.

    [WebGet]
    public IQueryable<Product > GetProductsByUserName(string username)
    {
          return ();
    }

    [QueryInterceptor("Product")]
    public Expression<Func<Product, bool>> FilterByAdmin()
    {
     }

    I would appreciate if someone could help with the code.

    Cheers

    Standard WCF Query

    public List<Product> GetProducts(string username)
    {
          CodelogEntities1 context = new CodelogEntities1();
          
          var roleCheck = context.aspnet_Users.Where(u => u.UserName == username).First().aspnet_Roles.Where(r =>   r.RoleName == "Admin");

            bool viewAdmin = false;

             if (roleCheck.Count() > 0)
             {
                        viewAdmin = true;
              }

                    var products = (from p in context.Products
                                    //where p.ProductKey == "PTK5" && (p.AdminOnly == false || p.AdminOnly == viewAdmin
                                    //&& p.Display == true)
                                    where p.AdminOnly == false || p.AdminOnly == viewAdmin && p.Display == true
                                    orderby p.ProductName
                                    select new Product
                                    {
                                        ProductId = p.ProductId,
                                        ProductName = p.ProductName,
                                        ProductKey = p.ProductKey,
                                       ,
                                        NonSpecificAllowed = viewAdmin,
                                        SendEmailAllowed = viewAdmin,
                                        NoExpiryWarningDisplay = p.Options.Where(x => x.OptionKey == "NoExpiryWarning" && (x.AdminOnly == false || x.AdminOnly == viewAdmin)).Count() > 0,
                                        NeverExpireDisplay = p.Options.Where(x => x.OptionKey == "NeverExpire" && (x.AdminOnly == false || x.AdminOnly == viewAdmin)).Count() > 0,
                                        Options = (from o in p.Options
                                                   where o.AdminOnly == false || o.AdminOnly == viewAdmin
                                                   select new Option
                                                   {
                                                       OptionId = o.OptionId,
                                                       OptionName = o.OptionName,
                                                       AdminOnly = o.AdminOnly,
                                                       ProductId = o.ProductId,
                                                       OptionKey = o.OptionKey,
                                                       OptionChoices = (from oc in o.OptionChoices
                                                                        select new OptionChoice
                                                                        {
                                                                            OptionChoiceId = oc.OptionChoiceId,
                                                                            OptionId = oc.OptionId,
                                                                            SelectionName = oc.SelectionName,
                                                                            SelectionValue = oc.SelectionValue,
                                                                            SelectionOn = oc.SelectionOn,
                                                                            OptionChoiceKey = oc.OptionChoiceKey
                                                                        })
                                                   }),
                                        PermissionGroups = (from pg in p.PermissionGroups
                                                            orderby pg.PermissionGroupId
                                                            select new PermissionGroup
                                                            {
                                                                PermissionGroupId = pg.PermissionGroupId,
                                                                PermissionGroupName = pg.PermissionGroupName,
                                                               ,
                                                                Permissions = (from pe in pg.Permissions
                                                                               where pe.AdminOnly == false || pe.AdminOnly == viewAdmin
                                                                               orderby pe.Order_Number
                                                                               select new Permission
                                                                               {
                                                                                   PermissionId = pe.PermissionId,
                                                                                   AdminOnly = pe.AdminOnly,
                                                                                   Description = pe.Description,
                                                                                   PermissionName = pe.PermissionName,
                                                                                   PermissionKey = pe.PermissionKey,
                                                                                   PermissionGroupId = pe.PermissionGroupId,
                                                                                   Selected = pe.Selected,
                                                                                   OrderNumber = pe.Order_Number
                                                                               })
                                                            }),
                                        PermissionSpecs = (from pspec in p.PermissionSpecs  
                                                           orderby pspec.PermissionSpecName
                                                           select new PermissionSpec
                                                           {
                                                               PermissionSpecId = pspec.PermissionSpecId,
                                                               PermissionSpecName = pspec.PermissionSpecName,
                                                               ProductId = pspec.ProductId,
                                                               Perm_PermSpecs = (from ps in pspec.Permission_PermSpec
                                                                                 select new Permission_PermissionSpec
                                                                                 {
                                                                                     PermissionSpecId = ps.PermissionSpecId,
                                                                                     PermissionId = ps.PermissionId
                                                                                 })
                                                           })
                                    }).ToList();

                    return products;
                            }
            }

    Friday, June 3, 2011 9:27 AM

Answers

  • You are on the right track. In your service op, you want to call GetProducts and then return results.AsQueryable().

    [WebGet]
    public IQueryable<Product > GetProductsByUserName(string username)
    {
          return this.GetProducts(username).AsQueryable();
    }

     

    in your query interceptor, you want to filter by the admin rights:

    [QueryInterceptor("Product")]
    public Expression<Func<Product, bool>> FilterByAdmin()
    {

         return p => p.AdminOnly == false || p.AdminOnly == viewAdmin && p.Display == true;
     }

    Regards,
    PQ


    Peter Q. http://blogs.msdn.com/peter_qian
    Friday, June 3, 2011 5:57 PM
    Answerer

All replies

  • You are on the right track. In your service op, you want to call GetProducts and then return results.AsQueryable().

    [WebGet]
    public IQueryable<Product > GetProductsByUserName(string username)
    {
          return this.GetProducts(username).AsQueryable();
    }

     

    in your query interceptor, you want to filter by the admin rights:

    [QueryInterceptor("Product")]
    public Expression<Func<Product, bool>> FilterByAdmin()
    {

         return p => p.AdminOnly == false || p.AdminOnly == viewAdmin && p.Display == true;
     }

    Regards,
    PQ


    Peter Q. http://blogs.msdn.com/peter_qian
    Friday, June 3, 2011 5:57 PM
    Answerer
  • Hi Peter,

    Many thanks for your reply.

    Could you also please shed some light about how to return the child objects of the product object?

    If you see the full WCF query, I am returning an Options collection as a property of the Product object.

    How do I do that in WCF Data Services since I want it to be eager loaded?

    I was reading about the Expand method. Is this what I have to use here? But what about the check for the Option.AdminOnly check? How do I do that?

    Do i need another Interceptor for Options?

    Could you please help with some sample code?

    Cheers  

    select new Product{
                      Options = (from o in p.Options
                                    where o.AdminOnly == false || o.AdminOnly == viewAdmin
                                    select new Option
                                    {
                                           OptionId = o.OptionId
                                            -------
                                            -------
                                    }

     

    Saturday, June 4, 2011 5:30 PM
  • Hi,

    Any help please?

    Monday, June 6, 2011 5:11 PM
  • You are right, you need another interceptor on Options, which filters base on admin rights.

    Then you have two choices:

    1) You can build the expansion into the service operation, that way you always get the expanded result by default. You do this by using the same query you showed above. At this point the query is running against the current data source (EF), so whatever you had working before should continue to work.

    2) You can leave out the expansion and use $expand and $select on your service op, this will require the consumer of the service know when to expand (or expand always). The syntax via URI is this:

    ServiceOpMethod()?username='user name'&$expand='Options, Options\OptionChoice'&$select='Options.OptionsId, ...'

     

    Regards,

    PQ


    Peter Q. http://blogs.msdn.com/peter_qian
    Monday, June 6, 2011 7:59 PM
    Answerer
  • Hi Peter,

    Thanks again for your help.

    1 - So, the other interceptor should look something like this, right?

    2 - I do not need to worry about the name of the QueryInterceptor method, right? Since this method is called automatically by the framework, right?? 

    [QueryInterceptor("Option")]
    public Expression<Func<Option, bool>> FilteOptionAdmin()
    {
       return o => o.AdminOnly == false || o.AdminOnly == viewAdmin && o.Display == true;
     }

    3 - I am confused about how to change the GetProductsByUserName method. I have noticed that you used another method inside it GetProducts(username).

    Do I have to define this method or the framework will take care of that?

    [WebGet]
    public IQueryable<Product > GetProductsByUserName(string username)
    {
          return this.GetProducts(username).Expand("Option").AsQueryable();
    }

    Cheers

    C

    Monday, June 6, 2011 9:32 PM
  • 1. Correct

    2. Correct, but you need to declare the entity set name in QueryInterceptor attribute, i think your entity set is called "Options", rather than "Option"

    3. Within this method, you can issue queries against your underlying data source, which is in this.CurrentDataSource. If you use EF provider, it should be an instance of ObjectContext. Therefore, you can do:

     

    return from p in this.CurrentDataSource.Products where ... select new Product() { ... }

     

     


    Peter Q. http://blogs.msdn.com/peter_qian
    Monday, June 6, 2011 9:41 PM
    Answerer
  • ok Thanks.

    So, will it look somethign like this? Otherwise I am not leveraging the power of WCF Data Service i.e. I prefer not to have to build the objects from scratch.

    [WebGet]
    public IQueryable<Product > GetProductsByUserName(string username)
    {
          return this.CurrentDataSource.Products.Expand("Options");

                                      

    Cheers

    C

    Monday, June 6, 2011 10:02 PM
  • Please try the following:

    CodelogEntities1 context = this.CurrentDataSource;

     

            var roleCheck = context.aspnet_Users.Where(u => u.UserName == username).First().aspnet_Roles.Where(r => r.RoleName == "Admin");

     

            bool viewAdmin = false;

     

            if (roleCheck.Count() > 0)

            {

                viewAdmin = true;

            }

     

            return (from p in context.Products

                    //where p.ProductKey == "PTK5" && (p.AdminOnly == false || p.AdminOnly == viewAdmin

                    //&& p.Display == true) 

                    where p.AdminOnly == false || p.AdminOnly == viewAdmin && p.Display == true

                    orderby p.ProductName

                    select new Product

                    {

                        ProductId = p.ProductId,

                        ProductName = p.ProductName,

                        ProductKey = p.ProductKey,

                        NonSpecificAllowed = viewAdmin,

                        SendEmailAllowed = viewAdmin,

                        NoExpiryWarningDisplay = p.Options.Where(x => x.OptionKey == "NoExpiryWarning" && (x.AdminOnly == false || x.AdminOnly == viewAdmin)).Count() > 0,

                        NeverExpireDisplay = p.Options.Where(x => x.OptionKey == "NeverExpire" && (x.AdminOnly == false || x.AdminOnly == viewAdmin)).Count() > 0,

                        Options = (from o in p.Options

                                   where o.AdminOnly == false || o.AdminOnly == viewAdmin

                                   select new Option

                                   {

                                       OptionId = o.OptionId,

                                       OptionName = o.OptionName,

                                       AdminOnly = o.AdminOnly,

                                       ProductId = o.ProductId,

                                       OptionKey = o.OptionKey,

                                       OptionChoices = (from oc in o.OptionChoices

                                                        select new OptionChoice

                                                        {

                                                            OptionChoiceId = oc.OptionChoiceId,

                                                            OptionId = oc.OptionId,

                                                            SelectionName = oc.SelectionName,

                                                            SelectionValue = oc.SelectionValue,

                                                            SelectionOn = oc.SelectionOn,

                                                            OptionChoiceKey = oc.OptionChoiceKey

                                                        })

                                   }),

                        PermissionGroups = (from pg in p.PermissionGroups

                                            orderby pg.PermissionGroupId

                                            select new PermissionGroup

                                            {

                                                PermissionGroupId = pg.PermissionGroupId,

                                                PermissionGroupName = pg.PermissionGroupName,

                                                Permissions = (from pe in pg.Permissions

                                                               where pe.AdminOnly == false || pe.AdminOnly == viewAdmin

                                                               orderby pe.Order_Number

                                                               select new Permission

                                                               {

                                                                   PermissionId = pe.PermissionId,

                                                                   AdminOnly = pe.AdminOnly,

                                                                   Description = pe.Description,

                                                                   PermissionName = pe.PermissionName,

                                                                   PermissionKey = pe.PermissionKey,

                                                                   PermissionGroupId = pe.PermissionGroupId,

                                                                   Selected = pe.Selected,

                                                                   OrderNumber = pe.Order_Number

                                                               })

                                            }),

                        PermissionSpecs = (from pspec in p.PermissionSpecs

                                           orderby pspec.PermissionSpecName

                                           select new PermissionSpec

                                           {

                                               PermissionSpecId = pspec.PermissionSpecId,

                                               PermissionSpecName = pspec.PermissionSpecName,

                                               ProductId = pspec.ProductId,

                                               Perm_PermSpecs = (from ps in pspec.Permission_PermSpec

                                                                 select new Permission_PermissionSpec

                                                                 {

                                                                     PermissionSpecId = ps.PermissionSpecId,

                                                                     PermissionId = ps.PermissionId

                                                                 })

                                           })

                    });

     

    Note the "CodelogEntities1 context = this.CurrentDataSource;"

     

    Regards,

    PQ


    Peter Q. http://blogs.msdn.com/peter_qian
    Monday, June 6, 2011 11:31 PM
    Answerer
  • Thanks Peter,

    I will try that tomorrow since it is getting really late here.

    Cheers

    c

    Tuesday, June 7, 2011 12:17 AM