locked
How to add table my join tables? ASP.NET MVC RRS feed

  • Question

  • User1670624291 posted

    My question is, inside my join of IDs is it possible to add another column with an ID? This is what Im trying to do:

    My Index:

            var orders= db.Orders.ToList();
            var colers = db.Colors.ToList();
            var result = (from c in orders
                         join st in colers on c.ID_Orders equals st.id into table1
                         select new OrderWithColorsViewModel { order =c, colers = table1.ToList() 
            }).ToList();
            return View(result);
    

    My Tables:

    public partial class Orders
    {
    public int ID_Orders { get; set; }
    public Nullable<System.DateTime> Data_Registo { get; set; }
    public string Num_Encomenda { get; set; }
    public string Ref_Cliente { get; set; }
    }
    
    public partial class Colors
    {
    public int ID_Orders { get; set; }
    public int ID_Line_Color { get; set; }
    public string Color{ get; set; }
    }
    
    public partial class Quantities
    {
    public int ID_Orders { get; set; }
    public int ID_Line_Color { get; set; }
    public int Quantities{ get; set; }
    }
    

    Of what i learning right now i have this from my join:

    order

    and:

    line_color

    what I want (I think):

    order_color-line

    If im wrong in thinking, correct me, thanks

    Thursday, December 17, 2020 2:00 PM

