Left join attempt in Linq Expression returning unwanted null item

Question

I have the following linq expression:

var list = _context.Transactions.Where(x => x.Date >= request.DateStart && x.Date <= request.DateEnd)
            .Include(x => x.Organisation).DefaultIfEmpty()
            .ToList();

The Query is attempting to perform a left join on Transactions when a OraganisationID is present. In SQL it, the equivalent query would be:

select * from Transactions t 
where t.Date > request.DateStart AND T.Date <= request.DateEnd
left join Organisation o ON t.OrganisationId = o.OrganisationId

The linq expression works great except for when there are no matches for Transactions between the supplied dates, at which point it should return 0 results but actually returns one null item in the list.

I suspect this is related to the Include.(..).DefaultIfEmpty() but I am not sure how to go about resolving it.

The linq query should do the following

• When transactions do match, it should return all transactions along with the corresponding Organisation Entity based on Transactions.OrganisationId (this works)
• When a Transaction contains no matching Organisation it should return null for the Oragnisation Entity but still retuirn the Transaction entity itself (this works)
• When No Transactions match the dates provided, it should return 0 results instead of 1 null result (this does not work)

Below is the Transction Entity:

public class Transaction
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int TransactionId { get; set; } = default!;
    public int OrganisationId { get; set; } = 0;
        
    [ForeignKey("OrganisationId")]
    public Organisation? Organisation { get; set; } = default!;
}

Answer 1

DefaultIfEmpty() is not needed here, Include already should perform left join for non-required relations, just remove it:

var list = _context.Transactions
    .Where(x => x.Date >= request.DateStart && x.Date <= request.DateEnd)
    .Include(x => x.Organisation)
    .ToList();

As for the null element for empty result, from the docs:

Returns the elements in a sequence or a default valued singleton collection if the sequence is empty.

DefaultIfEmpty is applied to your IQueryable<Transaction>, hence the output of collection with single default element (i.e. null).

UPD

To correctly represent non-required relation with explicit key specification, key should be nullable

public class Transaction
{
    // ...
    public int? OrganisationId { get; set; }
}