2

I'm researching how to set up an OData interface to our database. I would like to be able to pull/query data from our DB into Excel, as a start. Eventually I would like to have Excel run queries and pull data over HTTP from a remote client, including authentication, etc.

I've set up a working (rickety) prototype so far, using the ADO.NET Entity Data Model wizard in Visual Studio, and VSTO to create a test Excel worksheet with a button to pull from that ADO.NET interface. This works OK so far, and I can query the DB using Linq through the entities/objects that are created by the ADO.NET EDM wizard.

However, I have started to run into some problems with this approach. I've been finding the Entity Framework difficult to work with (and in fact, also difficult to research solutions to, as there's a lot of chaff out there regarding it and older versions of it). An example of this is my being unable to figure out how to set the SQL command timeout (as opposed to the HTTP request timeout) on the DataServiceContext object that the wizard generates for my schema, but that's not the point of my question.

The real question I have is, if I want to use OData as my interface standard, am I stuck with the Entity Framework? Are there any other solutions out there (preferably open source) which can set up, serve and consume an OData interface, and are easier to work with and less bloated than the Entity Framework? I have seen mention of NHibernate as an alternative, but most of the comparison threads I've seen are a few years old. Are there any other alternatives out there now?

Thanks very much!

Egahn
  • 369
  • 1
  • 4
  • 13
  • 1
    FWIW Your opinions of Entity Framework are just **inaccurate** in regards to EF 4.1. We have an application in production today with it and the experience EF provides is one of the best ORM experiences in existence now. We also had success in using fluent migrator to maintain database evolution. – Chris Marisic Jun 07 '11 at 18:29
  • You're upset with the EF because you read some bad things and you can't figure out how to change the DB connection string (hint: it's embedded in the EF connection string)? So you decide you want to dump it but can't find any alternatives? [Have you even looked?](http://www.odata.org/producers) – Craig Stuntz Jun 07 '11 at 18:34
  • Alrighty then, looks like I stepped on some toes. My question was intended less to bash EF than to ask for alternatives. Perhaps my language was too harsh. In any case, anybody have any personal experience/war stories to share regarding an EF-alternative? – Egahn Jun 07 '11 at 19:23
  • Your question just comes off as, "I'd like to know what the options are, but I haven't actually looked at *the big long list of options* on the OData.org site." – Craig Stuntz Jun 07 '11 at 19:39
  • Fair enough. I've seen that list before, but none of them jump out at me as being appropriate for my task at hand. I see options in there for working with geospatial data, CRM solutions, Azure DBs, Silverlight apps, etc. I'm looking for something a little more basic (and free), I think. Have you used any of the options in that list (or elsewhere?) Do you prefer any of them over the others? – Egahn Jun 07 '11 at 19:51
  • see it: http://stackoverflow.com/questions/10781309/asp-net-mvc-4-webapi-manually-handle-odata-queries –  Oct 02 '12 at 14:48

3 Answers3

2

WCF Data Services provide extensible model for defining custom providers. So answer to your main question is: No you are not stuck with Entity framework. Unless you have real example of some problems which you would like to solve with EF the rest of your question is mostly subjective and argumentative.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • 1
    ...and of course you're in no way limited to using WCF Data Services to produce OData, also. – Craig Stuntz Jun 07 '11 at 19:52
  • Wound up using a Linq2Sql data provider, rather than the Entity Framework. Works better for my simple scenario. Thanks. – Egahn Jun 13 '11 at 17:49
2

We have investigates a complete sprint of development (15 days) 2 person to try ODATA over our service layers using our DTO... Proto was in a real world including back end integration with external service provider, real use case with multiple front end connect to the same back office, real data with multiple database per context, millions of records. We found out that the only solution that works well is if we use entity framework... not very easy to defined custom provider, works well if everything is in memory(not very scalable), crash occurs when DTO contains non-primitive properties. Need to create a new class to implement IQueryable and IQueryProvider interfaces, but still need to do mapping in big switch case to call the proper service based on the URL OData query... and had a hard time to plug the integration with external provider data querying...

ODATA is very nice if you use entity framework...

Ben
  • 21
  • 1
1

We also can't use the entity framework. As we have a high performance solution that uses a lot of intelligent caching, the Entity framework just skips that, and connects directly to the database server, launching all kind of way to complicated queries to just do a simple select. It's joining on other tables for which I don't need the data in the request I investigated.

For me Entity framework is nothing but a performance killer. So +1 for your question!

JDC
  • 1,569
  • 16
  • 33