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
selectnew OrderWithColorsViewModel { order =c, colers = table1.ToList()
}).ToList();
return View(result);
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);
}
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
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()
}
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
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);
}
}
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);
}
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
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; }
}
//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();
Member
4 Points
16 Posts
How to add table my join tables? ASP.NET MVC
Dec 17, 2020 02:00 PM|MiguelMi|LINK
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:
and:
what I want (I think):
If im wrong in thinking, correct me, thanks
Contributor
2400 Points
690 Posts
Re: How to add table my join tables? ASP.NET MVC
Dec 18, 2020 03:31 AM|YihuiSun|LINK
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
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
Here is the result.
Best Regards,
YihuiSun
Member
4 Points
16 Posts
Re: How to add table my join tables? ASP.NET MVC
Dec 18, 2020 09:35 AM|MiguelMi|LINK
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:
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:
Contributor
2400 Points
690 Posts
Re: How to add table my join tables? ASP.NET MVC
Dec 22, 2020 03:21 AM|YihuiSun|LINK
Hi MiguelMi,
You need to use "group clauses" to group data.
I wrote an example, you can refer to it.
Model
Controller
View
Here is the result.
Best Regards,
YihuiSun
Member
4 Points
16 Posts
Re: How to add table my join tables? ASP.NET MVC
Dec 23, 2020 04:43 PM|MiguelMi|LINK
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
and add Status my Quantities
I tried this based on your code but it's going bad, I need to study more:
to start I also created the classes:
am I thinking the right way?
Thanks again for your help
Member
4 Points
16 Posts
Re: How to add table my join tables? ASP.NET MVC
Dec 30, 2020 09:49 AM|MiguelMi|LINK
I think I’m beginning to understand a little bit of this :)
Thanks YihuiSun