Combinding multiple Database Tables

Question

I would export data from multiple database tables into one new custom table so i can send it to the client side
and show that combineded table there

I have seen LINQ query can be used for joining multiple tables in Entity Framework
and I have also seen Multiple tables can be Combined in MySQL using:
INNER JOIN, LEFT JOIN clause, RIGHT JOIN clause, CROSS JOIN clause

My first question is what is the better or more common and more usefull way to achive this
(what do most programmers prefer or find beter ) or does it not matter at all?

My second question is: lets say I have combined a couple of tables inside MySQL using for example LEFT JOIN clause
How do I use that combined table so I can use it in Asp net core and then send it to the client side.

with an already existing table in the database it is just as simple as :

[HttpGet]
        public async Task<IEnumerable<Products>> getProducts()
        {
            var products = from m in _dbContext.Products
                              select m;

            return await products.ToListAsync();
        }

asp net core reads the Products from the DbContext.cs That is able to get and send data in the database.
but how would that work with a Joined tabled Created in MySQL?

Answer 1

My first question is what is the better or more common and more usefull way to achive this
(what do most programmers prefer or find beter ) or does it not matter at all?

Most would use an ORM like Entity Framework, Nhibernate or even a micro ORM like Dapper. You can even use raw T-SQL with them as well. But you can also use straight up MYSQL Command objects and T-SQL too.

My second question is: lets say I have combined a couple of tables inside MySQL using for example LEFT JOIN clause
How do I use that combined table so I can use it in Asp net core and then send it to the client side.

A DTO travels and it's a data container object that can be used to shape the data that travels between the service and the client for CRUD operations with the database on the backend..

https://en.wikipedia.org/wiki/Data_transfer_object#:~:text=In%20the%20field%20of%20programming,that%20carries%20data%20between%20processes.&text=In%20other%20words%2C%20DTOs%20are,transferring%20data%20over%20the%20wire.

https://learn.microsoft.com/en-us/aspnet/web-api/overview/data/using-web-api-with-entity-framework/part-5

https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

asp net core reads the Products from the DbContext.cs That is able to get and send data in the database.
but how would that work with a Joined tabled Created in MySQL?

You would use a DTO to shape the data that is to be sent to the client, and on the flip side, if you send the data back to the service, the one must know the data that belongs to each table to be persisted to.

And since you are using .NET Core try to implement some of the below.

https://learn.microsoft.com/en-us/archive/msdn-magazine/2016/may/asp-net-writing-clean-code-in-asp-net-core-with-dependency-injection

https://en.wikipedia.org/wiki/Separation_of_concerns

https://learn.microsoft.com/en-us/dotnet/architecture/modern-web-apps-azure/architectural-principles

Some like to use the generic repository pattern, I don't and I use the DAO pattern, becuase EF is already using the repository pattern.

https://www.thereformedprogrammer.net/is-the-repository-pattern-useful-with-entity-framework-core/

https://javarevisited.blogspot.com/2013/01/data-access-object-dao-design-pattern-java-tutorial-example.html

https://stackify.com/csharp-catch-all-exceptions/

Here is an example ASP.NET MVC client solution that is doing CRUD with a WebAPI using EF in the Data Access Layer (DAL) and the DTO pattern you can examine, which is implementing things talked about.

https://github.com/darnold924/PublishingCompany

Answer 2

Hi @osyris ,

My first question is what is the better or more common and more usefull way to achive this
(what do most programmers prefer or find beter ) or does it not matter at all?

The purpose of the ORM framework is to simplify the work of developers at the expense of some performance, but I personally think that in most cases, it is worthwhile.

Most database operations in a project should be simple CRUD. It is not a good idea to spend energy on these tasks. We should let the ORM framework complete these trivial tasks for us.

If some tasks are complex or have high performance requirements, then it is certainly better to use stored procedures.

So, you should choose suitable methods in suitable places according to the actual situation.

My second question is: lets say I have combined a couple of tables inside MySQL using for example LEFT JOIN clause
How do I use that combined table so I can use it in Asp net core and then send it to the client side.

You could create a CombinedModel model which contains the combined table properties, then returns it to the client side. Code like this:

var categories = _dbContext.Categories.ToList(); //query the Categories table  
var products  =_dbContext.Products.ToList();      //query the products table  
var innerJoinQuery =  
    from category in categories  
    join prod in products on category.ID equals prod.CategoryID  
    select new CombinedModel { ProductName = prod.Name, Category = category.Name }; //produces flat sequence   //get the combined table properties.  

Reference:

join clause (C# Reference)

Working with Stored Procedure in Entity Framework Core

Raw SQL Queries

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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