LINQ left join ordered with empty right-items at the end

Question

My understanding of LINQ and Entity Framework is minimal, and I'm learning as I go along...

I am trying to write a query that takes the information from a view called GroupView and does a left-join on a table called GroupSequence... the information is then to be used by an <asp:Repeater>.

The resultant set should have all the items from GroupView with the joined items at the start (in sequence defined by the GroupSequence table) and with non-joined items at the end (in sequence defined by the Id of the GroupView items).

I.e...

[GroupView]             |    [GroupSequence]
[Id]  [Name]   [Calc]   |    [Id]  [GroupId]  [UserId]  [Sequence]
1     Group 1  23       |    1     1          1         3
2     Group 2  34       |    2     2          1         2
3     Group 3  45       |    3     3          1         1
4     Group 4  56
5     Group 5  67

With the expected outcome of...

[Id]  [Name]   [Calc]
3     Group 3  45
2     Group 2  34
1     Group 1  23
4     Group 4  56
5     Group 5  67

If I do the following, despite using the DefaultIfEmpty, all I get is the 3 groups linked to the sequence. But the page displays, even though it's only 3 rows...

from @group in context.GroupViews
join seq in context.GroupSequences on @group.Id equals seq.GroupId into groupSeq
from item in groupSeq.DefaultIfEmpty()
where item.UserId == 1
orderby item.Sequence
select new { Id = @group.Id, Name = @group.Name, Calc = @group.Calc };

If I do the following, the .DataBind on the repeater complains that...

The entity or complex type 'DevModel.GroupSequence' cannot be constructed in a LINQ to Entities query

from @group in context.GroupViews
join seq in context.GroupSequences on @group.Id equals seq.GroupId into groupSeq
from item in groupSeq.DefaultIfEmpty(new GroupSequence { Id = @group.Id, UserId = 1, Sequence = @group.Id + 1000 })
where item.UserId == 1
orderby item.Sequence
select new { Id = @group.Id, Name = @group.Name, Calc = @group.Calc };

Based on this question and accepted answer I have also tried using a DTO like this...

class GroupViewSequenceDTO
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? Calc { get; set; }
}

from @group in context.GroupViews
join seq in context.GroupSequences on @group.Id equals seq.GroupId into groupSeq
from item in groupSeq.DefaultIfEmpty(new GroupSequence { Id = @group.Id, UserId = 1, Sequence = @group.Id + 1000 })
where item.UserId == 1
orderby item.Sequence
select new GroupViewSequenceDTO { Id = @group.Id, Name = @group.Name, Calc = @group.Calc };

But I still get the same error as before (cannot be constructed)

The question...

How can I write this query so that the repeater will show all 5 rows, with the first 3 in sequence order, and the last 2 added on? What am I doing wrong?

Magnus · Accepted Answer · 2015-03-10T10:37:57.813

1

You need to move the filter into the left join condition since it will be false when item is null.

from @group in context.GroupViews
from seq in context.GroupSequences.Where(x => @group.Id == x.GroupId && x.UserId == 1).DefaultIfEmpty()
orderby seq.Sequence ?? int.MaxValue
select new GroupViewSequenceDTO 
{
   Id = @group.Id, 
   Name = @group.Name, 
   Calc = @group.Calc 
};

edited Mar 10 '15 at 10:37

answered Mar 10 '15 at 10:32

Magnus

45,362
8
80
118

Thanks @Magnus - minor typo (`item.UserId` should be `x.UserId`). I'm getting all 5 rows through, but the two null items are at the start of the list – freefaller Mar 10 '15 at 10:36
Then order the items `descending` instead. – Magnus Mar 10 '15 at 10:37
But that would have the first 3 items in descending order (sorry, I'm being of a newbie with all this... I'm sure I could work it eventually, I'm just struggling to get my head into a totally different way of coding after a decade of writing data handlers by hand) – freefaller Mar 10 '15 at 10:38
2

If Sequence is if type `int` you can for example set it to `int.MaxValue` when it is `null`. – Magnus Mar 10 '15 at 10:43
Perfect, perfect, perfect... I'd managed to figure something similar out myself (`(seq.Sequence != null ? seq.Sequence : @group.Id + 1000)`), but that is exactly what I needed - thanks Magnus :-) – freefaller Mar 10 '15 at 10:44
1

Or in general you could try `orderby new { ISN = seq.Sequence == null ? 1 : 0, seq.Sequence }` – xanatos Mar 10 '15 at 10:44

LINQ left join ordered with empty right-items at the end

1 Answers1