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

an inexperienced web developer crying out help!

introduction

i developing mvc web app (using asp.net core due interest in it). in back-end, there mssql server quite sophisticated databases (thousands of tables). in project, i'd present part of public data in view (in tables), based on user's queries (sending form requests) , later allowing user download data (csv, xml).

architecture challenges

• data access layer

first started using entity framework later realized can't translate sql statements linq. reason simplest query contains several inner joins , left joins , select statements , endless number of tables.

• business logic layer

i planning build rest api, sending data in json format. far concerned in .net core mvc can have api controller in same project presentation layer.

• presentation layer

this part have experience with, building web apps using mvc 5.

the big struggle

in project, not manipulate data, read , present them user. aware guidelines of using different model class (domain, entity, viewmodel)

what now, , suppose it's wrong:

• the mvc's api controller returns sql query results type datatable object (have sql helper class job), far controller takes care of serializing objects json.

• another controller (with model binding view) gets user search criteria via html-forms , calls api controller binding corresponding properties.

the questions finally

• am supposed stick raw sql queries instead of entity framework, , if so, shall use separated class libraries (as data access layer) , reference in api? or leave out dal , put sql query logic api?
• does 1 need use entity framework in case of reading data without manipulating it? manipulation intend format look-and-feel in logic layer.

update edit:

in sql queries, must create temporary tables not supported in linq. suggestions?

in case question marked architectural , not programming one, please accept apologies , kindly refer me right forum help.

many in advance!

you'll find linq queries easier understand , debug sql. keep data access layer separate project , have unit tests queries. keep solid principle, don't mix data layer api. if you're starting out, ef core might better ef6 because of speed , portability.