Answers

  • User1686398519 posted

    Hi MiguelMi, 

    You need to use "group clauses" to group data.

    I wrote an example, you can refer to it.

    Model

        public class OrderWithColorsQuantitiesViewModel
        {
            public Order order { get; set; }
            public List<ColorsWithQuantitiesViewModel> colers { get; set; }
        }
        public class ColorsWithQuantitiesViewModel
        {
            public Colors coler { get; set; }
            public List<Quantities> quantities { get; set; }
        }

    Controller

    1. Lambda expression
      •         public ActionResult Index()
                {
                    var orders = db.Orders.ToList();
                    var colors = db.Colors.ToList();
                    var quantities = db.Quantities.ToList();
                    //lambda expression
                    var re =orders.GroupJoin(colors, o => o.ID_Orders, c => c.ID_Orders,
                        (o, cgroup) => new // resultSelector 
                        {
                            order = o,
                            colors = cgroup.ToList()
                        })
                       .Where(i=>i.colors.Count!=0)
                       .Select(i =>
                           new OrderWithColorsQuantitiesViewModel
                           {
                               order=i.order,
                               colers= i.colors.GroupJoin(quantities,
                               co => new { orderId = co.ID_Orders, colorlineId = co.ID_Line_Color },
                               q => new { orderId = q.ID_Orders, colorlineId = q.ID_Color_Line },
                              (co, qgroup) => new ColorsWithQuantitiesViewModel
                              {
                                  coler  = co,
                                  quantities = qgroup.ToList()
                              }).ToList()
                           } 
                       );
                    return View(re);
                }
            }
    2. Or Query syntax
      •         public ActionResult Index()
                {
                    var orders = db.Orders.ToList();
                    var colors = db.Colors.ToList();
                    var quantities = db.Quantities.ToList();
                    //Query syntax
                    var result1 = (from o in orders
                                  join c in colors
                                     on o.ID_Orders equals c.ID_Orders into co
                                  group new { o, co } by o.ID_Orders into g
                                  from i in g
                                  select new {order=i.o,colors=i.co}).ToList();
                    var result2 = (from r1 in result1
                                  from c in r1.colors
                                   join q in quantities
                                     on new { orderId = c.ID_Orders, colorlineId = c.ID_Line_Color } equals new { orderId = q.ID_Orders, colorlineId = q.ID_Color_Line } into q
                                  group new ColorsWithQuantitiesViewModel { coler=c, quantities=q.ToList()}  by c.ID_Orders).ToList();
                    var result = (from r1 in result1
                                  join r2 in result2
                                     on r1.order.ID_Orders equals r2.Key
                                 select new OrderWithColorsQuantitiesViewModel
                                 {
                                     order=r1.order,
                                     colers=r2.ToList()
                                 }).ToList();
                    return View(result);
                }

    View

    @model IEnumerable<DailyMVCDemo.Models.OrderWithColorsQuantitiesViewModel>
    <table class="table table-borderless table-sm " ;>
        @foreach (var item in Model)
        {
            <tr style="border-top: 2px solid #cdd0d4;">
                <td style="width: 130px;">
                    <b>Artigo: </b>@item.order.ID_Orders
                </td>
                <td colspan="8">
                    <b>Modelo: </b>@item.order.Details_Orders
                </td>
            </tr>
            <tr>
                <td colspan="8">
                    <table class="table table-bordered">
                        @foreach (var item2 in item.colers)
                        {
                            <tr>
                                @if (item2.quantities.Count != 0)
                                {
                                    <td style="width: 150px;">
                                        @item2.coler.ID_Colors-@item2.coler.Name_Colors
                                    </td>
                                    <td>
                                        @foreach (var item3 in item2.quantities)
                                        {
                                            <ul class="list-group">
                                                <li class="list-group-item">@item3.ID_Quantities-@item3.Name_Quantities</li>
                                            </ul>
                                        }
                                    </td>
                                }
                            </tr>
                        }
                    </table>
                </td>
            </tr>
        }
    </table>

    Here is the result. 

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 22, 2020 3:21 AM
  • User1670624291 posted

    I think I’m beginning to understand a little bit of this :)

    Thanks  YihuiSun

    //Juntar tabelas
                var order = embOpen.ToList();
                var fabric = embRef.ToList();
                var coler = embQuant.ToList();
                var quant = db.Programa_Cor_Info.ToList();
                var statu = db.Programa_Cor_Info_Status.ToList();
                //Query syntax
    
    
                var result1 = (from o in order
                               join c in coler
                                  on o.ID_Programa equals c.ID_Programa into co
                               group new { o, co } by o.ID_Programa into g
                               from i in g
                               select new { order = i.o, colors = i.co }).ToList();
                var result2 = (from r1 in result1
                               from c in r1.colors
                               join q in quant
                                 on new { orderId = c.ID_Programa, colorlineId = c.ID_Linha_Cor } equals new { orderId = q.ID_Programa, colorlineId = q.ID_Linha_Cor } into p1
                               from p in p1
                               join s in statu
                                 on new { orderId = p.ID_Programa, colorlineId = p.ID_Linha_Cor } equals new { orderId = s.ID_Programa, colorlineId = s.ID_Linha_Cor } into q
                               group new ColorsAndQuantities { coler = c, quant = p1.ToList(), status = q.ToList() } by c.ID_Programa).ToList();
                var result = (from r1 in result1
                              join m in fabric
                                on r1.order.ID_Programa equals m.ID_Programa into t
                                from t1 in t
                              join r2 in result2
                                 on t1.ID_Programa equals r2.Key
                              select new OrdersColorsViewModel
                              {
                                  order = r1.order,
                                  malhas = t1,
                                  colers = r2.ToList()
                              }).ToList();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 30, 2020 9:49 AM

