Dynamically Modifying a LINQ to SQL Select Statement's Columns

Question

Hello,

I'm trying to build a REST-ful API for my app. Currently I have something like this:
www.example.com/submissions/?format=json

This will return latest ten submissions in JSON. Each object has its details, such as submission name, date created, user, body, etc.

I'd like to do something such as:
www.example.com/submissions/?format=json&filter=name,user

The filter should make the request to return the same result but to only include the details mentioned, i.e. each object will only have a name and user.

This is fairly straightforward in terms of the JSON output. I can load all the columns from the database and create and serialize an object that will only include the columns in the filter. However, I do not want to load all the columns in the database - I want to bother my database with only the columns that I will include in the response.

I want to do something like this:

var result = from record in Submissions
       select
       {
       Name,
       Date,
       User,
       Body
       };

Now I have the result object, which is IQueryable, so no call to database made yet.

Then, I should examine the filter querystring and exclude the columns that are not mentioned.

Finally, I can execute the select statement with something like
JavaScript.Serialize(result.ToList());

Is this possible with LINQ to SQL?

Answer 1

Yes. You are going to want to research Modifying Expression Trees. Specifically the MemberInit Expression.

Answer 2

An alternative to building your Select expression tree by hand is Dynamic LINQ, which provides a Select method that takes a string:

var filter = "name,user";
var result = Submissions.Select("new(" + filter + ")");

The string is then translated into an expression tree and passed on to your query provider.