How to get one row of a table and a list of rows that are related to the first row in Linq.NET.
I have a specific problem. I don't know much about SQL, so this might be a stupid question. I have a class
class StructuredResult
{
public Pay Pay{ get; set; }
public Processing Processing{ get; set; }
public Member Member { get; set; }
public StructuredResult()
{
}
}
And this is the code I am getting with
var allPaying = (from pay in Entities.Pays
join prc in Entities.Processingss on pay.IDCalculation equals cal.IDCalculation
join mbr in Entities.Members on pay.IDMember equals mbr.IDMember
where pay.IDMember == IDMember
orderby prc.DateFrom descending
select new StructuredResult()
{
Pay = pay,
Processing = prc,
Member = mbr
}).ToList();
The code works fine. I changed the settings for posting here, maybe some name changes are out of order. I need to get the following:
class StructuredResult
{
public Pay Pay{ get; set; }
public Processing Processing{ get; set; }
public Member Member { get; set; }
public List<PayDetail> PayDetails { get; set; }
public StructuredResult()
{
}
}
Does anyone know if this is possible? Payment details are linked to Pay.ID = PayDetail.ID Thanks in advance
source to share
To also populate with a StructuredResult
collection PayDetail
, you need to use Group Join
. Cm:
- MSDN at
GroupJoin
- And the last Linq to Entities joins the groupjoin .
So:
var allPaying = from pay in Entities.Pays
join prc in Entities.Processingss on pay.IDCalculation equals cal.IDCalculation
join mbr in Entities.Members on pay.IDMember equals mbr.IDMember
join d in Entities.PayDetails on pay.IDMember equals d.ID into details
where pay.IDMember == IDMember
orderby prc.DateFrom descending
select new StructuredResult()
{
Pay = pay,
Processing = prc,
Member = mbr,
PayDetails = details
};
I would also recommend this since you are using EF to view navigation properties. By defining your classes a little differently, you get a lot more possibilities with much less (no need to write connections, for example)
- MSDN - Entity Structure Relationships and Navigation Properties
- MSDN - A Practical Guide. Navigation using navigation properties
And just a little advice - you define a default constructor with no logic in id. If this is true in real code as well, then there is no need for it. It is detected automatically.
source to share
This might help you:
var allPaying = (from pay in Entities.Pays
join prc in Entities.Processingss on pay.IDCalculation equals cal.IDCalculation
join mbr in Entities.Members on pay.IDMember equals mbr.IDMember
where pay.IDMember == IDMember
orderby prc.DateFrom descending
select new StructuredResult()
{
Pay = pay,
Processing = prc,
Member = mbr,
PayDetails = (from pd in Entities.PayDetails
where pd.ID == pay.IDMember
select pd).ToList())
}).ToList();
source to share