I have 1 main table and 3 tables for ordering the fields in the main table. I return all these tables as lists.

The main table structure is like this:

And The Ordering tables have Field Names in the main table and another numeric field for their ordering number, an example :

Now I want to order the Main table based on ordering numbers in Ordering Tables in Linq. In case on the main table if there is no corresponding field in the ordering table then the ordering number should be 0 (In ColorOrdering there is no Purple For example)

Just to be concrete I have made a working sample in Access and Query is as below:

SELECT Cars.Color, Cars.Make, Cars.Country,
IIf([MakeOrder] Is Null,0,[MakeOrder]) AS MkOrder,
IIf([ColorOrder] Is Null,0,[ColorOrder]) AS ClOrder,
IIf([CountryOrder] Is Null,0,[CountryOrder]) AS CntOrder
FROM ((Cars LEFT JOIN ColorOrder ON Cars.Color = ColorOrder.Color) 
LEFT JOIN MakeOrder ON Cars.Make = MakeOrder.Make) 
LEFT JOIN CountryOrder ON Cars.Country = CountryOrder.Country
ORDER BY 
IIf([MakeOrder] Is Null,0,[MakeOrder]),
IIf([ColorOrder] Is Null,0,[ColorOrder]), 
IIf([CountryOrder] Is Null,0,[CountryOrder]);

My problem is one of the ordering tables has no records (Country Table). So when I make a join to that table, whatever I do I keep getting,

System.NullReferenceException: 'Object reference not set to an instance of an object.' cntord was null.

Here is my Linq Query, help on this is much appreciated

List<CarsWithOrdering> carsWithOrdering = (from c in Cars
//Also tried this, doesn't work
//join cntord in CountryOrderingList.DefaultIfEmpty()
// on c.Country equals cntord.Country
join cntord in CountryOrderingList
on c.Country equals cntord.Country into lcntord
from cntord in lcntord.DefaultIfEmpty()
//........ (Other 2 left joins similar to above one)
select new CarsWithOrdering
{
Color = c.Color,
Make = c.Make,
Country = c.Country,        
ColorOrder = int.Parse(colord.ItemValue) ?? 0,
MakeOrder = int.Parse(makord.ItemValue) ?? 0,
CountryOrder = int.Parse(cntord.ItemValue) ?? 0
}).ToList();

CodePudding user response：

Enumerable.DefaultIfEmpty Method

public static System.Collections.Generic.IEnumerable<TSource> DefaultIfEmpty<TSource> (this System.Collections.Generic.IEnumerable<TSource> source, TSource defaultValue);

Returns

IEnumerable An IEnumerable that contains defaultValue if source is empty; otherwise, source.

.DefaultIfEmpty() will return null when the list is empty.

Solution

Hence, you need to do null checking for the LEFT JOIN table's value before accessing its properties.

List<CarsWithOrdering> carsWithOrdering = (from c in Cars
    join cntord in CountryOrderingList
    on c.Country equals cntord.Country into lcntord
    from cntord in lcntord.DefaultIfEmpty()
    //Other LEFT JOIN tables
    select new CarsWithOrdering
    {
        Color = c.Color,
        Make = c.Make,
        Country = c.Country,        
        ColorOrder = colord != null ? int.Parse(colord.ItemValue) : 0,
        MakeOrder = makord != null ? int.Parse(makord.ItemValue) : 0,
        CountryOrder = cntord != null ? int.Parse(cntord.ItemValue) : 0
    })
    .ToList();