How to get data from 3 table into 1 list

Question

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.

Answer 1

I have 3 tables in SQL Server. Each table has 4 columns with the same name and datatype. I want to get data from 4 columns "Id, Name, Quantity, IdCategory" from 3 tables into 1 list. I use ASP.NET Core 6 MVC - code first.

Well, lot of way around to handle this kind of scenario. Easiest and most convenient way I would prefer to use view model or using Linq query.

Let's assume you have these models:

Models:

public class Bags
{
        public int Id { get; set; }
        public string Name { get; set; }
        public int Quantity { get; set; }
        public string Category { get; set; }
}

public class Shirts
{
        public int Id { get; set; }
        public string Name { get; set; }
        public int Quantity { get; set; }
        public string Category { get; set; }
}

public class Shoes
{
        public int Id { get; set; }
        public string Name { get; set; }
        public int Quantity { get; set; }
        public string Category { get; set; }
}

Seeds in models:

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" });

Method #1: using view model

public class AllViewModel
{
        public List<Bags> Bags { get; set; }
        public List<Shirts> Shirts { get; set; }
        public List<Shoes> Shoes { get; set; }
}

Query using view model:

var allTableUsingViewModel = new AllViewModel();
allTableUsingViewModel.Bags = listBags;
allTableUsingViewModel.Shirts = listShirts;
allTableUsingViewModel.Shoes = listShoes;

Output using view model:

Method #2: using Linq anonymous type

Query using Linq anonymous 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 anonymous 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" });

    // Method #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
                                       };

    // Method 2 - view model
    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 projection here.