How to filter output columns with WCF OData Services?

Question

For a given entity set, say, WHO_TYPES, I want to limit the columns to the ID and the WHO_TYPE_NAME.

The entity set itself is exposed, via:

config.SetEntitySetAccessRule("WHO_TYPES", EntitySetRights.AllRead);

... so I can't see how to do this when setting the entity/table access rules. Is it possible to write a QueryInterceptor to accomplish this? If so, how?

Another attempt I have made is to write a custom method that references a custom object:

[DataServiceKey("MY_WHO_TYPES")]
public partial class MY_WHO_TYPES
{
    public MY_WHO_TYPES() { }

    public int MY_WID { get; set; }
    public string MY_WNAME { get; set; }
}

My custom method:

    [WebGet]
    public IQueryable<MY_WHO_TYPES> GetWhoTypesCustom()
    {
        var whoCustom = from w in this.CurrentDataSource.WHO_TYPES
                        select new MY_WHO_TYPES() { MY_WID = w.ID, MY_WNAME = w.WHO_TYPE_NAME };

        return whoCustom.AsQueryable<MY_WHO_TYPES>();
    }

With this attempt, I get the following error message:

Unable to load metadata for return type 'System.Linq.IQueryable`1[DAL.Models.MY_WHO_TYPES]' of method 'System.Linq.IQueryable`1[DAL.Models.MY_WHO_TYPES] GetWhoTypesCustom()'.

I'm feeling some sort of repository pattern coming up in a response, but I'm hoping this could be simpler.

The suggestion of using a Tuple sounds good, but I'm not quite sure how to implement it or what the return type would be:

select new { Tuple<int, string> (w.ID, w.WHO_TYPE_NAME)};  // error: Invalid anonymous type member declarator.  Anonymous type members must be declared with a memeber assignment...

Next I tried simply returning an anonymous type:

    [WebGet]
    public IQueryable GetWhoTypesCustom()
    {
        var whoCustom = from w in this.CurrentDataSource.WHO_TYPES
                        select new { w.ID, w.WHO_TYPE_NAME };
        return whoCustom;
    }

The problem with this is that I get an error indicating the IQueryable type is not defined.

Answer 1

The simplest way to do this is to use stored procedures instead of the actual entities themselves.

This involves creating a complex type from the sproc. Define the columns that you want to be returned in the sproc and create a complex object when creating your entities.

From the *.edmx view:

Right-click / Add / Function Import
Select the sproc
Give it a Function Import Name
Click Get Column Information
Click Create New Complex Type - give it a name (by default looks like [Function Import Name]_Result

That should work, but it seems there is some issue when consuming the service, where navigating to the URL in a browser shows data, but when consuming it in an application, an error is seen in the service trace log: Configuration evaluation context not found. This is addressed in another question.

However, if it's a 1:1 field mapping in the sproc to one of the entities, in the steps above, instead of selecting a new complex type, select Entities and the matching entity and this will work fine in both cases (URL navigation an consumption).

class Program
{
    static void Main(string[] args)
    {
        DataServiceContext context = new DataServiceContext(new Uri("http://localhost:50100/DataService1.svc/"));
        IEnumerable<WHO_TYPES> result = context.Execute<WHO_TYPES>(new Uri("http://localhost:50100/DataService1.svc/GetDataWhoTypesSproc"));
        foreach (WHO_TYPES w in result)
        {
            Console.WriteLine(w.ID + "\t" + w.WHO_TYPE_NAME + "\t" + w.CREATED_DATE);
        }

        Console.Read();
    }
}

The issue found (unable to return complex types) seems to be what the root cause of this issue, and is more accurately addressed here.