All replies

  • User1686398519 posted

    Hi MiguelMi, 

    Do you want to compare multiple columns when using join?Based on the code you provided, I wrote an example, you can refer to it.

    Model

        public class OrderWithColorsQuantitiesViewModel
        {
            public Order order { get; set; }
            public Colors colers { get; set; }
            public List<Quantities> quantities { get; set; }
    
        }
        public class Order
        {
            [Key]
            public int ID_Orders { get; set; }
            public string Details_Orders { get; set; }
        }
        public class Colors
        {
            [Key]
            public int ID_Colors { get; set; }
            public string Name_Colors { get; set; }
            public int ID_Line_Color { get; set; }
            public int ID_Orders { get; set; }
        }
        public class Quantities
        {
            [Key]
            public int ID_Quantities { get; set; }
            public string Name_Quantities { get; set; }
            public int ID_Orders { get; set; }
            public int ID_Color_Line { get; set; }
        }

    Controller

            public ActionResult Index()
            {
                var orders= db.Orders.ToList();
                var colers = db.Colors.ToList();
                var quantities = db.Quantities.ToList();
                var result = (from c in orders
                             join st in colers 
                                on c.ID_Orders equals st.ID_Orders into re
                             from r in re
                             join q in quantities 
                                on new {o= r.ID_Orders,c=r.ID_Line_Color} equals new {o=q.ID_Orders,c=q.ID_Color_Line } into table1
                             select 
                                new OrderWithColorsQuantitiesViewModel {
                                 order=c,
                                 colers=r,
                                 quantities=table1.ToList()
                                }
                             ).ToList();
                return View(result);
            }

    View

    @model IEnumerable<DailyMVCDemo.Models.OrderWithColorsQuantitiesViewModel>
    <table class="table">
        <tr>
            <td>ID_Orders</td>
            <td>Details_Orders</td>
            <td>ID_Colors</td>
            <td>Name_Colors</td>
            <td>
                Quantities
            </td>
        </tr>
        @foreach (var item in Model)
        {
            <tr>
                <td>@item.order.ID_Orders</td>
                <td>@item.order.Details_Orders</td>
                <td>@item.colers.ID_Colors</td>
                <td>@item.colers.Name_Colors</td>
                <td>
                    @if (item.quantities.Count != 0)
                    {
                        foreach (var item2 in item.quantities)
                        {
                            <ul class="list-group">
                                <li class="list-group-item">@item2.ID_Quantities- @item2.Name_Quantities</li>
                            </ul>
    
                        }
                    }
                    else
                    {
                            <p>Null</p>
                    }
                </td>
            </tr>
        }
    </table>

    Here is the result. 

    Best Regards,

    YihuiSun

    Friday, December 18, 2020 3:31 AM
  • User1670624291 posted

    Thanks for the help, but i have a problem because i'm already doing a color listing based on ID_Orders, and it happens to duplicate orders

    My View:

    table class="table table-borderless table-sm " ;>
    @foreach (var item in Model)
    {
        <tr style="border-top: 2px solid #cdd0d4;">
            <td style="width: 130px;">
                <b>Artigo: </b>@item.order.Cod_Artigo
    
            </td>
            <td colspan="8">
                <b>Modelo: </b>@item.order.Modelo
            </td>
        </tr>
        <tr>
            <td colspan="8">
                <table class="table table-bordered">
               
                    @foreach (var item2 in item.coler)
                    {
                        <tr>
                            <td style="width: 150px;">
                                @item2.Cor
                            </td>
                            <td>
                                @if (item.quant.Count != 0)
                                {
                                    foreach (var item3 in item.quant)
                                    {
                                        <ul class="list-group">
                                            <li class="list-group-item">@item3.Cliente</li>
                                        </ul>
    
                                    }
                                }
                                else
                                {
                                    <p>Null</p>
                                }
                            </td>                     
                        </tr>
                    }
                </table>
            </td>
        </tr>
    }
    </table>

    possibly I am not organizing my view in the best way...

    My error:

    Im not having an match with my tables in the color line look the example, i add my line color IDs from table Colers and Quantities an my view

    and what is happening is that when searching for ID_Orders it doubles.

    My Join:

    var result = (from c in orders
                             join st in colers 
                                on c.ID_Orders equals st.ID_Orders into table1
                             from st in table1
                             join q in quant 
                                on new {o= st .ID_Orders,c=st .ID_Line_Color} equals new {o=q.ID_Orders,c=q.ID_Color_Line } into table1
                             select 
                                new OrderWithColorsQuantitiesViewModel {
                                 order=c,
                                 colers=table1.ToList()
                                 quant=table2.ToList()
                                }

    Friday, December 18, 2020 9:35 AM
  • User1686398519 posted

    Hi MiguelMi, 

    You need to use "group clauses" to group data.

    I wrote an example, you can refer to it.

    Model

        public class OrderWithColorsQuantitiesViewModel
        {
            public Order order { get; set; }
            public List<ColorsWithQuantitiesViewModel> colers { get; set; }
        }
        public class ColorsWithQuantitiesViewModel
        {
            public Colors coler { get; set; }
            public List<Quantities> quantities { get; set; }
        }

    Controller

    1. Lambda expression
      •         public ActionResult Index()
                {
                    var orders = db.Orders.ToList();
                    var colors = db.Colors.ToList();
                    var quantities = db.Quantities.ToList();
                    //lambda expression
                    var re =orders.GroupJoin(colors, o => o.ID_Orders, c => c.ID_Orders,
                        (o, cgroup) => new // resultSelector 
                        {
                            order = o,
                            colors = cgroup.ToList()
                        })
                       .Where(i=>i.colors.Count!=0)
                       .Select(i =>
                           new OrderWithColorsQuantitiesViewModel
                           {
                               order=i.order,
                               colers= i.colors.GroupJoin(quantities,
                               co => new { orderId = co.ID_Orders, colorlineId = co.ID_Line_Color },
                               q => new { orderId = q.ID_Orders, colorlineId = q.ID_Color_Line },
                              (co, qgroup) => new ColorsWithQuantitiesViewModel
                              {
                                  coler  = co,
                                  quantities = qgroup.ToList()
                              }).ToList()
                           } 
                       );
                    return View(re);
                }
            }
    2. Or Query syntax
      •         public ActionResult Index()
                {
                    var orders = db.Orders.ToList();
                    var colors = db.Colors.ToList();
                    var quantities = db.Quantities.ToList();
                    //Query syntax
                    var result1 = (from o in orders
                                  join c in colors
                                     on o.ID_Orders equals c.ID_Orders into co
                                  group new { o, co } by o.ID_Orders into g
                                  from i in g
                                  select new {order=i.o,colors=i.co}).ToList();
                    var result2 = (from r1 in result1
                                  from c in r1.colors
                                   join q in quantities
                                     on new { orderId = c.ID_Orders, colorlineId = c.ID_Line_Color } equals new { orderId = q.ID_Orders, colorlineId = q.ID_Color_Line } into q
                                  group new ColorsWithQuantitiesViewModel { coler=c, quantities=q.ToList()}  by c.ID_Orders).ToList();
                    var result = (from r1 in result1
                                  join r2 in result2
                                     on r1.order.ID_Orders equals r2.Key
                                 select new OrderWithColorsQuantitiesViewModel
                                 {
                                     order=r1.order,
                                     colers=r2.ToList()
                                 }).ToList();
                    return View(result);
                }

    View

    @model IEnumerable<DailyMVCDemo.Models.OrderWithColorsQuantitiesViewModel>
    <table class="table table-borderless table-sm " ;>
        @foreach (var item in Model)
        {
            <tr style="border-top: 2px solid #cdd0d4;">
                <td style="width: 130px;">
                    <b>Artigo: </b>@item.order.ID_Orders
                </td>
                <td colspan="8">
                    <b>Modelo: </b>@item.order.Details_Orders
                </td>
            </tr>
            <tr>
                <td colspan="8">
                    <table class="table table-bordered">
                        @foreach (var item2 in item.colers)
                        {
                            <tr>
                                @if (item2.quantities.Count != 0)
                                {
                                    <td style="width: 150px;">
                                        @item2.coler.ID_Colors-@item2.coler.Name_Colors
                                    </td>
                                    <td>
                                        @foreach (var item3 in item2.quantities)
                                        {
                                            <ul class="list-group">
                                                <li class="list-group-item">@item3.ID_Quantities-@item3.Name_Quantities</li>
                                            </ul>
                                        }
                                    </td>
                                }
                            </tr>
                        }
                    </table>
                </td>
            </tr>
        }
    </table>

    Here is the result. 

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 22, 2020 3:21 AM
  • User1670624291 posted

    First, thanks for all the help, second where can I find more information about the joins?

    I need to add two more tables:

    add Fabrics my Orders

    public partial class Fabrics
        {
            public int ID_Fabrics { get; set; }
            public string Ref{ get; set; }
        }

    and add Status my Quantities

    public partial class Status
    { public int ID_Order { get; set; } public int ID_Color_Line { get; set; } public string Status { get; set; } public string Obs { get; set; } }

    I tried this based on your code but it's going bad, I need to study more:

                var result0 = (from o in order
                               join s in statu
                                  on o.ID_Programa equals s.ID_Programa into so
                               group new { o, so } by o.ID_Programa into st
                               from i in st
                               select new { order = i.o, statu = i.so }).ToList();
                var result1 = (from r0 in result0
                               join c in coler
                                  on r0.order.ID_Programa equals c.ID_Programa into co
                               group new { r0, co } by r0.order.ID_Programa into g
                               from i in g
                               select new { order = i.r0, colors = i.co }).ToList();
                var result2 = (from r1 in result1
                               from c in r1.colors
                               join q in quant
                                 on new { orderId = c.ID_Programa, colorlineId = c.ID_Linha_Cor } equals new { orderId = q.ID_Programa, colorlineId = q.ID_Linha_Cor } into q
                               group new ColorsAndQuantities { coler = c, quants = q.ToList() } by c.ID_Programa).ToList();
                var result = (from r1 in result1
                              join r2 in result2
                                 on r1.order.order.ID_Programa equals r2.Key
                              select new OrdersColorsViewModel 
                              {
                                  order = r1.order.order,         
                                  colers = r2.ToList()
                              }).ToList();

    to start I also created the classes:

    public class ColorsViewModel
        {
            public Orders order { get; set; }
            public List<Fabrics> fabric{ get; set; }
            public List<ColorsAndQuantities> colers { get; set; }
        }
    
    public class ColorsAndQuantities
        {
            public Colors coler { get; set; }
            public List<QuantitiesAndStatus> quants { get; set; }
        }
    
    public class QuantitiesAndStatus
        {
            public Quantities quant { get; set; }
            public List<Status> status { get; set; }
        }

    am I thinking the right way?

    Thanks again for your help

    Wednesday, December 23, 2020 4:43 PM
  • User1670624291 posted

    I think I’m beginning to understand a little bit of this :)

    Thanks  YihuiSun

    //Juntar tabelas
                var order = embOpen.ToList();
                var fabric = embRef.ToList();
                var coler = embQuant.ToList();
                var quant = db.Programa_Cor_Info.ToList();
                var statu = db.Programa_Cor_Info_Status.ToList();
                //Query syntax
    
    
                var result1 = (from o in order
                               join c in coler
                                  on o.ID_Programa equals c.ID_Programa into co
                               group new { o, co } by o.ID_Programa into g
                               from i in g
                               select new { order = i.o, colors = i.co }).ToList();
                var result2 = (from r1 in result1
                               from c in r1.colors
                               join q in quant
                                 on new { orderId = c.ID_Programa, colorlineId = c.ID_Linha_Cor } equals new { orderId = q.ID_Programa, colorlineId = q.ID_Linha_Cor } into p1
                               from p in p1
                               join s in statu
                                 on new { orderId = p.ID_Programa, colorlineId = p.ID_Linha_Cor } equals new { orderId = s.ID_Programa, colorlineId = s.ID_Linha_Cor } into q
                               group new ColorsAndQuantities { coler = c, quant = p1.ToList(), status = q.ToList() } by c.ID_Programa).ToList();
                var result = (from r1 in result1
                              join m in fabric
                                on r1.order.ID_Programa equals m.ID_Programa into t
                                from t1 in t
                              join r2 in result2
                                 on t1.ID_Programa equals r2.Key
                              select new OrdersColorsViewModel
                              {
                                  order = r1.order,
                                  malhas = t1,
                                  colers = r2.ToList()
                              }).ToList();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 30, 2020 9:49 AM