Brief Overview
Okay, so here we go. This is going to be a bit of a rambling adventure, because that’s the only way it can follow what I actually managed to accomplish.
I’ve been obsessed with learning LINQ to SQL since I first heard of it, but never really had the time. Besides time constraints, I was also put off by the fact that there seemed to be nothing coming from the Oracle camp regarding the exciting new technology I saw unfolding. After a while I began hearing whispers of the Entity Framework, a complete object-relational mapping solution coming from the Microsoft camp, and was immediately interested. I had never really taken a look at the other available ORM solutions for .NET, NHibernate and the like, simply because I thought there would be a slim chance I would be able to use both Oracle and LINQ with them without too much trouble. As it stands, I had exactly that trouble with Entity Framework, but it feels justified in a weird way. I’m sure after this I’ll try taking a look around for other available solutions, but so far this looks extremely promising, if a little unpolished.
This all starts off with EFOracleProvider, an Entity Framework-friendly provider used to generate the necessary classes and mapping files for database access. Compiling EFOracleProvider gave me a DLL and some configuration settings I could toss at the already existing data model generator, EdmGen.exe, to generate the classes that map to the database tables and fields I specified.
I’m going to show you the files I worked with in all of this, but I’m not going to obfuscate them, or simplify them. I’m going to give you an idea of the exact situation I was in, in order to show you the problems I encountered, and how easy it is to encounter them. I’d also like to remind you that use of EFOracleProvider is not recommended in production environments, as it is extremely rough quality code. This was more of an adventure than a focused goal, but it raises a lot of questions.
Observing the Database
My first step, after compiling EFOracleProvider, was to create a storage model for the part of the database I wanted to present to .NET. I decided to go straight to our live database and take my queues from it. I took a small cross-section of our available schema — just a single table, with two columns, both VARCHAR2s.
+--------------------------+
| tblAsset |
+--------------------------+
| Asset_Number |
| Asset_Type |
+--------------------------+
Yes, Asset_Number is a string. It refers to alphanumeric serial numbers. But it’s also our primary key, so it’ll make do. In any event.
The storage model file is XML, and fairly easy to create, but has a lot of hidden gotchas that we’ll cover.
<?xml version="1.0" encoding="utf-8"?>
<Schema Namespace="TestAppEFModel.Administration" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl" Provider="EFOracleProvider" ProviderManifestToken="10g">
<EntityContainer Name="dbo">
<EntitySet Name="Assets" EntityType="TestAppEFModel.Administration.Assets" Table="tblAsset" />
</EntityContainer>
<EntityType Name="Assets">
<Key>
<PropertyRef Name="Asset_Number" />
</Key>
<Property Name="Asset_Number" Type="varchar2" Nullable="false" StoreGeneratedPattern="Identity" />
<Property Name="Asset_Type" Type="varchar2" MaxLength="50" />
</EntityType>
</Schema>
Fairly straightforward. We’re reminded in the EFOracleProvider documentation to set the ProvierManifestToken to the relevant version number of our database. You’ll note that your namespace seems to require a secondary class. You cannot place your entities in TestAppEFModel, you need to create something like TestAppEFModel.Administration. Also note that I’ve manually specified the table name in the EntitySet tag, since it does not map cleanly to what .NET expects.
Fire up EdmGen.exe:
C:/OracleEFTests>edmgen.exe /mode:FromSSDLGeneration /
/c:"Data Source=TNSNAME;User ID=user_id;Password=password..." /
/project:TestApp /prov:EFOracleProvider /
/inssdl:TestAppEFModel.Oracle.ssdl /project:TestAppEFModel
Microsoft (R) EdmGen version 3.5.0.0
Copyright (C) Microsoft Corporation 2007. All rights reserved.
Creating conceptual layer from storage layer...
Writing msl file...
Writing csdl file...
Writing object layer file...
Writing views file...
Generation Complete -- 0 errors, 0 warnings
Preparing the Application
There are two basic steps to preparing your application: Providing a DLL of the object model, and configuring your application for use with EFOracleProvider and your database. Providing the DLL is easy enough — take the two .cs files created by EdmGen.exe and throw them into a project, and reference that project in your app.
I had initially put all of the test and generation files in one root directory on my disk, just to keep track of them. This turned out to be a good idea for a secondary reason. In your application configuration, you must provide access to the metadata files you’ve just created. The example code in the EFOracleProvider project used the following syntax in its App.config:
<add name="NorthwindEntities"
connectionString="provider=EFOracleProvider;metadata=res://NorthwindEFModel/NorthwindEFModel.NorthwindEFModel.csdl|res://NorthwindEFModel/NorthwindEFModel.NorthwindEFModel.msl|res://NorthwindEFModel/NorthwindEFModel.NorthwindEFModel.ssdl;Provider Connection String='data source=XE;user id=edmuser;password=123456'" providerName="System.Data.EntityClient" />
Note those res:// addresses. I imagine those are used to refer to resource files, but I didn’t see any of the projects involved actually containing one. On top of that, there’s that double-namespace thing, i.e. NorthwindEFModel.NorthwindEFModel.csdl. I couldn’t get this syntax to even work a little bit with my configuration, so I just pointed my application at the root directory I created, that contained all of the necessary files. I imagine on a test or application server, leaving these files in a common place may be the most useful option.
<add name="TestServer"
connectionString="provider=EFOracleProvider;metadata=C:/OracleEFTests/TestAppEFModel.csdl|C:/OracleEFTests/TestAppEFModel.msl|C:/OracleEFTests/TestAppEFModel.Oracle.ssdl;Provider Connection String='Data Source=TNSNAME;User ID=user_id;Password=password...'" />
Don’t forget to add EFOracleProvider to the <DbProviderFactories> section of your configuration, as well. Also note that I didn’t use the providerName attribute, which in the test code was listed as System.Data.EntityClient, even though the provider in the connection string is our good old EFOracleProvider.
Finally, write a small piece of code to test the functionality.
using System;
using System.Collections.Generic;
using System.Data.EntityClient;
using System.Linq;
using System.Configuration;
using System.Text;
using TestAppEFModel;
namespace TestAppEFOracleTests
{
class Program
{
public static void Main()
{
TestAppEFModelContext context = new TestAppEFModelContext( ConfigurationManager.ConnectionStrings["TestServer"].ConnectionString );
var query = from asset in context.Assets
where asset.Asset_Type == "TEST ASSET TYPE"
select asset;
foreach(var asset in query)
Console.WriteLine("{0}, {1}",
asset.Asset_Number,
asset.Asset_Type);
}
}
}The Troubles
And we’re good to go, right? Not quite.
ORA-00942: table or view does not exist
Whoops. What did we do wrong? Well, let’s look at the SQL that was generated.
SELECT
"Extent1"."Asset_Number",
"Extent1"."Asset_Type"
FROM
"tblAsset" "Extent1"
WHERE
(N'TEST ASSET TYPE' = "Extent1"."Asset_Type")What’s wrong with that? Actually, something very subtle and dangerous that I’m sure is causing Oracle a lot of pain in trying to get a provider out for the Entity Framework. Observer the following session in SQL*Plus:
SQL> DESC tblAsset;
Name Null? Type
-------------- -------- --------------
ASSET_NUMBER NOT NULL VARCHAR2(50)
ASSET_TYPE VARCHAR2(50)
SQL> DESC "tblAsset";
ERROR:
ORA-04043: object "tblAsset" does not exist
SQL> DESC "TBLASSET";
Name Null? Type
-------------- -------- --------------
ASSET_NUMBER NOT NULL VARCHAR2(50)
ASSET_TYPE VARCHAR2(50)
This hearkens back to a long-standing conflict in dealing with Oracle and mixed-case or reserved identifier names, to wit: In order to ensure that table names that happen to be reserved keywords can be used, we need to enclose them in double-quotes. But we can create tables with or without quotes, with differing cases, and create two different tables. We may not be able to rely on the user telling us the exactly correct name of a table in our storage model file, but we can’t convert it to all caps without the risk of accidentally referring to a different table!
My solution at this point was to cave and refer to the table name in all caps in my storage model file. But now the problem is the column names! And we can’t refer to a column name in the storage model, but we can in the mapping file, the .msl, but we can’t simply generate the mapping file, change the column names to all caps, and have it compile. What I ended up doing was simply referring to all of the fields in all caps in the storage model, which of course breaks our code, making us refer to asset.ASSET_TYPE, as well as uglifying the constructor code for creating new objects:
/// <summary>
/// Create a new Assets object.
/// </summary>
/// <param name="aSSET_NUMBER">Initial value of ASSET_NUMBER.</param>
public static Assets CreateAssets(string aSSET_NUMBER)One More Thing
So, those are the big things, the deal-breakers. The largest problem to me is the idea that we can specify a table name in our storage model file, but not column names. This seems entirely too half-baked to me, but maybe I just don’t understand the conceptual tactics behind a move like that.
And I just noticed this as I was signing off, a quick bizarre thing I wanted to try:
var query = context.Assets.Where( a => a.ASSET_TYPE.Contains("A") ).Take(10);Take the top ten items that have an uppercase A in them. Simple enough, right? Nope. A rewriting of the generated SQL:
SELECT
asset_number,
asset_type
FROM
tblAsset
WHERE ((INSTR('A', asset_type)) >0)
AND ( ROWNUM <= (10) )This is a mess. First off, that ROWNUM clause is not going to do what we expect, no matter what the rest of the query is. Secondly, INSTR takes its parameters in the reverse order. Little things like this are going to be the hell Oracle would have to go through to get a decent provider out there for Entity Framework, and I don’t even envy them a little bit.
One more thing I want to say about all of this. The work I did above took seven hours, a lot of Googling, a lot of searching through documentation, and a lot of guessing. The following is how everything above would be handled in my current favorite Ruby ORM.
class Asset
include DataMapper::Resource
property :asset_type, String
property :asset_number, String
end
query = Asset.get :asset_type => 'TEST ASSET TYPE'This is not to compare languages or ideologies, but methodologies. Simplicity always trumps complexity in the software engineering world. Always always always. Make Entity Framework easier for people to use, and it could be a godsend for shops that want a Microsoft-written ORM solution.
I’ll try and answer any questions anyone has, but this is literally the maximum scope of what I was able to accomplish. I’ll probably throw myself back at it later this weekend.
67
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
