Processing Non-Entity Fields in IQueryable

Topics: ASP.NET Web API
Aug 10, 2012 at 9:06 PM

Hi!

I have to implement a following scenario.

public partial class EntityFrameworkData {
	public int SomeDataFromDB {get; set;}
}
public partial class EntityFrameworkData {
	public int SomeDataToBeCalculated {get; set;}
}

public class SomeController {
	[Queryable]
	public HttpResponseMessage Get() {
		IQueryable<EntityFrameworkData> query = this.EntityFrameworkContext. // ... and some LINQ stuff here
		// Query is not materialized. And it is great because if request is like '...&$top=10...' then SQL request will select only top 10 items.
		
		// I need to calculate and fill SomeDataToBeCalculated based on some conditions
		foreach (var item in query) item.SomeDataToBeCalculated = 42; // Bad: query is materialized and fetch all data
		item.ForEach(...); // Wrong: not implemented in query
		query = item.Select(x => { x.SomeDataToBeCalculated = 42; return x; }); // Wrong: C# compiler can't translate lambda statement body to expression tree
		query = item.Select(x => SomeStaticMethodToFillData(x)); // Wrong: LINQ-to-Entity doesn't know how to translate SomeStaticMethodToFillData
		
		Expression<Func<EntityFrameworkData, EntityFrameworkData>> calculateIsNewExpression = // ... and some neat stuff to assign value to property that contains Expression.Block
		query = item.Select(calculateIsNewExpression); // Wrong: LINQ-to-Entity doesn't know how to translate Expression.Block
		
		return this.Request.CreateResponse(HttpStatusCode.OK, query);
	}
}

I suppose that one way is to process data after materialization. How to get these data? What another ways to do it?

 

Cheers!

Alex

Aug 17, 2012 at 11:55 AM

Hi!

 

Is this question so obvious to answer? I don't understand some simple thing? Or should I reformulate the question?

 

Cheers!

Alex

Aug 17, 2012 at 4:43 PM
Edited Aug 22, 2012 at 9:16 PM
I beleive you are still using RC bits. We have moved QueryableAttribute out of the RTM bits and put them in a separate nuget package. The nuget package has the QueryableAttribute and other extra goodness like ODataFormatter that lets you build full OData services. We have released a public preview (alpha) of this nuget package couple of days back. You can find it at http://nuget.org/packages/Microsoft.AspNet.WebApi.OData/

Getting back to the answer to your question, we have added a customization point for QueryableAttribute in these bits. If the standard QueryableAttribute functionality suits your need you just need to use the QueryableAttribute. If not, like in your case, you can use ODataQueryOptions. You can find a sample at http://aspnet.codeplex.com/SourceControl/changeset/view/eb6e13361e99#Samples%2fNet4%2fCS%2fWebApi%2fODataQueryableSample%2fControllers%2fOrderController.cs

ODataQueryOptions lets you apply the query that the user sends manually which is what you need. Some sample code to help you with your scenario,

public HttpResponseMessage Get(ODataQueryOptions queryOptions)
        {
            IQueryable<EntityFrameworkData> query = this.EntityFrameworkContext; // ... and some LINQ stuff here
 
            // Apply the user query. The result would be what the user wants.
            // So, if he sends $top=10 it contains only 10 results.
            query = queryOptions.ApplyTo(query);
 
            // get the results in memory and apply the calculation yourselves.
            query = query.ToArray().AsQueryable().Select(x => SomeStaticMethodToFillData(x));
 
            return this.Request.CreateResponse(HttpStatusCode.OK, query);
        }

 

 

 

 

  

alexander_myltsev wrote:

Hi!

 

Is this question so obvious to answer? I don't understand some simple thing? Or should I reformulate the question?

 

Cheers!

Alex

Sep 21, 2012 at 6:48 AM

This seems to be working. Kudos! 

There is another problem with ODataQueryOptions. I have User entity type that has corresponding EntitySet in the EdmModel. If I have a controller action like this:

        public IQueryable<User> Get(ODataQueryOptions oDataQueryOptions)
        {
            return new List<User>
                {
                    new User {Nickname = "a"},
                    new User {Nickname = "b"},
                    new User {Nickname = "c"},
                }.AsQueryable();
        }

 

Then everything works just fine. But for a controller action like:

        public HttpResponseMessage Get(ODataQueryOptions oDataQueryOptions)
        {
            return Request.CreateResponse(HttpStatusCode.OK, new List<User>
                {
                    new User {Nickname = "a"},
                    new User {Nickname = "b"},
                    new User {Nickname = "c"},
                }.AsQueryable());
        }
I have an error message: "The server failed to retrieve a inner generic type from type 'System.Net.Http.HttpResponseMessage' against action 'Get' on controller 'Test'". What am I doing wrong this time?

Regards,

  Alex

Sep 21, 2012 at 6:52 AM

By the way, I have another error. Queryable attribute of previous version just ignored OData $count request. I inherited a class from Queryable and implemented $count by hand. Now Queryable throws an exception if there is $count in Request. 

