locked
How to change the order of execution in oData ? RRS feed

  • Question

  • Hi, I have gone through the sample codes provided in OData.org and many tutorial articles on the net.  The order of execution seem to be like this:

    1) Define MetaData

    2) Populate Data in CreateDataSource (eg, 1million rows)

    3) Use DataServiceQuery provider to convert the URI filters into LINQ expression, which queries the DataSource in Memory.

     

    I would like to make it more efficient by doing the following:

    1) Define MetaData

    2) Use DataSeriveQuery provider to return me the URL filters into LINQ expression.

    3) The LINQ expression will help me to filter the data retrieve from the database to be populated to the datasource.   (eg, 1000 rows)

     

    As you can see the earlier approach pull 1 million rows from the database, and the later approach only pull 1000 rows.

     

    How would I implement this?  Can it be done?  

    By the way, I am not binding to Entity Framework to get the data.  I am retrieving data through API or direct SQL call to the SQL server.

     

    Regards,

    Matt

    Tuesday, June 29, 2010 2:05 PM

Answers

  • Hi,

    This is totally doable. The sample uses CreateDataSource to prepare a sample piece of data, but that's all it is, it's a sample. It also uses LINQ to Objects (the one you get by calling List<T>.AsQueryable()) which needs all of the data in memory. None of this is required by WCF Data Services. All we need is just a working IQueryable.

    Actually the call order is more like this:

    1) Define metadata. Note that this can be done dynamically and on demand, you don't need to construct the entire metadata for every request. This is done by implementing the IDataServiceMetadataProvider.

    1a) CreateDataSource - this can be a pretty much "do nothing" operation. The WCF Data Services doesn't use this at all, it's there for your provider to be able to track a lifetime of a "connection" to the data source. For DB based providers, this would typically get a connection to the DB (either create a new one or get one from cache).

    2) Return IQueryable implementation from your IDataServiceQueryProvider.GetQueryRootForResourceSet.

    The IQueryable is basically just a promise of data. WCF Data Services will use it (and the accompanying IQueryProvider) to construct the query (the LINQ expression tree). Once the entire query is constructed it will try to enumerate the queryable, which is basically equivalent to executing the query. Only then you need to actually go and retrieve the data.

    For example the EF's implementation of IQueryable will pretty much cache the LINQ expression tree during the construction phase. Only when you try to enumerate the results it will go, translate the LINQ Expression tree into a SQL SELECT command, run it and return the results to you. Your provider can do the same.

    It's just that implementing your own IQueryable is rather hard, that's why the samples don't do it (mostly, note that the untyped samples actually do have their own IQueryable which does some small fixups on the expression tree before running it using LINQ to Objects). If you want to go that route I suggest you take a look at this blog series about writing IQueryable (pretty much something like LINQ to SQL) and then you can also take a look at my blog series about the expression trees WCF Data Services generate for certain types of queries (so that you can only implement the parts which are needed).

    Thanks,


    Vitek Karas [MSFT]
    • Marked as answer by Matthew Kwan Wednesday, June 30, 2010 10:37 PM
    Tuesday, June 29, 2010 3:12 PM
    Moderator
  • Hi Matt,

    Just a partial reply :-)

    1. Yes, you do need to create a custom LINQ provider (pretty much)

    2. The question of "where do I put my LINQ query" doesn't actually make sense that much. It's kind of the job of the custom LINQ provider, to turn the query created by WCF Data Services (from the URL) to whatever query language your underlying data source supports. Now you can layer LINQ providers, so you could write a LINQ provider which just modifies the query (the expression tree) in certain ways and then passes it along to another LINQ provider (like LINQ to SQL, LINQ to Entities, LINQ to Objects, ...). But this heavily depends on what you actually need to do with your queries.

    3. The DSP classes in the samples are just that, samples. Some of them might work for you, some of them might not. In your case it seems that the DSPMetadata might work well, but the classes which implement the query support itself will probably mostly not work for you. Use them just as a sample of how it could be done, doesn't mean you have to do it like that, or that such approach will work always.

    I am thinking about a sample... if you have something in particular on your mind, please share it, I would rather not come up with something totally out of reality. The sample DSP in the OData SDK was the first shot I gave it, so they are very generic.

    Thanks,


    Vitek Karas [MSFT]
    • Marked as answer by Matthew Kwan Wednesday, June 30, 2010 10:37 PM
    Wednesday, June 30, 2010 2:50 PM
    Moderator

