How to fetch entries from multiple tables in Entity framework?

Question

In my Application I need to join data from two tables and display them as a single list .The list needs to be sorted based on date (Date is available in both tabled) and it should support pagination. These two tables are not related to each other. The only thing they have in common is the date. How can we do it?

Union? Or fetch them seperately and append them to a single collection on the client? https://learn.microsoft.com/en-us/dotnet/api/system.linq.queryable.union?view=netcore-3.1 — David Browne - Microsoft, May 20 '20 at 12:38

score 0 · Accepted Answer · edited Jun 16 '22 at 04:18

Let's say you have classes which represents 2 tables: A and B

public class A
{
    public DateTime Date { get; set; }
    public string PropA { get; set; }
}

public class B 
{
    public DateTime Date { get; set; }
    public string PropA { get; set; }
}

If you want to display them together you will need a model that has all of theirs properties like this:

public class ResultDto 
{
    public DateTime Date { get; set; }
    public string PropA { get; set; }
    public string PropB { get; set; }
}

Then to get them together, and order by date, a query on the dbContext would look like this:

var result = _dbContext
                .A.Select(a => new ResultDto()
                {
                    PropA = a.PropA,
                    Date = a.Date,
                    PropB = null
                }).Concat(_dbContext.B.Select(b => new ResultDto()
                {
                    PropA = null,
                    Date = b.Date,
                    PropB = b.PropB
                }))
                .OrderByDescending(r => r.Date)
                .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)

Also here is a similar question that I answered today: Join and sort 2 not related tables

How to fetch entries from multiple tables in Entity framework?

1 Answers1