Linq returns null when invoice does not contains any items

Question

I have an invoice and it can contains items called tracks. InvoiceLine is the junction table to connect the two. Everything works fine if an invoice contains at least one track. What I want to achieve is to return the invoice even if it contains no tracks.

My initial code:

var screenset =
from inv in context.Invoices where inv.InvoiceId == invoiceID
join line in context.InvoiceLines on inv.InvoiceId equals line.InvoiceId
join track in context.Tracks on line.TrackId equals track.TrackId
select new InvoiceAndItemsDTO
{
    InvoiceId = inv.InvoiceId,
    InvoiceDate = inv.InvoiceDate,
    InvoiceTotal = inv.Total,
    CustomerId = inv.CustomerId,
    CustomerFullName = inv.Customer.LastName + ", " + inv.Customer.FirstName,
    CustomerPhoneNumber = inv.Customer.Phone,
    InvoiceLineId = line.InvoiceLineId,
    TrackId = track.TrackId,
    TrackName = track.Name,
    TrackPrice = track.UnitPrice,
    Artist = track.Album.Artist.Name,
    UnitPrice = line.UnitPrice,
    Quantity = line.Quantity,
    Action = "None"
};

The above will return the invoice and tracks correctly if it contains any track.

Basically the above code is an inner join so if there are no tracks the result set will be empty. So I corrected my code to include the invoice even when it contains no tracks.

var screenset =
from inv in context.Invoices where inv.InvoiceId == invoiceID
from j in context.Invoices where j.InvoiceId == invoiceID
join line in context.InvoiceLines on j.InvoiceId equals line.InvoiceId
join track in context.Tracks on line.TrackId equals track.TrackId
select new InvoiceAndItemsDTO
{
    InvoiceId = inv.InvoiceId,
    InvoiceDate = inv.InvoiceDate,
    InvoiceTotal = inv.Total,
    CustomerId = inv.CustomerId,
    CustomerFullName = inv.Customer.LastName + ", " + inv.Customer.FirstName,
    CustomerPhoneNumber = inv.Customer.Phone,
    InvoiceLineId = line.InvoiceLineId,
    TrackId = track.TrackId,
    TrackName = track.Name,
    TrackPrice = track.UnitPrice,
    Artist = track.Album.Artist.Name,
    UnitPrice = line.UnitPrice,
    Quantity = line.Quantity,
    Action = "None"
};

But I get an empty result:

I tried using join into newset and using from e in newset.DefaultIfEmpty() but I am joining 3 tables and join into produces the last table and cannot include all joined tables.

I appreciate any help on this.

Thanks in advance.

Do you have `Track` navigation property defined in InvoiceLines class ? — CodeNotFound, Aug 12 '16 at 13:10
Sounds like you could use `Left Join` https://msdn.microsoft.com/tr-tr/vstudio/ee908647.aspx#leftouterjoin — uTeisT, Aug 12 '16 at 13:11
@CodeNotFound, yes I have Track navigation property in InvoiceLines — superfly71, Aug 12 '16 at 13:18
@superfly71 I answer to your question by making your query more readable and easy to maintain. — CodeNotFound, Aug 12 '16 at 13:37

score 3 · Answer 1 · answered Aug 12 '16 at 13:21

3

It's simple.

Let take the original query

from inv in context.Invoices where inv.InvoiceId == invoiceID
join line in context.InvoiceLines on inv.InvoiceId equals line.InvoiceId
join track in context.Tracks on line.TrackId equals track.TrackId
...

which generates inner joins. In order to turn them into a left outer joins, all you need is to insert two lines:

from inv in context.Invoices where inv.InvoiceId == invoiceID
join line in context.InvoiceLines on inv.InvoiceId equals line.InvoiceId
into lines from line in lines.DefaultIfEmpty() // turn the above to left join
join track in context.Tracks on line.TrackId equals track.TrackId
into tracks from track in tracks.DefaultIfEmpty() // turn the above to left join
...

See Left Outer Join in the MSDN documentation.

answered Aug 12 '16 at 13:21

Ivan Stoev

195,425
15
312
343

Also make sure that when you `left join`, when accessing a property of a left joint table to check that it isn't null: `track?.SomeProperty` - otherwise you will get a `NullReferenceException` in the case that it really didn't have a record – Gilad Green Aug 12 '16 at 13:47
Use default values to avoid `NullReferenceException` – Sathish Aug 12 '16 at 13:52
@GiladGreen In LINQ to Entities it really doesn't matter - SQL never generates NRE. The only requirement is if you have a non nullable value type property to cast it to nullable. Of course `right !- null ? right.Prop : default_value` and similar constructs are supported. – Ivan Stoev Aug 12 '16 at 16:11
1

Ah cool didn't have experience with EF so didn't know that :) – Gilad Green Aug 12 '16 at 17:49

score 1 · Accepted Answer · answered Aug 12 '16 at 13:48

You can use left outer join and give default value for track model.

Track defaultTrack= new Track()
        {
            TrackId = 0,
            TrackName = "No Track",
            TrackPrice = 0,
            Artist = "No Artist",
        };

 //Left outer join
 var screenset =
 from inv in context.Invoices where inv.InvoiceId == invoiceID
 from j in context.Invoices where j.InvoiceId == invoiceID
 join line in context.InvoiceLines on j.InvoiceId equals line.InvoiceId
 join track in context.Tracks on line.TrackId equals track.TrackId into trackGroup
 from trackDetails in trackGroup.DefaultIfEmpty(defaultTrack)
 select new InvoiceAndItemsDTO
 {
   InvoiceId = inv.InvoiceId,
   InvoiceDate = inv.InvoiceDate,
   InvoiceTotal = inv.Total,
   CustomerId = inv.CustomerId,
   CustomerFullName = inv.Customer.LastName + ", " + inv.Customer.FirstName,
   CustomerPhoneNumber = inv.Customer.Phone,
   InvoiceLineId = line.InvoiceLineId,
   TrackId = trackDetails .TrackId,
   TrackName = trackDetails .Name,
   TrackPrice = trackDetails .UnitPrice,
   Artist = trackDetails .Album.Artist.Name,
   UnitPrice = line.UnitPrice,
   Quantity = line.Quantity,
   Action = "None"
 };

Thanks for the good answer! I think we will have to check InvoiceLines for null too — superfly71, Aug 13 '16 at 01:06
This answer actually is wrong. At runtime you'll get the famous EF `NotSupportedException`: "Unable to create a constant value of type 'Namespace.Track'. Only primitive types or enumeration types are supported in this context." — Ivan Stoev, Aug 13 '16 at 08:34
I have used this code main times. This is one of the way to achieve left outer join.Link - https://msdn.microsoft.com/tr-tr/vstudio/ee908647.aspx#leftouterjoin — Sathish, Aug 13 '16 at 11:26
Sadly, LINQ to Objects is one, LINQ to Entities - another. The difference is that in the later many methods are not supported. In particular, `DefaultIfEmpty(default_value)`. Anyway, I'm not going to argue, try running your code in EF and you'll see. — Ivan Stoev, Aug 13 '16 at 15:43

Linq returns null when invoice does not contains any items

2 Answers2