4

I need help architecting a data access layer using Entity Framework. Here are the requirements:

  • The database is preexisting, an absolute mess, and it's structure and cannot be altered
  • Entity Framework will be used
  • The database will be used by dozens of .NET MVC applications each with their own set of business objects, logic, and read/write permissions.
  • The data objects required by these applications usually bear little resemblance to the tables stored in the database.
  • The data access layer needs to be as simple to use as possible for the developers using it.

I've never worked on anything of this scope before and would appreciate any help on how to architect this solution. What I have so far is:

Project 1: DataLayer

• contains a purely EF-generated data model

Project 2: BusinessLayer

• contains logic to convert objects from the DataLayer into business objects used by the views and vice versa. Most (but not all) logic is shared amongst applications. • Contains common DTO / business objects used by all or multiple applications

Project 3: ServiceLayer.Application1

• contains all the DTO / business objects for Application 1 • contains a repository to be used by Application 1

Project 4: ServiceLayer.Application2

• contains all the DTO / business objects for Application 2 • contains a repository to be used by Application 2

Say an MVC application using the data layer needed to get back an IQueryable, and the business object FacultyFormattedForAView consists of data from 6 different tables with some business logic thrown on top. Here is how I envision things going:

  1. The application creates an instance of their respective repository in the service layer and calls IQueryable GetAllFacultyFormattedForAView().
  2. The service layer makes a call to the business layer: IQueryable GetAllFaculty(). The Faculty business object consists of data from 4 different tables.
  3. The business layer hands back a LINQ statement converting objects from the generated EF classes living in the data layer to a Faculty
  4. The service layer converts the Faculty objects into FacultyFormattedForAView objects and returns the result to the UI

Using this structure, the common palette of commands would exist in the business layer to avoid any code duplication and the repository would only expose the commands that a particular application has access to and provide additional formatting specific to that application.

I am using IQueryable rather than IEnumerable so paging and sorting can be used. Seeing how I'm only exposing the business objects / DTO to the developers, I figured this was ok although I would like it if they wouldn't have to do a ToList() or anything in order to actually execute the query.

This is the best I could come up with but I can't shake the feeling I could be doing something better.

I would appreciate any and all feedback.

Thank you.

Improve this question
11
  • 1
    I would not advise that you use ef for this. The ef datamodel will introduce an unneeded and difficult layer
    – Ewan
    CommentedMay 3, 2016 at 18:52
  • Thank you for your comment. What would you recommend instead of using EF?
    – Ben
    CommentedMay 3, 2016 at 18:55
  • Given the "absolute mess, cannot be changed" nature of the database, I think I'd be opting for a custom data access layer where you can exert some control. If you need some help with data retrieval, use Dapper.
    – Robert Harvey
    CommentedMay 3, 2016 at 21:32
  • Robert: I appreciate the feedback, but EF is a requirement. The customer doesn't want anyone hand-writing SQL. If there was an alternate solution that didn't involve me hand-writing SQL I may be able to convince them to use something other than EF.
    – Ben
    CommentedMay 3, 2016 at 23:04
  • Then I would use EF in a "database-first" strategy to create a Data Access Layer. The rest of your question is just about ordinary architecture.
    – Robert Harvey
    CommentedMay 4, 2016 at 1:25

1 Answer 1

Reset to default
2

For the sad "Messy DBs" part I recommend

  • a book by Scott Ambler "Database Refactoring", it teaches you a lot about the real world of databases, and no, it sure will not save you from SQL, but it might save your product's life, i.e. get it to a maintainable state.
  • don't fear the SQL, master it (TSQ, PL/SQL and other native DB languages)

And EF, a few points from my experience

  • EF is good for greenfield projects where there is expectation of a mapping problem between the data in DB and the .NET object instances
  • don't use SQL if you are creating a PoC or a demo, or an application that doesn't seem to scale in size, complexity and speed of processing
  • you should not constrain yourself not to use SQL if you want to build a maintainable application (in performance manner) - there are pretty good reasons for bits of SQL, at least in the parts highly dependant on performance
  • I've seen almost well-behaving EF solutions larger DBs (billions of rows read-write tables, in production for up to millions of users), and no, it is not possible to get rid of SQL if you "really mean it".

My advice for the EF part:

  • hire a real SQL hands-on-operations guru who actually is a programmer and knows the ORMs, so he will not tend to make SQL a Gold Hammer, and let the EF/SQL decisions on him. I've met some of those people and, sadly for most of my career, had to supply this role on various projects, and I can't imagine, how the projects would end up without this kind of expertise available.
Improve this answer

    Start asking to get answers

    Find the answer to your question by asking.

    Ask question

    Explore related questions

    See similar questions with these tags.