Change default published sets in a WCF data service RRS feed

  • Question

  • I have a database with very big tables(some of them may have more than 1,000,000 records) and every user of this database should see some of this data, so we have multiple TVF(Table valued function) that get a user ID and select those records of the table that is visible to that user(this operation require multiple SELECT statements and I think calling TVF is far better than implementing it in the code). In first release of the program to my clients, I had a class with multiple properties of type IQueryable that implemented using LinqToSql and it worked great. Now I have a client that want to use my assembly to write a WCF data service, so I have to write a class derived from DbContext(using EF) that can be used in specified service. My problem is:

    • DbContext automatically expose all DbSet properties that defined in it, so every user with minimum level of access can see entire data of the table(of course client app will restrict data, but client can access data directly and even import those data to an Excel or Access using OData)

    • I have multiple public properties of type IQueryable but they will not appeared in the list of data that exposed by WCF data service.

    In order to solve this problem, I think the most complete solution is to be able to call TVF as a table and create a set from it. But I don't know how to do this?!

    Note Any change to the database require some logging, so I have stored procedures to do those changes, so I only require read only access to my WCF data service and I don't want default sets that contain all records of the tables published in the service

    Wednesday, August 14, 2013 9:02 PM

All replies

  • Hello Mehdi,

    Welcome to MSDN Forums. 

    >In order to solve this problem, I think the most complete solution is to be able to call TVF as a table and create a set from it. But I don't know how to do this?!

    Entity Framework 5 does not include Code First support for TVFs. We could go to Customer Feedback Site to vote and speed up its process. I would like you to refer those references below:

    Customer Feedback for Entity Framework Feature Suggestions.

    Use TVFs with Database First.

    Best Regards,

    Thursday, August 15, 2013 9:25 AM
  • Hi and thanks for your answer.

    I'm using Database First model and I also know how to use TVF in Database First model. But the problem is:

    1) WCF data service will show all DbSet that created from the database and because my data is sensitive and only users with certain permissions may see them, this will create a security problem in my application. So every user with minimum permissions may see all of the records in my database, but I only want a sub set of records to be available to the user! use can even import all the data to an excel or access files using OData import feature.

    2) WCF data service won't publish TVF data, so I'm only able to call it in the code and user won't be able to import it in Excel and Access using OData import feature.

    Friday, August 16, 2013 4:15 PM
  • Hi Mehdi,

    Why don't you try Stored Procedure in EF to instead of TVF.

    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, August 21, 2013 5:17 AM