Entity model design recommendation needed

Question

Hello there,

I am working on an ASP.NET Core Blazor Server application. It is a sort of order management system. Trying to follow clean architecture design. In the core business, there are entities like orders, suppliers, and clients. Each one of them has its own repositories for adding, updating, and retrieving data. So far I am planning to construct entities as follows;

Orders

Order Id
Order Number
Client Name
Supplier Name
Shipment Number
Employee Name
Order Status
Date
Description

Clients

Client Id
Client Name
Tax Administration
Address
Telephone
email
Responsible Name
Responsible email
Description

Suppliers

Supplier Id
Supplier Name
Address
Telephone
email
Responsible Name
Responsible email
Description

Orders will be added via excel, but two problems come to mind.

1. If there is a supplier or customer that is not in the system, how can I check it in excel?
2. What to do if the user wants to see customer or supplier information (address, email, etc) in the orders report?

Thanks in advance.

Answer 1

Hi @Cenk ,

How with EF Core if models have no relations?

If you don't configure the relationship via EF core, you can use LINQ left outer joins to join the table and get the related entities information.

For example, I created the following model:

public class Order  
{  
    public int OrderId { get; set; }  
    public int OrderNumber { get; set; }  
    public string ClientName { get; set; }  
    public string SupplierName { get; set; }  
    public string ShipmentNumber { get; set; }  
    public string EmployeeName { get; set; }  
    public string OrderStatus { get; set; }  
    public DateTime Date { get; set; }  
    public string Description { get; set; }  
}  
public class Client  
{  
    public int ClientId { get; set; }  
    public string ClientName { get; set; }  
    public string TaxAdministration { get; set; }  
    public string Address { get; set; }  
    public string Telephone { get; set; }  
    public string email { get; set; }  
    public string ResponsibleName { get; set; }  
    public string Responsibleemail { get; set; }  
    public string Description { get; set; }  
}  
public class Supplier  
{  
    public int SupplierId { get; set; }  
    public string SupplierName { get; set; }  
    public string Address { get; set; }  
    public string Telephone { get; set; }  
    public string email { get; set; }  
    public string ResponsibleName { get; set; }  
    public string Responsibleemail { get; set; }  
    public string Description { get; set; }  
}  

Then, add the ApplicationDbContext as below:

public class ApplicationDbContext : IdentityDbContext  
{  
    public DbSet<Order> Orders { get; set; }  
    public DbSet<Client> Clients { get; set; }  
    public DbSet<Supplier> Suppliers { get; set; }  
 
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)  
        : base(options)  
    {  
          
    }  
}  

Then, enable migration to generate the relate table in the database and add the test data:

After that, in the controller, perform left outer joins to get the orders and its related clients and supplier information, if the clients or supplier does not exist, it will show null.

View the code from here: 213811-linq.txt

The result is as below:

The output model is like this, you can add other information.

public class Output  
{  
    public int OrderNumber { get; set; }  
    public string OrderDate { get; set; }  
    public string ClientName { get; set; }  
    public string Client_Address { get; set; }  
    public string SupplierName { get; set; }  
    public string Suoplier_Email { get; set; }  
}  

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion

Answer 2

Hi @Cenk ,

Orders will be added via excel, but two problems come to mind.

If there is a supplier or customer that is not in the system, how can I check it in excel?

You can check whether the supplier and customer exist or not before inserting the data into the database, but not check it in excel.
After reading the excel data, you can loop through the data and then check whether the supplier and customer exist or not, then based on the result to insert order or insert the supplier or customer.

What to do if the user wants to see customer or supplier information (address, email, etc) in the orders report?

If you are using ADO.NET to query the database, based on your entity model, you can use the JOIN clause to join the orders and customer or supplier table, then get the related data.

If you are using Entity Framework/Entity Framework Core (EF Core), you can configure relationships between the Orders and Customer or Supplier model, then using Eager Loading to load the related entities. Refer to the following tutorials:

EF Core Relationships

Configuring One To Many Relationships in Entity Framework Core

EF Core Loading Related Data

Eager Loading of Related Data

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion

Answer 3

Hi @Zhi Lv - MSFT ,

Thank you for your reply. The excel that the user will save into the database is only the orders entity. If I have a relationship with clients and suppliers, then there is nothing related other than Client Name and Supplier Name. Do you understand what I am trying to say?

Thank you.

Answer 4

Thank you @Zhi Lv - MSFT ,

Do you mean in the Orders table/Model, the Client Name and Supplier Name was stored the Clients's Client Name property and the Suppliers's Supplier Name property, right?

Yes, there is no relation between models.

Before inserting the Order entity, you can query the Clients and Supplier table and check whether the current order's client name and supplier name exist or not.

This part is OK.

you can join the orders table and the clients or supplier table based on the Client Name and Supplier Name property, then you can get the related customer or supplier information

How with EF Core if models have no relations?

Answer 5

@Zhi Lv - MSFT thank you so much for your time and effort. I couldn't figure out the joins but now it is much clear. I'll bother you if I have any other questions :)