All replies

  • Hi,

    This is totally doable. The sample uses CreateDataSource to prepare a sample piece of data, but that's all it is, it's a sample. It also uses LINQ to Objects (the one you get by calling List<T>.AsQueryable()) which needs all of the data in memory. None of this is required by WCF Data Services. All we need is just a working IQueryable.

    Actually the call order is more like this:

    1) Define metadata. Note that this can be done dynamically and on demand, you don't need to construct the entire metadata for every request. This is done by implementing the IDataServiceMetadataProvider.

    1a) CreateDataSource - this can be a pretty much "do nothing" operation. The WCF Data Services doesn't use this at all, it's there for your provider to be able to track a lifetime of a "connection" to the data source. For DB based providers, this would typically get a connection to the DB (either create a new one or get one from cache).

    2) Return IQueryable implementation from your IDataServiceQueryProvider.GetQueryRootForResourceSet.

    The IQueryable is basically just a promise of data. WCF Data Services will use it (and the accompanying IQueryProvider) to construct the query (the LINQ expression tree). Once the entire query is constructed it will try to enumerate the queryable, which is basically equivalent to executing the query. Only then you need to actually go and retrieve the data.

    For example the EF's implementation of IQueryable will pretty much cache the LINQ expression tree during the construction phase. Only when you try to enumerate the results it will go, translate the LINQ Expression tree into a SQL SELECT command, run it and return the results to you. Your provider can do the same.

    It's just that implementing your own IQueryable is rather hard, that's why the samples don't do it (mostly, note that the untyped samples actually do have their own IQueryable which does some small fixups on the expression tree before running it using LINQ to Objects). If you want to go that route I suggest you take a look at this blog series about writing IQueryable (pretty much something like LINQ to SQL) and then you can also take a look at my blog series about the expression trees WCF Data Services generate for certain types of queries (so that you can only implement the parts which are needed).

    Thanks,


    Vitek Karas [MSFT]
    • Marked as answer by Matthew Kwan Wednesday, June 30, 2010 10:37 PM
    Tuesday, June 29, 2010 3:12 PM
    Moderator
  • Hi Vitek, thanks for the suggestion.

    I am a newbie in WCF Data Service area, please be patient with me.


    * So I need to create a custom Linq provider, am I correct?

    * After created the custom Linq provider, where do I place LINQ query (eg, var _queryable = from p in Reports select p;)  to retrieve data since I should not pull data in    CreateDataSource method?  put it in GetQueryRootForResourceSet?

    * Do I need to make changes to the DSP classes provided by OData.org in order to get this to work?

     

    It would be really good if you could create a tutorial and sample codes on this topic :)

    Thanks for your help.

    Matt

    Wednesday, June 30, 2010 12:24 AM
  • Hi Matt,

    Just a partial reply :-)

    1. Yes, you do need to create a custom LINQ provider (pretty much)

    2. The question of "where do I put my LINQ query" doesn't actually make sense that much. It's kind of the job of the custom LINQ provider, to turn the query created by WCF Data Services (from the URL) to whatever query language your underlying data source supports. Now you can layer LINQ providers, so you could write a LINQ provider which just modifies the query (the expression tree) in certain ways and then passes it along to another LINQ provider (like LINQ to SQL, LINQ to Entities, LINQ to Objects, ...). But this heavily depends on what you actually need to do with your queries.

    3. The DSP classes in the samples are just that, samples. Some of them might work for you, some of them might not. In your case it seems that the DSPMetadata might work well, but the classes which implement the query support itself will probably mostly not work for you. Use them just as a sample of how it could be done, doesn't mean you have to do it like that, or that such approach will work always.

    I am thinking about a sample... if you have something in particular on your mind, please share it, I would rather not come up with something totally out of reality. The sample DSP in the OData SDK was the first shot I gave it, so they are very generic.

    Thanks,


    Vitek Karas [MSFT]
    • Marked as answer by Matthew Kwan Wednesday, June 30, 2010 10:37 PM
    Wednesday, June 30, 2010 2:50 PM
    Moderator
  • Thanks Vitek, you have been extremely helpful.


    You have given us good directions to pursue this challenge.


    Currently what we would like to do is:

    * We already have our API returnings data from a database eg, Categories > Products 

    * Now we would like to expose these data to our clients thru ODATA

    * The clients will consume the ODATA in Excel PowerPivot 

    * The clients will be using ODATA URI filtering method to query our API to return data

    * Therefore our custom Linq providers need to capture the DataService LINQ Query expression, walk thru the expression tree, get the query and filters, pass them to our API to return data


    We are really looking forward to your sample article.   Please reply in this thread once you got the sample or article ready :)

    Thanks again.

    Wednesday, June 30, 2010 10:53 PM