question

osyris-3187 avatar image
0 Votes"
osyris-3187 asked ZhiLv-MSFT answered

Combinding multiple Database Tables

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?

dotnet-aspnet-generaldotnet-aspnet-core-webapidotnet-aspnet-core-generaldotnet-aspnet-data
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

It all depends on your application design. Keep in mind, stored procedures are very common in DB design.

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.

The standard approach, found in every beginning level tutorial, is populating a model from the data access layer. Is there anyway, you can provide code that illustrates the problem you are facing with the LEFT JOIN? Is this a Razor Pages or MVC application?

Keep in mind, actions should return a view model not an entity. Anyway, I recommend the following tutorials.

Razor Pages with Entity Framework Core in ASP.NET Core - Tutorial 1 of 8
ASP.NET Core MVC with EF Core - tutorial series


0 Votes 0 ·
DuaneArnold-0443 avatar image
0 Votes"
DuaneArnold-0443 answered DuaneArnold-0443 edited

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://docs.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://docs.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://docs.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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZhiLv-MSFT avatar image
0 Votes"
ZhiLv-MSFT answered

Hi @osyris-3187,

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.