I have two tables, point_transactions which shows how users got and spent their in-app points, and wallet_transactions which shows how users got and spent their wallet money (real money). These two tables do not have direct relation with each other. They both have a created_on column which shows when they were created. I need to create a table that shows history of a user's transactions (both point and wallet). This table is sorted based on the creation time of the transaction and has paging, which means it's better to get paged result from database rather than loading all data into memory.
The following query gives me what I want:
select *,
case
when pt.id is null then wt.created_on
else pt.created_on
end as tx_created_on
from point_transactions as pt
full outer join wallet_transactions as wt on false
order by tx_created_on desc
Is there any way I can get this with EF Core?
