So I have sad surveys and fun surveys, they are recorded in their own tables. Each have audit data attached. So the surveys contain a foreign key to their record in the audit table.
I am building a web page to list all surveys that have been submitted. The survey Id, user name, and submitted date.
Coming from a MySQL/MSSQL background, I would write something like this:
SELECT s.Id, u.Name, a.SubmittedOn
FROM sadSurveys s
LEFT JOIN audits a ON s.AuditId = a.Id
LEFT JOIN users u ON s.UserId = u.Id
UNION
SELECT f.Id, u.Name, a.SubmittedOn
FROM funSurveys f
LEFT JOIN audits a ON f.AuditId = a.Id
LEFT JOIN users u ON f.UserId = t.Id
However, using EF I have this so far...
var allSurveys = _context.Audits
.Include(f => f.FunSurvey)
.ThenInclude(u => u.User)
.Include(s => s.SadSurvey)
.ThenInclude(u => u.User)
.ToList();
...but I can't figure how to map it back to a DTO because the UserId/Name is across different survey objects depending on which survey it relates to and it's getting messy which leads me to think my EF code here is not using the correct approach.
I also would rather not be getting all the columns in surveys and audits as this is a waste, returning far more data than needed.
The equivalent LINQ query using the query syntax is pretty much the same as the SQL query (taking into account the LINQ specifics):
var allSurveys = (
from s in _context.SadSurveys
join a in _context.Audits on s.AuditId equals a.Id
into s_a from a in s_a.DefaultIfEmpty() // left join
join u in _context.Users on s.UserId == u.Id
into s_u from u in s_u.DefaultIfEmpty() // left join
select new SurveyDTO { Id = s.Id, Name = u.Name, SubmittedOn = a.SubmittedOn }
).Concat(
from s in _context.FunSurveys
join a in _context.Audits on s.AuditId equals a.Id
into s_a from a in s_a.DefaultIfEmpty() // left join
join u in _context.Users on s.UserId == u.Id
into s_u from u in s_u.DefaultIfEmpty() // left join
select new SurveyDTO { Id = s.Id, Name = u.Name, SubmittedOn = a.SubmittedOn }
).ToList();
However, ORM and navigation properties allow you to achieve the same result with much simpler LINQ query:
var allSurveys = (
from s in _context.SadSurveys
select new SurveyDTO { Id = s.Id, Name = s.User.Name, SubmittedOn = s.Audit.SubmittedOn }
).Concat(
from s in _context.FunSurveys
select new SurveyDTO { Id = s.Id, Name = s.User.Name, SubmittedOn = s.Audit.SubmittedOn })
).ToList();