CODEWITHSUNDEEP
linqentity-framework-core
Craig
Craig

Reputation: 18724

Getting a list from a grandparent relationship

I have a budget table, which has 0:many TransactionLines. A TransactionLine has a Transaction.

enter image description here

So a user created as transaction, which may be split into multiple transactionlines. A TransactionLine can be associated to zero or one Budget.

A Transaction has common details such as date, description. A transactionLine has the amounts, a budgetId, a transactionid and come account details.

I am trying to get a list of Transactions, which have been related to a budget.

But I've got stuck early with the linq.

var budget = context.Budget
    .Include(x=>x.TransactionLines)

I'm not sure how to pull in a distinct list of transaction records for a budget. It's possible that a transaction can have many lines related to the same budget..

In SQL terms, I'm trying to do this:

Select * from Budget b
inner join TransactionLine tl
on tl.BudgetId = b.id
INNER JOIN [Transaction] t
ON t.Id = tl.TransactionId

Upvotes: 0

Views: 29

Answers (2)

Thomas Koelle
Thomas Koelle

Reputation: 3742

Here is a solution using the linq sql syntax. I assume all FK are set up correctly

from row in TransactionLine
where row.Budget.Id == 5
select row.Transaction

Upvotes: 0

Carlos Parra
Carlos Parra

Reputation: 1067

Based on your SQL query, in LINQ you can do something like this:

UPDATE

Using EFCore .ThenIncludeMethod along with Include might give you the result you're expecting to have.

using (var context = new YourContext())
{
    var budget = context.Budget
        .Include(budget => budget.TransactionLine)
            .ThenInclude(transactionLine => transactionLine.Transaction)
        .ToList();
}

using LINQ-to-Entities

from b in Budget
join tl in TransactionLine
on b.Id equals tl.BudgetId
join t in Transaction
on tl.TransactionId equals t.Id
select new
{
    b.TransactionId,
    ... other properties...
}

You can also use LINQ methods syntax, this way:

Budgets
.Join(
    TransactionLine,
    budget => budget.Id,
    transactionLine => transactionLine.BudgetId,
    (budget, transactionLine) => new {budget, transactionLine})
.Join(
    Transaction,
    tl => tl.transactionLine.TransactionId,
    transaction => transaction.Id,
    (tl, transaction) => new 
    {
        tl,
        transaction
    });

Upvotes: 1

Related Questions