How to properly create a union from two tables selecting only specifc columns where one column is going to empty with some text

Question

I am trying to translate SQL code that I wrote with a union into linq code in C#, but I don't understand what I am doing wrong.

I have two tables one is tTask and another one is tTask_Users.

I tried joinning them together by using a model, because I thought it was giving me errors because the data didn't have same number amount of columns.

Here is my SQL code:

SELECT PK_Task, TaskName, Enabled, FK_TaskTeam, 'No Owner' AS Owners
FROM tTask
WHERE hasOwner = 0 
  UNION
SELECT PK_Task, TaskName, Enabled, FK_TaskTeam, Login
FROM tTask
INNER JOIN tTask_User ON tTask.PK_Task = tTask_User.FK_Task
INNER JOIN tOfficeUsers on tTask_User.FK_OfficeUser = tOfficeUsers.PK_OfficeUser
WHERE FK_Taskrecipienttype = 5 AND Enabled = 1

Here is what I tried

private OfficeEntities db = new OfficeEntities();

//GET: api/MainGrid
public IQueryable<MainGridDto> GetGridModelDtos()
{
    var query2 = db.tTask_User.Where(task =>
        task.tTaskRecipientType.pk_taskrecipienttype == 5
    ); 
    var query = from items in query2                            
                select new MainGridDto()
                {
                    PK_Task = items.FK_Task,
                    TaskName = items.tTask.TaskName,
                    TaskRecipientType = items.tTaskRecipientType,
                    Owner = items.tOfficeUser.Login,
                    TaskDescription = items.tTask.TaskDescription,
                    Enabled = items.tTask.Enabled,
                    tTaskTeam = items.tTask.tTaskTeam,
                    EmailBody = items.tTask.EmailBody,
                    EmailSubject = items.tTask.EmailSubject
                };

    var taskquery = db.tTasks.Where(items =>
        items.hasOwner == 0
    );
    var querytask = from items in taskquery
                    select new MainGridDto()
                    {
                        PK_Task = items.PK_Task,
                        TaskName = items.TaskName,
                        TaskRecipientType = null,
                        Owner = "no owner",
                        TaskDescription = items.TaskDescription,
                        Enabled = items.Enabled,
                        tTaskTeam = items.tTaskTeam,
                        EmailBody = items.EmailBody,
                        EmailSubject = items.EmailSubject
                    };

    query2.Union(querytask);
    query.Union(taskquery);
}

Here are the errors I am getting:

Severity    Code    Description Project File    Line    Suppression State
Error   CS1929  'IQueryable<tTask_User>' does not contain a definition for 'Union' and the best extension method overload 'ParallelEnumerable.Union<MainGridDto>(ParallelQuery<MainGridDto>, IEnumerable<MainGridDto>)' requires a receiver of type 'ParallelQuery<MainGridDto>'    ReportingWebAPI \source\repos\ReportingWebAPI\ReportingWebAPI\Controllers\MainGridController.cs 56  Active

Severity    Code    Description Project File    Line    Suppression State
Error   CS1929  'IQueryable<MainGridDto>' does not contain a definition for 'Union' and the best extension method overload 'ParallelEnumerable.Union<tTask>(ParallelQuery<tTask>, IEnumerable<tTask>)' requires a receiver of type 'ParallelQuery<tTask>'   ReportingWebAPI \repos\ReportingWebAPI\ReportingWebAPI\Controllers\MainGridController.cs    57  Active

Answer 1

Just one query is enough. Notice the change in Where and in the Owner property.

var query2 = db.tTask_User.Where(task => task.hasOwner == 0 || (task.Enabled && task.tTaskRecipientType.pk_taskrecipienttype == 5)); 

var query = from items in query2                            
            select new MainGridDto()
            {
                 PK_Task = items.FK_Task,
                 TaskName = items.tTask.TaskName,
                 TaskRecipientType = items.tTaskRecipientType,
                 Owner = items.hasOwner == 0 ? "no owner" : items.tOfficeUser.Login,
                 TaskDescription = items.tTask.TaskDescription,
                 Enabled = items.tTask.Enabled,
                 tTaskTeam = items.tTask.tTaskTeam,
                 EmailBody = items.tTask.EmailBody,
                 EmailSubject = items.tTask.EmailSubject
            };

Answer 2

I could be wrong, but it looks like an issue with types. The general rule is:

1. both types should have exactly the same property names
2. both types should have exactly the same property types (int vs a smallint or double vs a decimal will cause a build error)

Looks like query2 is type of IQueryable<tTask_User> and querytask is type of IQueryable<MainGridDto> that's why you can't do a union here:

query2.Union(querytask);

Answer 3

Following my SQL to LINQ Recipe, first you do the two sub-queries from SQL:

var query1 = from items in db.tTasks
             where items.hasOwner == 0
             select new MainGridDto() {
                 PK_Task = items.PK_Task,
                 TaskName = items.TaskName,
                 TaskRecipientType = null,
                 Owner = "no owner",
                 TaskDescription = items.TaskDescription,
                 Enabled = items.Enabled,
                 tTaskTeam = items.tTaskTeam,
                 EmailBody = items.EmailBody,
                 EmailSubject = items.EmailSubject
             };
var query2 = from items in db.tTask_User
             where items.tTaskRecipientType.pk_taskrecipienttype == 5 && items.tTask.Enabled
             select new MainGridDto() {
                 PK_Task = items.FK_Task,
                 TaskName = items.tTask.TaskName,
                 TaskRecipientType = items.tTaskRecipientType,
                 Owner = items.tOfficeUser.Login,
                 TaskDescription = items.tTask.TaskDescription,
                 Enabled = items.tTask.Enabled,
                 tTaskTeam = items.tTask.tTaskTeam,
                 EmailBody = items.tTask.EmailBody,
                 EmailSubject = items.tTask.EmailSubject
             };

Then you combine the sub-queries for the final answer:

var ans = query1.Union(query2);