Is there a big reason why $count is not implemented in WebAPI? Or it is just planned for a future release?

Regards,

  Alex

Sep 21, 2012 at 6:52 AM

By the way, I have another error. Queryable attribute of previous version just ignored OData $count request. I inherited a class from Queryable and implemented $count by hand. Now Queryable throws an exception if there is $count in Request. 

Is there a big reason why $count is not implemented in WebAPI? Or it is just planned for a future release?

Regards,

  Alex

Sep 21, 2012 at 1:49 PM

Sorry for double post.

If have IQueryable<Card> instance 'query' that has 'OrderByDescending(с => c.SendDate)' in construction chain. If I call query.ToList() then the order is correct. But if I call '(oDataQueryOptions.ApplyTo(query) as IQueryable<Card>).ToList()' then ordering is wrong. Is this a bug?

Regards,

  Alex

Sep 21, 2012 at 4:53 PM
alexander_myltsev wrote:

This seems to be working. Kudos! 

There is another problem with ODataQueryOptions. I have User entity type that has corresponding EntitySet in the EdmModel. If I have a controller action like this:

        public IQueryable<User> Get(ODataQueryOptions oDataQueryOptions)
        {
            return new List<User>
                {
                    new User {Nickname = "a"},
                    new User {Nickname = "b"},
                    new User {Nickname = "c"},
                }.AsQueryable();
        }

 

Then everything works just fine. But for a controller action like:

 

        public HttpResponseMessage Get(ODataQueryOptions oDataQueryOptions)
        {
            return Request.CreateResponse(HttpStatusCode.OK, new List<User>
                {
                    new User {Nickname = "a"},
                    new User {Nickname = "b"},
                    new User {Nickname = "c"},
                }.AsQueryable());
        }
I have an error message: "The server failed to retrieve a inner generic type from type 'System.Net.Http.HttpResponseMessage' against action 'Get' on controller 'Test'". What am I doing wrong this time?

 

Regards,

  Alex

Thanks. The issue you mentioned in the post is a known one. http://aspnetwebstack.codeplex.com/workitem/368 . ODataQueryOptions is bound before calling the actions so it has no way right now to figure out the 'T' from IQueryable<T> which it requires. We are thinking of adding a ODataQueryOptions<T>. As you have been hitting this issue I will prioritize fixing this bug to others.

Sep 21, 2012 at 5:02 PM
alexander_myltsev wrote:

By the way, I have another error. Queryable attribute of previous version just ignored OData $count request. I inherited a class from Queryable and implemented $count by hand. Now Queryable throws an exception if there is $count in Request. 

Is there a big reason why $count is not implemented in WebAPI? Or it is just planned for a future release?

Regards,

  Alex

OData spec says that any $parameter that is not understood by the service should send a bad request. So, to be complaint with the spec we throw if we see any $parameter other than $filter, $orderby, $top and $skip. 

If you want to implement support for a new $parameter you have to override "ValidateQuery" method to not throw for this parameter. Look at http://aspnetwebstack.codeplex.com/SourceControl/changeset/view/2c75266574f2#src%2fSystem.Web.Http.OData%2fQueryableAttribute.cs to see the default implementation. 

And there is a bug tracking adding $inlinecount support to webapi. http://aspnetwebstack.codeplex.com/workitem/330

Sep 21, 2012 at 5:12 PM
alexander_myltsev wrote:

Sorry for double post.

If have IQueryable<Card> instance 'query' that has 'OrderByDescending(с => c.SendDate)' in construction chain. If I call query.ToList() then the order is correct. But if I call '(oDataQueryOptions.ApplyTo(query) as IQueryable<Card>).ToList()' then ordering is wrong. Is this a bug?

Regards,

  Alex

Could you share more details like the model for Card and the query that is failing. We insert order by clauses on the primary keys by default to ensure a stable order if you have a $skip or $top in your user query. You might be having issues there I think. You can disable that by setting 'EnsureStableOrdering' on the QueryableAttribute or on the ODataQuerySettings that you pass to Apply method in ODataQueryOptions.

 

P.S: ODataQuerySettings is added to the source recently and can be found only in our nightly builds.

Sep 21, 2012 at 7:50 PM

Kudos for rapid reply!

Model for Card:

 

class Card
{
        public long Id { get; set; }
        public System.DateTime SendDate { get; set; }
// Other fields
}

 

Result LINQ query of an action is as follows:

_dataContext
  .Cards
  .OrderByDescending(kc => kc.SendDate)
  .Where(x => x.SendDate > someDate);

And it is ordered wrong in the result response after all applications of OData routines.

Regards,

  Alex

Sep 21, 2012 at 10:03 PM
Edited Sep 21, 2012 at 10:04 PM

Hey alex,

I just tried this out and looks like this is a bug. As explained earlier, we insert orderby clauses on the primary key to ensure a stable sort. So, the resulting query might look something like,

