Sorry for my bad English.

Here is my SQL Design.

I have 3 table in Sqlsever. Each table has 4 column with same name, same datatype.

And i want to get data from 4 column "Id, Name, Quantity, IdCategory" from 3 table into 1 list object same as returning value in this code below:

public async Task<IEnumerable<Shirt>> LoadAllShirt()
{
    return await _dbContext.Shirt.ToListAsync();
}

I use .NET Core 6 Mvc - code first. Thanks for your help.

CodePudding user response：

I have 3 table in Sqlsever. Each table has 4 column with same name, same datatype. And I want to get data from 4 column "Id, Name, Quantity, IdCategory" from 3 table into 1 list, I use .NET Core 6 Mvc - code first.

Well, lot of way around to handle this kind of scenario. Most easy and convenient way I would prefer to use

Way: 2 using Linq Annonymous Type:

Query Using Linq Annonymous Type:

var AllTableListUsingLinq = from a in listBags
                             join b in listShirts on a.Id equals b.Id
                             join c in listShoes on b.Id equals c.Id
                             select new
                             {
                                 FromBagsID = a.Id,
                                 FromBagsName = a.Name,
                                 FromBagsQuantity = a.Quantity,
                                 FromBagsCategory = a.Category,

                                 FromShirtsID = b.Id,
                                 FromShirtsName = b.Name,
                                 FromShirtsQuantity = b.Quantity,
                                 FromShirtsCategory = b.Category,

                                 FromShoesID = c.Id,
                                 FromShoesName = c.Name,
                                 FromShoesQuantity = c.Quantity,
                                 FromShoesCategory = c.Category

                             };

Output Using Linq Annonymous Type:

Full Controller:

        [HttpGet("GetFrom3Tables")]
        public IActionResult GetFrom3Tables()
        {
            List<Bags> listBags = new List<Bags>();
            listBags.Add(new Bags() {  Id = 101, Name = "Bag A", Quantity =10, Category = "Cat-A"});
            listBags.Add(new Bags() {  Id = 102, Name = "Bag B", Quantity =15, Category = "Cat-A"});
            listBags.Add(new Bags() {  Id = 103, Name = "Bag C", Quantity =20, Category = "Cat-A"});

            List<Shirts> listShirts = new List<Shirts>();
            listShirts.Add(new Shirts() { Id = 101, Name = "Shirt A", Quantity = 10, Category = "Cat-B" });
            listShirts.Add(new Shirts() { Id = 102, Name = "Shirt B", Quantity = 15, Category = "Cat-B" });
            listShirts.Add(new Shirts() { Id = 103, Name = "Shirt C", Quantity = 20, Category = "Cat-B" });

            List<Shoes> listShoes = new List<Shoes>();
            listShoes.Add(new Shoes() { Id = 101, Name = "Shirt A", Quantity = 10, Category = "Cat-S" });
            listShoes.Add(new Shoes() { Id = 102, Name = "Shirt B", Quantity = 15, Category = "Cat-S" });
            listShoes.Add(new Shoes() { Id = 103, Name = "Shirt C", Quantity = 20, Category = "Cat-S" });

            //Way: 1 Linq Query

            var AllTableListUsingLinq = from a in listBags
                             join b in listShirts on a.Id equals b.Id
                             join c in listShoes on b.Id equals c.Id
                             select new
                             {
                                 FromBagsID = a.Id,
                                 FromBagsName = a.Name,
                                 FromBagsQuantity = a.Quantity,
                                 FromBagsCategory = a.Category,

                                 FromShirtsID = b.Id,
                                 FromShirtsName = b.Name,
                                 FromShirtsQuantity = b.Quantity,
                                 FromShirtsCategory = b.Category,

                                 FromShoesID = c.Id,
                                 FromShoesName = c.Name,
                                 FromShoesQuantity = c.Quantity,
                                 FromShoesCategory = c.Category

                             };

            //Way: 2 : ViewModel
            var allTableUsingViewModel = new AllViewModel();
            allTableUsingViewModel.Bags = listBags;
            allTableUsingViewModel.Shirts = listShirts;
            allTableUsingViewModel.Shoes = listShoes;
           

            return Ok(AllTableListUsingLinq);
        }

Note: If you need more information you could check our official document for View Model and Linq Projction here

CodePudding user response：

The following sample query will list your 3 types of data into a single result set.

var allResults = resultSet1.Concat(resultSet2);

For the return type create a class which will be the parent class for all your products (Bag,Shirt,Shoes) Which will help you to return data in a single Generic data.

If you use any non-generic list to send the data like hashtable or Arraylist then then there will be no issue.

In my way I will suggest to use generic data list as it will help you fetch data in better time complexity.

CodePudding user response：

In this case you may need to define additional indirect base class with these 4 parameters. Than you can create Collection of this base class, and concatinate all 3 tables into.

public class BaseEntity
{
    public string Name {get;set;}
}

public class Shoes : BaseEntity
{

}
...

public IEnumerable<BaseEntity> GetAllTables()
{
    var shirts = await _dbContext.Shirt.ToListAsync();
    var shoes = await _dbContext.Shoes.ToListAsync();
    var bags = await _dbContext.Bags.ToListAsync();

    return shirts.Concat(shoes).Concat(bags);
}

Similar example but witout casting to base class is shown in Enumerable.Concat documentation: https://learn.microsoft.com/pl-pl/dotnet/api/system.linq.enumerable.concat?view=net-7.0