CODEWITHSUNDEEP
c#entity-frameworkentity-framework-core
Piotrek
Piotrek

Reputation: 1416

Join and sort 2 not related tables

I have 2 entities: Post and Article and I need to select both of them at once and sort by common property. I'm searching for some "join" possibilities, but all I'm finding are examples ending with something like ...where a.id == b.a_id which is not relevant in my case.

That's my models

class BaseEntity {
    public int Id;
    public DateTime AddedDate;
}

class Post extends BaseEntity {
    public string Title;
}

class Article extends BaseEntity {
    public string Name;
}

For that data:

Articles:
Id | Name | AddedDate
 1 | AAA  | 15/05/2020 18:00:00
 2 | BBB  | 17/05/2020 18:00:00
 3 | CCC  | 19/05/2020 18:00:00

Posts:
Id | Title | AddedDate
 1 | DDD   | 16/05/2020 18:00:00
 2 | EEE   | 18/05/2020 18:00:00
 3 | FFF   | 20/05/2020 18:00:00

when sorting by date DESC and limit it to 4 i need to end up with something like that, or something similar:

Article Id | PostId | Name  | Title | AddedDate
NULL       | 3      | NULL  | FFF   | 20/05/2020 18:00:00
3          | NULL   | CCC   | NULL  | 19/05/2020 18:00:00
NULL       | 2      | NULL  | EEE   | 18/05/2020 18:00:00
2          | NULL   | BBB   | NULL  | 17/05/2020 18:00:00

Could someone advice me somehow? I need to do this in EF Core.

I could construct whole list similarly to this and manipulate on this, but this is very far from being optimal:

var results = (new List<BaseEntity>()).Concat(_context.Posts.ToList());
results = results.Concat(_context.Articles.ToList()).ToList();

//order
//take(x)

Upvotes: 0

Views: 166

Answers (1)

Jakub Kozera
Jakub Kozera

Reputation: 3473

To achieve the result you need you will have to project both Post and Article to a shared typed, so first I created a ResultDto - which is representation of the type that you want to get:

public class ResultDto
{
    public int? ArticleId { get; set; }
    public int? PostId { get; set; }
    public string Name { get; set; }
    public string Title { get; set; }
    public DateTime AddedDate { get; set; }
}

Then you have to project Articles to ResultDto, as well as Post to ResultDto, concat the results and sort like this:

var result = _dbContext
            .Articles.Select(a => new ResultDto()
            {
                ArticleId = a.Id,
                AddedDate = a.AddedDate,
                Name = a.Name,
                PostId = null,
                Title = null
            }).Concat(_dbContext.Posts.Select(p => new ResultDto()
            {
                PostId = p.Id,
                AddedDate = p.AddedDate,
                Title = p.Title,
                ArticleId = null,
                Name = null
            }))
            .OrderByDescending(r => r.AddedDate)
            .ToList();

Btw since this Select is invoked on an IQueryable, you will have to select all of the properties (even if they would be NULL by default).

This results in following SQL query:

SELECT [t].[ArticleId], [t].[AddedDate], [t].[Name], [t].[PostId], [t].[Title]
FROM (
          SELECT [a].[Id] AS [ArticleId], [a].[AddedDate], [a].[Name], NULL AS [PostId], NULL AS [Title]
          FROM [Articles] AS [a]
          UNION ALL
          SELECT NULL AS [ArticleId], [p].[AddedDate], NULL AS [Name], [p].[Id] AS [PostId], [p].[Title]
          FROM [Posts] AS [p]
      ) AS [t]
ORDER BY [t].[AddedDate] DESC

Upvotes: 2

Related Questions