Linq to Entities Custom Order Through Item Mapping Table

Question

I have a news table, and I would like to implement a custom order. I did this before using a positional matching table that has news and position.

Then I LEFT OUTER join the table of positions ON news.newsId = position.itemId with the case case statement

CASE WHEN [position] IS NULL THEN 9999 ELSE [position] END

and order by asc position, articleDate desc.

Now I'm trying to do the same with Linq for Entities. I set up my tables with PK, FK relationships so that my News object has a collective position position.

Now comes a bit that I can’t solve. How to implement LEFT OUTER JOIN.

I still:

 var query = SelectMany (n => n.Positions, (n, s) => new { n, s })
                    .OrderBy(x => x.s.position)
                    .ThenByDescending(x => x.n.articleDate)
                    .Select(x => x.n);

This view works. However, this uses an INNER JOIN, not what I want.

I had another idea:

 ret = ret.OrderBy(n => n.Positions.Select(s => s.position));

DbSortClause , .

ret = ret.GroupJoin(tse.Positions, n => n.id, s => s.itemId, (n, s) => new { n, s })
                    .OrderBy(x => x.s.Select(z => z.position))
                    .ThenByDescending(x => x.n.articleDate)
                    .Select(x => x.n);

!

- , !

UPDATE:
.

ret = ret.GroupJoin(entity.Positions, n => n.id, s => s.itemId, (n, s) => new { n, s })
                    .SelectMany(x => x.n.Positions.DefaultIfEmpty(), (n, s) => new { n, s })
                    .OrderBy(x => x.s.position)
                    .ThenByDescending(x => x.n.n.articleDate)
                    .Select(x => x.n.n);

. positionid articleType.

1 , , ( ), linq ?

where, , . select, SQL:

CASE WHEN [position] IS NULL OR shuffleId != 1 THEN 9999 ELSE [position] END

, , , . , - ?

Answer 1

:

var query =
   from n in news
   let p = n.Positions.Select(p=>p.Position).FirstOrDefault() ?? 9999
   orderby p, n.ArticleDate
   select n;

Ps. , 1 ... , .

---

OP :

ret = ret
      .GroupJoin(tse.Positions, n => n.id, s => s.itemId, (n, s) => new { n, s }) 
      .SelectMany(x => x.s.DefaultIfEmpty(), (n, s) => new { n, s }) 
      .OrderBy(x => x.s.position == null || x.s.positionId != positionId ? 9999 : x.s.position) 
      .ThenByDescending(x => x.n.n.articleDate) 
      .Select(x => x.n.n);

Answer 2

, , - - Linq-To-SQL, DataContext . , .

, , .

.