Search code examples
c#sql-serverentity-frameworkasp.net-coremulti-tier

Right way to use ORM in ASP.NET Core MVC?

An inexperienced web developer crying out for help!

Introduction

I am developing an MVC web app (using ASP.NET Core due to my interest in it). In the back-end, there is an MSSQL Server with quite sophisticated Databases (thousands of tables). In my project, I'd like to present part of the public data in the View (in Tables), based on the user's queries (sending Form requests) and later allowing the user to download the data (CSV, XML).

Architecture challenges

  • Data Access Layer

First I started with using Entity Framework but later realized that simply can't translate all my SQL statements to LINQ. The reason is the simplest query contains several INNER JOINS and LEFT JOINS and SELECT statements and an endless number of Tables.

  • Business Logic Layer

I am planning to build a REST API, sending the data in JSON format. As far as I concerned in .NET Core MVC I can have my API Controller in the same project as my presentation layer.

  • Presentation Layer

This is the only part I have experience with, building web apps using MVC 5.

The big struggle

In this project, I will not manipulate the data, only READ it and present them to the user. I am aware the guidelines of using different Model class (Domain, Entity, ViewModel)

What I do now, and I suppose it's wrong:

  • The MVC's API Controller returns the SQL query results as type DataTable object (have an SQL Helper class to do the job), so far my controller takes care of serializing the objects as JSON.

  • Another Controller (with model binding from the view) gets the user search criteria via HTML-forms and calls the API Controller with binding the corresponding properties.

The questions finally

  • Am I supposed to stick to raw SQL queries instead of Entity Framework, and if so, shall I use simply separated class libraries (as Data Access Layer) and reference it in the API? Or leave out the DAL and put all the SQL query logic into the API?
  • Does one need to use Entity Framework in case of only reading the Data without manipulating it? The only manipulation I intend to do is to format the look-and-feel in the logic layer.

UPDATE EDIT:

In my SQL queries, I must create temporary tables which are not supported in LINQ. Any suggestions?

In case this question will be marked as an architectural and not a programming one, please accept my apologies and kindly refer me to the right forum where I could get help.

Many thanks in advance!

Solution

  • You'll find LINQ queries much easier to understand and debug than SQL. Keep the Data Access Layer as a separate project and have unit tests for the queries. To keep with the SOLID principle, don't mix the data layer with the api. If you're just starting out, EF Core might be better than EF6 mainly because of speed and portability.