_dataContext
  .Cards
  .OrderByDescending(kc => kc.SendDate)
  .Where(x => x.SendDate > someDate)
  .Userqueries
  .OrderBy(kc => kc.Id)

which is causing the issues as the second orderby takes precedence. Could you please log a bug for this at http://aspnetwebstack.codeplex.com/WorkItem/Create
Meanwhile, you can use the 'EnsureStableOrdering' flag to workaround it.

Sep 22, 2012 at 10:21 AM

Done: http://aspnetwebstack.codeplex.com/workitem/444 .

I am going to try 'EnsureStableOrdering'. Kudos for recommendations!

Sep 22, 2012 at 3:12 PM

ODataQueryOptions and ODataQuerySettings works as expected. Kudos to you!

Things turn bad when I try to apply $count to a queryable. Variants I thought about and tried:

 

public IQueryable<KudosCard> GetAll(ODataQueryOptions oDataQueryOptions, <STUFF>) {

  // oDataQueryOptions application to 'IQueryable<Card> cards'
  // oDataQueryOptions doesn't have any field related to $count. ODataQueryOptions can't be inherited to be processed by Web API either.
  // Another attempt is to fetch $count data from request. But we can't return cards.LongCount() because of function signature.
  // If I try to change result type to something more generic like HttpResponseMessage then I get error message that ODataQueryOptions binder is upset about result type.

 

I even can't implement a satellite method:

 

public long GetAllCount(ODataQueryOptions oDataQueryOptions, <STUFF>) {
  return GetAll(oDataQueryOptions, <STUFF>);
}
 

 

because ODataQueryOptions binder is upset.

So, for that moment I implemented:

 

public long GetAllCount(<STUFF>) {
  return GetAll(null, <STUFF>);
}

 

that is correct while my-API consumer provides only $count and no other OData parameters.

I can try to implement $count by myself by code contributing to OData project. I suppose that first step is to fix http://aspnetwebstack.codeplex.com/workitem/368 . Then OData have to be extended to support $count. What do you think? Any suggestions?

Cheers!

Sep 28, 2012 at 3:12 PM

Hi!

Any suggestions?

Regards

Sep 28, 2012 at 6:12 PM
  • ODataQueryOptions has a RawValueOptions which should contain all the raw values for '$' parameters. You should find $count there.
  • There is a workitem tracking adding support for odata $inlinecount. Is that sufficient for you ? Or do you want to expose an API which just returns only the count and not the results.

One solution for such API is to inherit QueryableAttribute, call the base class implementation and then replace the HttpResponseMessage.

 

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            HttpServer server = new HttpServer();
            server.Configuration.Routes.MapHttpRoute("default", "{controller}");

            HttpClient client = new HttpClient(server);
            HttpResponseMessage response = client.GetAsync("http://localhost/Test").Result;
            Console.WriteLine(response.Content.ReadAsStringAsync().Result);
        }
    }

    public class TestController : ApiController
    {
        [CountingQueryable]
        public IQueryable<int> GET()
        {
            return Enumerable.Range(1, 100).AsQueryable();
        }
    }

    public class CountingQueryableAttribute : QueryableAttribute
    {
        private static MethodInfo _count = typeof(Queryable).GetMethods().Where(m => m.Name == "Count" && m.GetParameters().Count() == 1).Single();

        public override void OnActionExecuted(HttpActionExecutedContext actionExecutedContext)
        {
            base.OnActionExecuted(actionExecutedContext);

            HttpResponseMessage response = actionExecutedContext.Response;
            IQueryable queryable;
            if (response.TryGetContentValue(out queryable))
            {
                int count = Count(queryable);
                actionExecutedContext.Response = actionExecutedContext.Request.CreateResponse(actionExecutedContext.Response.StatusCode, count);
            }
        }

        private int Count(IQueryable queryable)
        {
            return (int)_count.MakeGenericMethod(queryable.ElementType).Invoke(null, new[] { queryable });
        }
    }
}

Oct 16, 2012 at 1:47 PM

raghuramn,

Thanks for you advice about CoutingQueryableAttribute. Actually, I had similar solution. It does not cover the scenario I posted in the beginning of that tread. I.e. CoutingQueryableAttribute will materialize IQueryable only after method execution. There is a scenario when I need postprocess data of materialized query.

 

RawValueOptions doesn't contain Count:

 

-		queryOptions.RawValues	{System.Web.Http.OData.Query.ODataRawQueryOptions}	System.Web.Http.OData.Query.ODataRawQueryOptions
		Expand	null	string
		Filter	null	string
		InlineCount	null	string
		OrderBy	null	string
		Select	null	string
		Skip	null	string
		SkipToken	null	string
		Top	null	string

Regards

Oct 16, 2012 at 5:08 PM

$count is not a query string option as is the case with the other query options. So, we did not include it in the ODataQueryOptions. Please open an issue on codeplex if you expect that $count should be included in QueryOptions.