How can I join datasets from multiple tables with only certain columns in EF Core?

Question

How can I join datasets from multiple tables with only certain columns in EF Core?

c# ef-core-2.0 entity-framework entity-framework-core linq

Question

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.

egmfrs

View more on Stack Overflow

Ivan Stoev · Accepted Answer · 4/26/2018 2:26:34 AM

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();

How can I join datasets from multiple tables with only certain columns in EF Core?

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related