Should I stick onto Entity Framework?

Question

I adopted EF (out of box of .NET 4.5) in my new internet web app.

this app do involve quite some operation over db activities, and involve around 30 or more tables.

my point is this is not a simple school project, where EF typically suit for most needs.

as further I developing this app, I found EF very limited or prohibitive for proper/good db design (especially in term over performance)

1) Include

I encountering the Include feature in the query part. Many data missing is due to missing include setting.

the more I putting in this thing, the more I worry a simple data retrieval is getting more things than I needed in certain function.

2) Validation

I'm adopting Fluent Validation for this need, as I prefer the visitor pattern, where no direct code changes needed over the data code itself.

but then I getting more challenges as I subclassing, I need to workout validation able to respect OOP simple stuff. I managed it, though more complexity and certain things I really dislike in its implementation. I believe this subclass validation issue happened in DataAnnotation as well.

3) Transaction

Now, I need to incorporate proper db transactaction control, and I found this lacking in EF, correct me if I'm wrong.

I used to worked with Enterprise component (COM+ in .NET, long-long time ago), and I can't find the proper (or the lack of it) transaction implementation in EF.

I'm still working on this...

concluding)

I come to realize the only thing EF has helped me in my coding, is the data/entity code generation, which this is common feature over many other tool/framework.

I'm thinking to drop this EF, switch back to pre-EF era approach, adopting stored-proc, still code-generated table class (but not EF or DBML).

I can do without SQL LINQ, as I have many challenges over this in past over performance issue.

I like your opinion that I should stay and stick to EF, for whatever reason that my simple mind can perceive, apart from this ORM, which hardly is realistically working at all.

Answer 1

To be honest, I few years ago I'd still suggest any .net/sql newcomer to go with EF because of the linq syntax, it looks sexy... But that's all about it. There's honestly nothing else you may ever gain from EF.

I know that the temptation of writing C# instead of SQL might sound very useful at the beginning but in fact it's not. No machine-generated sql can ever beat handcrafted piece of SQl beast you end up having at the end of the day :)

Other than that, EF is just eating the memory which is the web server's memory and CPU ,well, in most cases I think, but not sure. Still, it's consuming way more resources performancewise and memorywise.

And I'm entirely fed up to the ears with the heavy heavy 20 pound edmx model GUI. +1 pound for every table :) Loosing so much time keeping the edmx in sync with the actual DB, and what reason for after all? Who said you need all that colorful icons for your tables in VS, really? All that stuff is there just for one and the only reason - to make linq-to-entities possible. To hell with it, I just find original SQL looking in no way less nice or cool.

So, I myself made a decision some time ago - no EF for my personal projects. No heavy buggy stuff, life's easy when you're taking and making it easy.

Answer 2

Have you looked into Dapper? Dapper is a micro ORM which is super fast. It was developed by Stackoverflow folks (Sam Saffron) and they use it on this site extensively for the very reasons you mention - performance and speed. Here is the link:

https://github.com/StackExchange/dapper-dot-net.

We ditched EF and we use Dapper exclusively. Combined with some other community developed Dapper extensions like Dapper.SimpleCRUD and Dapper.SimpleCRUD.ModelGenerator we can quickly generate POCOs from database while retaining all the advantages that Dapper has over EF. Overall you will be writing a bit more code but the speed of Dapper is almost equivalent to using ADO.NET's SqlDataReader. Here's a link to SimpleCRUD:

https://github.com/ericdc1/Dapper.SimpleCRUD/

Answer 3

I have my own data access layer created using Service stack's ORMlite, and toptensoftware's PetaPoco.

ORMlite:

1) Create table from C# class, including relations, constrains etc.. (CodeFirst)

2) Can do Insert, Delete, update etc..

example - Creating table: Employee.EnsureTable()

Petapoco:

Providers great SQL wrapper class, I use this instead of linq

for Example: sql.Select("FirstName,LastName").From("Employee").Where("ID=@0",100).AND("Active=@0",1)

I prefer above style than LINQ. This also supports Joins (Cool right?)

Currently ORM lite is not a free one. But you can get the older version which is free.

Example:

//Creating Entity Class

public class Employee:DatabaseEntity<Employee>
{
    //Define properties
}

//DatabaseEntity is my own base class which provides many helper methods from ORM lite and Petapoco, including many static methods eg:EnsureTable()

//Creating Table

Employee.EnsureTable()

//Adding new entity
Employee e=new Employee();
e.FirstName="Chola"
e.LastName="Raja"
e.Active=true
e.Save()

//Find an employee
e=Employee.FirstOrDefault(x=>x.ID==101 && x.Active==true);
//OR
e=Employee.QuerySingle(sql.Select("FirstName,LastName").From("Employee").Where("ID=@0",100).AND("Active=@0",1))

//update
e.LastName="Raja"
e.Save()

//Delete a entity
e.Delete()

//Transaction
e.AssignProject(project1)  //this method could do many operation on many entities using Transaction scope

Note: I Could not able to extract code from my project. But you can create your own Base class according to your requirement

Answer 4

I agree EF is less useful when you get beyond a certain stage

If your database is useful beyond your application then design the DB for your enterprise, not just your application. This is where EF falls down. It does not (cannot) consider the different viewpoints onto the database the other players in need, so you end up with naive indexes and relationships.

My 2c (quick response to a very complex question):

Write SPs (yeah, I know) to manage the data collection, provide useful data sets to your business layer and allow inserts/updates to your underlying tables. Make sure you write a useful data API though, don't just write CRUD for each table. That's a pointless waste of time.

Use EF to hook into these SPs. EF generates useful data classes, gives you a transaction wrapper and quite a few other useful bits & bobs.

Enjoy!

No one tool does everything - pick & choose as circumstances arise.