How to select unique rows from left table while doing left join operation between two tables in Enti-CodePudding

I have two tables, table A (loan_id, amount) and table B (id, loan_id). Now I want to select rows from table A which loan_id is not available in table B. For example

Table A has following rows:
loan_id   amount
-------   ------
1         200
2         400

Table B has following rows:
id     loan_id
--     -------
1      2

In the above scenario, I want to join this table based on loan_id and display only those rows which is not available in table B. I want output should be like following

 output:
loan_id   amount
-------   ------
1         200

how can I achieve this using Entity framework. So far, what I know that I need to perform left join and select those rows which B.id == null, however, I am not finding how to do this using c#, linq.

EDIT:

here I also added my Entity class:

[Table("loans")] ( in my given scenario this is table A)
public class Loan
{
    [Column("loan_id")]
    public int Id { get; set; }
    [Column("funding_amount")]
    public decimal FundingAmount { get; set; }
}

[Table("loan_approves")] (in my given scenario this is table B)
public class LoanApprove
{
    [Column("id")]
    public int Id { get; set; }
    [Column("loan_id")]
    public int LoanId { get; set; }
}

CodePudding user response：

Your query should looks like this:

var result = context.Loan
   .Where(l => !context.LoanApprove.Any(a => a.LoanId == l.Id))
   .ToList();

Or with NOT IN

var result = context.Loan
   .Where(l => !context.LoanApprove.Select(a => a.LoanId).Contains(l.Id))
   .ToList();

CodePudding user response：

Since you haven't provided any details of what your entity classes look like, this is just a guess:

Assuming you have:

class TableA
{
    public int LoanId { get; set; }
    public decimal Amount { get; set; }
    public List<TableB> TableBs { get; set; }
}

class TableB
{
    public int Id { get; set; }
    public int LoanId { get; set; }
    public TableA Loan { get; set; }
}

Then you simply need to use:

var result = context.TableAs.Where(a => !a.TableBs.Any()).ToList();