C # Linq UNION syntax for 2 complex queries

Question

This is pretty tricky for someone just learning Linq! I asked how to join the group with this question and BIG SHOUT OUT in TriV for his explicit and very useful answer.

Now I want to take my already very complex query and create a UNION for a similar query. I have below two separate SQL queries that I put in 1 query using the UNION clause.

The first query joins two tables. But the second query refers to only one table, because it is for unassigned assets that are not related to the organization in the organization table. The first query returns a separate row for each organization. The second query returns only one row for all unassigned assets.

The entire SQL query is as follows:

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 two separate Linq queries that I want to put in 1 Linq query using the UNION clause. I believe that the code below only needs a little tweeking so that everything is correct. I tried to use examples from other questions, but I still can't figure it 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()
         };

Thanks in advance for your help. I am sure that the answer will be useful to others who are struggling with complex queries such as 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

Hide result