How do I create a Data Access Layer objects from database first approach?

Question

I have built a SQL database with my CustomerDetails, ProductDetails, OrderDetails from scratch and also stored procedures to create new Customers, new Products and Orders.

So now I want to build a data access layer from C#. I don't know how to associate the entity framework data object declaration such as the following code to map my SQL data tables, because using the following code will be a code first approach and entity framework will generate the SQL code for me. I don't want EF generated SQL code because it is so buggy.

public int ProductID { get; set; }
public string ProductName { get; set; }
public string Description { get; set; }
public int? OrderID { get; set; }

I have checked out the following reference which is so daunting for me, because a lot of reference pages are out of date (downloads are no longer available and the User Interface of Visual Studio has been completely changed since), and some are involved in MVC which make things unnecessarily complicated.

https://learn.microsoft.com/en-us/aspnet/web-forms/overview/data-access/introduction/

I have also checked out using Table Adapters. But it doesn't allow me to create data objects. All data objects are automatically generated.

So how do I hand-code explicitly both SQL code and data object code if you know what I mean? My goal is to be able model my data objects according to the SQL table and call stored procedures already created in SQL to perform all kinds of operations. Please let me know where I can take reference.

https://github.com/sjh37/EntityFramework-Reverse-POCO-Code-First-Generator <-- This T4 script does everything you need. EF Core also has its own scaffolding, but last time I checked templates weren't customizable and overall it left leaving _a lot_ to be desired. — Dai, May 18 '22 at 22:58
What you probably need is an EDMX (database first approach), with it you can update EF model from database - here's a similar q+a on SO on how to create it: https://stackoverflow.com/questions/51487232/how-to-create-an-entity-framework-edmx-file-for-an-existing-database-with-net-s. And a step by step description can be found here: https://www.yogihosting.com/entity-framework-create-edmx-file/. Let me know if it helped. — Matt, May 19 '22 at 06:42
yeah, i realised it:s called database first approach. I am studying this https://www.youtube.com/watch?v=ZX7_12fwQLU&t=38s — Hoy Cheung, May 19 '22 at 19:27
@Matt EDMX can be considered deprecated. Certainly not something use for new code. — Gert Arnold, May 22 '22 at 15:35
@GertArnold - Thanks for the hint. What would you suggest for a database first approach? — Matt, May 23 '22 at 07:02

score 1 · Accepted Answer · edited May 23 '22 at 07:06

From what I saw you are trying to do databasefist approach so you need to do the following things:

First you need to open the package manager console. That is from
View -> Other Windows -> Package Manager Console.
There you need to make sure that the default project is the project you want to work with.

Then run this command

Scaffold-DbContext -Provider Microsoft.EntityFrameworkCore.SqlServer 
                   -Connection "Data Source=.\SQLExpress;Initial Catalog=DatabaseName;Integrated Security=True"

Change the "Data Source" to your SQL instance and "Initial Catalog" with your database name

(This will work only if you use SqlServer)

How do I create a Data Access Layer objects from database first approach?

1 Answers1