Creating a set from Table Valued Function in Entity Framework

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

Answer 1

I think you can get the functionality you want by using a customized data service provider to define the shape of your WCF service. That can get pretty complicated, but since you already have a bunch of IQueryables, you'll probably be able to use the Reflection Provider, about which the docs say:

The reflection provider exposes data in classes that return types that implement the IQueryable interface. WCF Data Services uses reflection to infer a data model for these classes and can translate address-based queries against resources into language integrated query (LINQ)-based queries against the exposed IQueryable types.

That documentation links to a "How to" for using the Reflection Provider. Basically you just create a pseudo-context class with IQueryable properties, add a few attributes to your data objects, and point your DataService at your pseudo-context type. (If you're using Database/Model-first EF, you'll probably need to create partials of your entities in order to add the attributes, or update the T4 templates.)