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
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?