.NET (290) administrative (42) Ajax (42) AngularJS (1) ASP.NET (144) bicycle (2) books (178) browser (8) C# (128) cars (1) chess (27) CodePlex (10) Coma (8) database (46) deployment (3) Entity Framework (2) essay (109) flash/shockwave (2) flex (1) food (3) friend (2) game (20) idea (5) IIS (8) javascript (81) LInQ (2) Linux (6) management (4) manga (43) misc (663) mobile (1) movies (88) MsAccess (1) murder (2) music (65) mysql (1) news (98) permanent (1) personal (66) PHP (1) physics (2) picture (308) places (12) politics (13) programming (495) rant (118) religion (3) science (40) Sharepoint (3) software (57) T4 (2) technology (11) Test Driven Development (4) translation (2) VB (2) video (100) Visual Studio (44) web design (45) Windows API (8) Windows Forms (3) Windows Server (4) WPF/Silverlight (63) XML (11)

Friday, May 08, 2009

The golden hammer of database applications

I have been working on a silly little project that involved importing files and then querying the data in a very specific way. And I wanted to do it with the latest technologies so I used The Entity Framework! (imagine a little glowing halo around that name and a choir in the background).

Well, how do I do an efficient import in Linq to Entities? I can't! At most I can instantiate a lot of classes and add them to the DataModel, then SaveChanges. In the background this translates to a lot of insert statements. So it occurred to me that I don't really need Entities here. All I needed is good old fashioned ADO.Net and a SqlBulkCopy object. So I used it like that. A bit of unfortunate translation of objects to a DataTable because the SqlBulkCopy class knowns how to import only a DataTable and I was set.

Ok, now back to the querying the data. I could have used ADO.Net, of course, and in this project, I would probably have been right, but I suspected the requirements for the project will change so I used Entities. It worked like a charm and yes, the requirements did get bigger and stranger as I went. But then I had to select the users that have amassed a number of rows in two related tables (or use a value in the user table) but only if the total number of amassed rows would satisfy a formula based on a string column in the user table that mapped to a certain value stored in the web.config a complicated query. I did it (with some difficulty) in Linq, then I had to solve all kind of weird issues like not being able to compare a class variable with an enum value because the number of types that can be used in a Linq to Entities query is pretty limited at the moment.

Well, was it the best way? I don't know. The generated SQL is something containing a lot of select from select from select sometimes 6 or 7 levels deep. Even the joining is done with select from tables. Shouldn't I have used a stored procedure instead?

To top it off, I listened to a podcast today about Object Databases. They do away with ORMs because there is no relational to begin with. The guy argued that if you need to persist objects, wouldn't an Object Database be more appropriate? And, as reporting would be a bitch when having to query large amounts of tabular data, shouldn't one use a Relational Database for this particular task in the project?

So this is it. It got me thinking. Is the database/data access layer the biggest golden hammer out there? Are we trying to use a single data access model and then build our applications around it like big twisting spaghetti golden nails?

No comments: