Joining 2 Table on 2 Equals Column in ASP.NET

Question

please help me fix my code to join 2 tables but 2 equals. I have 2 tables (Transaction and Chartaccount)

Chartaccount Table
+---------+--------+
|KodeAkun |NamaAkun|
+---------+--------+
|10000    |Asset   |
|20000    |Expense |
|30000    |Revenue |
+------------------+

Transaction Table
+---------+--------+---------+-------+
|KodeAkunD| DEBIT  |KodeAkunK|KREDIT |
+---------+--------+---------+-------+
|10000    |10000   |20000    |0      |
+------------------+---------+-------+

In My Model

Chartaccount Model
public string KodeAkun {get; set;}
public string NamaAkun {get; set;}
public decimal Saldo {get; set;}

Transaction Model
public string KodeAkunD {get; set;}
public string KodeAkunK {get; set;}
public decimal Debit {get; set;}
public decimal Kredit {get; set;}
public string Keterangan {get; set;}

In My controller

public IActionResult Index()
{
var transaction1 = from p in _context.Transaction join k in _context.ChartAccount on p.KodeakunD equals k.Kodeakun
                           select new Transaction {
                           KodeakunD = p.KodeakunD + k.Namaakun,
                           KodeakunK = p.KodeakunK + k.Namaakun};          
return View(transaction1);
}

Currently in my view

+-----------+--------+----------+-------+-------------------+
|KodeAkunD  | DEBIT  |KodeAkunK |KREDIT |Action             |
+-----------+--------+----------+-------+-------------------+
|10000Asset |10000   |20000Asset|0      |Edit,Details,Delete|
+-----------+--------+----------+-------+-------------------+

what i want

+-----------+--------+------------+-------+-------------------+
|KodeAkunD  | DEBIT  |KodeAkunK   |KREDIT |Action             |
+-----------+--------+------------+-------+-------------------+
|10000Asset |10000   |20000Expense|0      |Edit,Details,Delete|
+-----------+--------+------------+-------+-------------------+

and also, if i join in controller, my action not route correctly, if i click on my action it return 404

It seems to be working as expected – Gauravsa Jan 18 '19 at 08:44 — Gauravsa, Jan 18 '19 at 08:44
it work but not work as i expected :D – David Budiman Jan 18 '19 at 08:53 — David Budiman, Jan 18 '19 at 08:53

Mhsn · Answer 1 · 2019-01-22T07:58:54.500

Your problem is in this line

KodeakunK = p.KodeakunK + k.Namaakun

p.KodeakunK= 2000, but you joined table on p.KodeakunD which is the join line of 1000 that leads to ´Asset´.

You need to join KodeakunK and KodeakunD individually with Chartaccount. You can do two different joins as alternative or you should use linq multiple joins.

Possible duplicates of LINQ to Entity : Multiple join conditions

Linq multiple join conditions using extra variables

LINQ multiple joins with multiple conditions

How to do joins in LINQ on multiple fields in single join

These can help you.

Joining 2 Table on 2 Equals Column in ASP.NET

1 Answers1