C# Linq UNION syntax on 2 complex queries

Question

This is pretty complicated for a person just learning Linq! I asked how to JOIN and GROUP BY in this question and a BIG SHOUT OUT to TriV for his explicit and very helpful answer.

Now, I want to take my already very complicated query and create a UNION to a similar query. I have below, 2 separate SQL queries that I have put into 1 query using a UNION clause.

The first query joins two tables. But the second query is against only one table, because it is for unassigned assets that are not linked to an organization in the organization table. The first query will return a separate row for each organization. The second query will return only one row for all unassigned assets.

The entire SQL query looks like this:

SELECT  o.org_hq_name,
        o.org_command_name,
        o.org_region_name,
        o.org_installation_name,
        o.org_site_name,
        o.org_subsite_name,
        o.org_hq_id,
        o.org_command_id,
        o.org_region_id,
        o.org_installation_id,
        o.org_site_id,
        count(org_site_id) AS count
FROM    organization o, asset a
WHERE   o.org_hq_id = a.hq_org_id
AND     o.org_command_id = a.command_org_id
AND     o.org_region_id = a.region_org_id
AND     o.org_installation_id = a.installation_org_id
AND     o.org_site_id = a.site_org_id
GROUP BY o.org_hq_name,
        o.org_command_name,
        o.org_region_name,
        o.org_installation_name,
        o.org_site_name,
        o.org_subsite_name,
        o.org_hq_id,
        o.org_command_id,
        o.org_region_id,
        o.org_installation_id,
        o.org_site_id

UNION ALL

SELECT  'Unassigned',
        'Unassigned',
        'Unassigned',
        'Unassigned',
        'Unassigned',
        a.hq_org_id,
        a.command_org_id,
        a.region_org_id,
        a.installation_org_id,
        a.site_org_id,
        count(org_site_id) AS count
FROM    asset a
WHERE   a.unassigned = 1
GROUP BY a.hq_org_id,
        a.command_org_id,
        a.region_org_id,
        a.installation_org_id,
        a.site_org_id

---

I have below, 2 separate Linq queries that I want to put into 1 Linq query using a UNION clause. I believe the code below only needs a little tweeking to get it right. I've tried using examples from other questions but I still cannot figure this out.

var join1 =  from a in context.asset
         join o in context.organization
         on new {hqID = a.hq_org_id, commandID = a.command_org_id, regionID = a.region_org_id, installationID = a.installation_org_id, siteID = a.site_org_id}
         equals new {hqID = o.hq_id, commandID = o.command_id, regionID = o.region_id, installationID = o.installation_id, siteID = o.site_id}
         group new {a,o} by new {   o.org_hq_name,
                                    o.org_command_name,
                                    o.org_region_name,
                                    o.org_installation_name,
                                    o.org_site_name,
                                    o.org_hq_id,
                                    o.org_command_id,
                                    o.org_region_id,
                                    o.org_installation_id,
                                    o.org_site_id
                                } into gr1
         select new
         {
            org_hq_name = gr1.Key.org_hq_name,
            org_command_name = gr1.Key.org_command_name,
            org_region_name = gr1.Key.org_region_name,
            org_installation_name = gr1.Key.org_installation_name,
            org_site_name = gr1.Key.org_site_name,
            org_hq_id = gr1.Key.org_hq_id,
            org_command_id = gr1.Key.org_command_id,
            org_region_id = gr1.Key.org_region_id,
            org_installation_id = gr1.Key.org_installation_id,
            org_site_id = gr1.Key.org_site_id,
            Count = gr1.Count()
         };


UNION


var join2 =  from a in context.asset
         where (a.hq_org_id == 0)
         group m by new {  a.hq_org_id,
                           a.command_org_id,
                           a.region_org_id,
                           a.installation_org_id,
                           a.site_org_id
                        } into gr2
         select new
         {
            org_hq_name = "unknown",
            org_command_name = "unknown",
            org_region_name = "unknown",
            org_installation_name = "unknown",
            org_site_name = "unknown",
            org_hq_id = gr2.Key.org_hq_id,
            org_command_id = gr2.Key.org_command_id,
            org_region_id = gr2.Key.org_region_id,
            org_installation_id = gr2.Key.org_installation_id,
            org_site_id = gr2.Key.org_site_id,
            Count = gr2.Count()
         };

Thank you in advance for Helping. I am sure the answer will be helpful to others who are struggling with complex queries like this one.

Answer 1

    class Pet
{
    public string Name { get; set; }
    public int Age { get; set; }
}

// This method creates and returns an array of Pet objects.
static Pet[] GetCats()
{
    Pet[] cats = { new Pet { Name="Barley", Age=8 },
                   new Pet { Name="Boots", Age=4 },
                   new Pet { Name="Whiskers", Age=1 } };
    return cats;
}

// This method creates and returns an array of Pet objects.
static Pet[] GetDogs()
{
    Pet[] dogs = { new Pet { Name="Bounder", Age=3 },
                   new Pet { Name="Snoopy", Age=14 },
                   new Pet { Name="Fido", Age=9 } };
    return dogs;
}

public static void ConcatEx1()
{
    Pet[] cats = GetCats();
    Pet[] dogs = GetDogs();

    // Concatenate a collection of cat names to a
    // collection of dog names by using Concat().
    IEnumerable<string> query =
        cats.AsQueryable()
        .Select(cat => cat.Name)
        .Concat(dogs.Select(dog => dog.Name));

    foreach (string name in query)
        Console.WriteLine(name);
}

// This code produces the following output:
//
// Barley
// Boots
// Whiskers
// Bounder
// Snoopy
// Fido