How to join tables only if not empty?

I have following LINQ query:

var LINQFilter = (from Cash in _DataTable_Cash.AsEnumerable()
                  join CashOpeningsAssignments in _DataTable_CashOpeningsAssignments.AsEnumerable().Where(a => (a.Field<Int32>("cashopeningassignmentstatus_id") == 1 || a.Field<Int32>("cashopeningassignmentstatus_id") == 2))
                       on Cash.Field<Int32>("cash_id") equals CashOpeningsAssignments.Field<Int32>("cash_id") into into_cashopeningsassignments
                  from CashOpeningsAssignments in into_cashopeningsassignments.DefaultIfEmpty()
                  join Users in _DataTable_Users.AsEnumerable()
                       on CashOpeningsAssignments.Field<Int32>("user_id") equals Users.Field<Int32>("user_id") into into_users
                  from Users in into_users.DefaultIfEmpty() 
                  select new
                  {
                      cash_id = Cash.Field<Int32>("cash_id"),
                      cellar_name = Cellars.Field<String>("cellar_name"),
                      cash_name = Cash.Field<String>("cash_name"),
                      cashstatus_name = CashStatus.Field<String>("cashstatus_name"),
                      user_name = (Users == null ? "[No Data]" : Users.Field<String>("user_firstname") + (Char)32 + Users.Field<String>("user_lastname")),
                      cashtransaction_amount = (Cash.Field<Int32>("cashstatus_id") == 2 ? 0.00 : 150.00)
                  });

I have problems showing the result because this Field returns null: CashOpeningsAssignments.Field<Int32>("user_id") when CashOpeningsAssignments is Empty.

I tried moving the .DefaultIfEmpty() into users but still not working, Any idea how i can solve this?

Solution

Answer

Use the overload of DefaultIfEmpty to create an empty item.

E.g.

into_cashopeningsassignments
    .DefaultIfEmpty(new CashOpeningsAssignments())

Running Code

The code reflects what you're trying to do, even though I took some liberties with it. For instance, I used List<T> instead of DataTable, because I did not figure out how to use Field<T>(string name) in a DotNetFiddle.

It's live here: https://dotnetfiddle.net/YaAc6D

using System;
using System.Linq;
using System.Collections.Generic;

public class Program
{
    public static void Main()
    {   
        var query = 
            from Cash 
            in _DataTable_Cash.AsEnumerable()
            join CashOpeningsAssignments 
            in _DataTable_CashOpeningsAssignments.AsEnumerable()
                .Where(a => 
                    (a.cashopeningassignmentstatus_id == 1 || 
                     a.cashopeningassignmentstatus_id == 2))
            on Cash.cash_id 
            equals CashOpeningsAssignments.cash_id 
            into into_cashopeningsassignments

            from CashOpeningsAssignments 
            in into_cashopeningsassignments.DefaultIfEmpty(new CashOpeningsAssignments()) 
            join Users 
            in _DataTable_Users.AsEnumerable()
            on CashOpeningsAssignments.user_id 
            equals Users.user_id
            into into_users

            from Users 
            in into_users.DefaultIfEmpty() 
            select new
            {
                cash_id = Cash.cash_id,
                // cellar_name = Cellars.cellar_name,
                cash_name = Cash.cash_name,
                // cashstatus_name = CashStatus.cashstatus_name,
                user_name = (Users == null ? "[No Data]" : Users.user_firstname + (Char)32 + Users.user_lastname),
                cashtransaction_amount = (Cash.cashstatus_id == 2 ? 0.00 : 150.00)
            };

        foreach(var result in query)
        {
            Console.WriteLine(result);
        }
    }

    public static List<Cash> _DataTable_Cash = 
        new List<Cash> { new Cash() };

    public static List<Cellars> _DataTable_Cellars = 
        new List<Cellars> { new Cellars() };

    public static List<CashStatus> _DataTable_CashStatus = 
        new List<CashStatus> { new CashStatus() };

    public static List<CashOpeningsAssignments> _DataTable_CashOpeningsAssignments = 
        new List<CashOpeningsAssignments> { };

    public static List<Users> _DataTable_Users =
        new List<Users>() { new Users() };
}

public class Cash
{
    public int cash_id { get; set; }
    public string cash_name { get; set; }
    public int cellar_id { get; set; }
    public int cashstatus_id { get; set; }
}

public class Cellars
{
    public string cellar_name { get; set; }
    public int cellar_id { get; set; }
}

public class CashStatus
{
    public int cashstatus_id { get; set; }
    public string cashstatus_name { get; set; }
}

public class CashOpeningsAssignments
{
    public int user_id { get; set; }
    public int cash_id { get; set; }
    public int cashopeningassignmentstatus_id { get; set; }
}

public class Users
{
    public string user_firstname { get; set; }
    public string user_lastname { get; set; }
    public int user_id { get; set; }
}

How to join tables only if not empty?

Answer

Running Code

See Also