2

I have taken over the maintenance and development of a mature .NET Framework ASP.NET MVC application which uses an Entity Framework repository pattern. Several developers have worked on it before me and there are various styles. Database access is predominently accomplished though a mixture of EF LINQ and raw SQL. Most of the SQL is in stored procedures but there are lots of views. There is some raw SQL in the repositories.

I intend to do as much new development in .NET Core as I can, which I intend put in a Web API. I'm uncertain about whether to use Entity Framework or not. It was my intention to do so and I have invested a lot of my own time in learning EF Core, predominently from the book "Entity Framework Core in Action" by JP Smith, but also from web sources. It feels like the "right" approach is to use EF Core. MS pushes this. Lots of job adds list it in the required skills.

I think all the learning materials I have come across, including the book, cover very simple data models. For example, books, authors, reviews, orders, order lines. Even for these simple data models, writing a query in SQL seems easier to me than writing it in EF. Often when explaining what an EF query is doing the author will show the (horrid) generated SQL. Surely, if we need to worry about tweaking the EF code to improve the SQL we might as well just write the SQL? The data model I am dealing with is in another league from the those discussed in the EF learning materials I have come across. I have trouble enough understanding the SQL sometimes without trying to write such queries in EF. Maybe EF is OK with simple queries but we need to use raw SQL when it gets hard?

Often times, to get the data needed for a web page, several queries are needed with the output of one being the input of another. EF doesn't seem ideal for this.

I'm not stupid, neither am I a genius. I'm a standard developer, willing to spend some of my own time learing new skills, but I think to master EF Core I'd need to invest a huge amount more time. Why bother with EF at all I'm starting to think. Are any other ORMs better? Should I just use Dapper? It seems to be a vast edifice to generate SQL that may not be as good as I can write.

Improve this question

2 Answers 2

Reset to default
3

Often when explaining what an EF query is doing the author will show the (horrid) generated SQL.

It doesn't matter what the generated SQL looks like, if it accomplishes its intended purpose. You will never maintain that SQL. "Accomplishes its intended purpose" means that it retrieves the requested data with adequate performance characteristics, not that the SQL wins a beauty contest.

Surely, if we need to worry about tweaking the EF code to improve the SQL we might as well just write the SQL?

EF is what I call an "eighty-percent solution." You would only write SQL and use raw queries for the 20 percent (or less) of data access that requires it.

The data model I am dealing with is in another league from the those discussed in the EF learning materials I have come across.

The examples provided in the documentation are simpler than most real-world database schemas.

I think to master EF Core I'd need to invest a huge amount more time.

You don't need to be a "master" to make it work. The mixture of EF LINQ and raw SQL you described is exactly what happens in most real-world projects, and you already understand that.

Improve this answer
3
  • 1
    "EF is what I call an "eighty-percent solution." You would only write SQL and use raw queries for the 20 percent (or less) of data access that requires it." -- this is an important sentence. People regard ORMs as a 100% solution way too often. Getting the ORM to do all data access requires such intimate knowledge and such a large amount of time you are better off writing SQL and using the DB libs directly for some use cases. Just make sure it is properly encapsulated in a class or separate library to facilitate testing and reduce effort to switch DB vendors.
    – Greg Burghardt
    Commented May 20, 2022 at 15:48
  • I first started writing web apps with C++ and ADO, returning disconnected recordsets to ASP pages. SQL was hardcoded in the data access layer. It worked fine, without a hugely complicated ORM to learn about. I guess working with Dapper is like this. I'm just thinking maybe, for me, EF is a huge waste of time.
    – Ian
    Commented May 23, 2022 at 10:07
  • You can think of Dapper as a modern equivalent of your ADO technique, except that it takes three to five lines of code to write a query instead of 20 or 30. Raw queries in Entity Framework work the same way. Dapper also has a CRUD extension, if you still want the 80 percent solution.
    – Robert Harvey
    Commented May 23, 2022 at 12:00
0

You should use an ORM to save you writing your own.

It sounds like your problem is more that there is no consistent design choice in where you put your data access logic.

I think EF contributes to this problem by not working well with a repository pattern.

If you were using a simple ORM or writing your own SQL, and denied yourself "business logic on the db" in the form of stored procedures and views, then you are almost forced to put all your "selection logic" in a DAL or Repository class. You code becomes neat and all your data access is compartmentalised naturally.

EF provides generic repository objects and; (glossing over code first a bit) generates entity objects which might not be quite what you want.

So its quick and easy, but leaves an open question about where you put filtering and joining, or mapping more complex objects, to which there is no good answer.

Throw in views, stored procedures and random sql and you further complicate the situation.

However, EF is a pretty tried and true solution these days, I don't think you need to worry about it generating bad sql or not being able to handle complex databases.

Just make that architectural design choice and ask if it works better with EF or a lighter ORM.

Improve this answer
4
  • EF works well with a repository pattern in the sense that it is using a repository (and unit of work) pattern itself. The issue arises when people try to wrap the existing uow/repo that EF provides and then balk at the effort required to reinvent the same interface again themselves. The solution here is to either not bother with the wrapper, or not balk at needing to write and maintain it.
    – Flater
    Commented May 21, 2022 at 12:26
  • well exactly. it doesn't work well with the repo pattern, because it is a generic repo. see para (4)
    – Ewan
    Commented May 21, 2022 at 12:31
  • My point was more that it doesn't quite make sense to balk at the effort of writing an additional repo wrapper if that is what you've decided you want to implement. It's also not that difficult to do when done right if you're relying on generic base repo's to handle the straightforward repo methods that apply across all of your entities. I've set this up numerous times and as long as you make sure to not leak your IQueryables and also roll your own unit of work (or, alternatively, do away with the repo wrapper entirely), it can be elegantly handled.
    – Flater
    Commented May 21, 2022 at 12:34
  • I think that's the best solution, but it begs the question of why use EF if you are going to hide it, and potentially your EF entity objects. Also its not the way of the EF examples, which would have you using EF in your controllers/BL. Hence "no good answer"
    – Ewan
    Commented May 21, 2022 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.