Easy LINQ to SQL for Oracle databases
I really doubt I'm the first one to do or think about this, but after Googling for ways to use LINQ with an Oracle database I was surprised that this hasn't even been mentioned in passing. Sometimes I need to find the simplest solution no matter what the performance hit is going to be—specially since I work in an IT department at a non-technical corp—to at least get the first version out the door. People may complain about stuff being slow, but hey, at least we made the deadline. We'll worry about optimizing the solution later when or if necessary.
If you're in a hurry, let me get to the gist of it. All you need to do is create a linked server to your Oracle database in SQL Server, create a few views that pretty much just SELECT * FROM whatever, and then drag&drop those views in a LINQ to SQL designer file. Ugly, but you can at least say it works.
Linked servers are obviously a big no-no if you're worried about performance, specially if your linked server is on another network. I assume if both your servers are on the same subnet it might not be that bad, but I may be wrong. I'll let you do the research if you're interested. All I know is that in my case it's pretty slow (even on the same network), but we can live with it. It may be that our Oracle database just sucks—it was designed by a third-party vendor for use in some proprietary software we license, but in Bizarro world the database may have some logical structure.
Anyway, this post isn't about linked servers or database performance. It's about getting things done, and that means LINQ to SQL.
Why not Entity Framework?
It's common knowledge that LINQ to SQL is pretty much SQL Server only, but that you can use Entity Framework as your ORM instead. You do not get LINQ to Entities for Oracle out-of-the-box, though. You need to buy a copy of Devart's dotConnect Oracle data provider (as the sample MSDN one is obviously not production-proof).
However, do you really want to use Entity Framework to begin with? If you need a feature that LINQ to SQL doesn't support, it's an obvious choice. (This MSDN article explains it all: Introducing LINQ to Relational Data.) In my case my project needs to be done 3 months ago, so I went with Occam's razor in addition to the fact that I had already worked with LINQ to SQL in the past.
Enter LINQ to SQL
LINQ to SQL is a pretty good choice when deciding between it and EF, as it is simple and straight-to-the-point. I was actually set out to use EF in my project, without ever having read any documentation or full overview of it, until I hit a few walls thanks to my ignorance.
After realizing I wasn't going to be able to map straight to the Oracle database without buying a dotConnect license and actually learning EF, I somehow remembered that the code I was refactoring was actually making stored procedure calls that already queried the Oracle database through a linked server. So I said to myself, "Let me just convert that to a view (without the extra parameterized conditions) and include that in my schema."
For some reason or another, my SQL view was simply not showing up. "The hell with Entity Framework!" I exclaimed (in even more vulgar terms). I didn't want to have to spend a few hours Googling and figuring this one out, so I decided to just use LINQ to SQL. My view was happily available in the Server Explorer for some tender drag&drop.
Good riddance and I'm glad I went this route. Had I known that LINQ to SQL was not actually dead or obsolete, that Entity Framework isn't really "the hotsauce" (to put it mildly), and that Occam's razor is a concept Jodie Foster may or may not have butchered, I would have gone with LINQ to SQL from the start.
Future considerations
For the next iteration of this I might end up having to go with Entity Framework if the linked server seems to be a bottleneck. I would have to weigh the performance gains (if any) against the effort of switching to EF before making a decision.
Leave a comment and let me know what you think.
