How to select different fields using .Concat for Union All query

Question

I’m doing some preliminary work getting data consumption ready from a webAPI controller in MVC. I’m using a linq query that mimics a union query that uses two joins. However, I need the select statement to ask for a different field on each side of the union. This works fine in SSMS when I run the query, but when I create the query in linq it will not allow the fields to be different. It gives an error saying that each anonymous type requires a receiver of the same type. This is referring to the two anonymous types created via select new { }… Here’s the two queries.

In SQL:

Select m.Last_Name, first_name, m.dc_number, Dept_Job as JobOrStatus  FROM 
Master_Roster m 
INNER JOIN Class_Assignment a on m.dc_number = a.dc_number 
where a.subject_am = 'y1'
AND Start_Date_AM <= '1/31/18'

UNION ALL

Select m.Last_Name, first_name, m.dc_number, status_am  FROM Master_Roster m 
INNER JOIN Attend_am_y1 at on m.dc_number = at.dc_number 
where at.class_date_am >= '1/1/18'
AND at.class_date_am <= '1/31/18' 
ORDER BY Last_Name

In linq:

(from m in db.Master_Roster
join c in db.Class_Assignment
on m.dc_number equals c.dc_number
orderby m.Last_Name
where c.Subject_AM == "y1"
select new { m.dc_number, m.Last_Name, m.First_Name }).Concat(
from m in db.Master_Roster
join a in db.attend_am_y1 on m.dc_number equals a.dc_number
orderby m.Last_Name
where a.class_date_am >= date1 && 
a.class_date_am <= date2
select new { m.dc_number, m.Last_Name, m.First_Name });

If I were to add dept_job and status_am to the linq query, it throws aforementioned error. I’d like to do this without using a stored procedure. Any ideas?

Answer 1

Instead of putting into 2 anonymous types, put into the same viewmodel and concat. Eg:

Internal Class ConcatViewModel{
  public int DcNumber {get;set;}
  public string LastName {get;set;)
  // etc...
}

Then:

select new ConcatViewModel{ 
  DcNumber = m.dc_number,
  LastName = m.Last_Name,
  FirstName = m.First_Name,
  Status = m.Dept_Job
}

and

select new ConcatViewModel{ 
  DcNumber = m.dc_number,
  LastName = m.Last_Name,
  FirstName = m.First_Name,
  Status = m.status_am
}

(assuming that status_am and Dept_Job are the same underlying type)