Linq union all equivalent of sql code

Question

I have the following queries:

var majorClients = maj in dbContext.MajorClients
                   where (maj.startdate > startDate)
                   where (maj.status == "Active")
                   Select new Client{EntityPK = maj.mjPrimaryKey,Name = maj.name, Type = "Maj"};

var minorClients = min in dbContext.MinorClients
                   where (min.startdate > startDate)
                   where (min.status == "Active" || min.status== "Inactive")
                   Select new Client{EntityPK = min.mnPrimaryKey,Name = min.name, Type = "Min"};

There are clients that could appear in both major and minor tables. I would like to return a list of all occurrences of clients in both tables, however if there are matching clients by name, then I would only want to return the matching record from the majorClients table.

I have written a sql query to return the results:

SELECT mjPrimaryKey AS EntityPK,name,'Maj' AS TYPE 
FROM majorClients 
WHERE status = 'Active' AND startDate > @startDate
UNION ALL
SELECT mnPrimaryKey,name,'Min' FROM minorClients
WHERE status IN ('Active','Inactive') AND startDate > @startDate
WHERE name NOT IN (SELECT name FROM majorClients WHERE status = 'Active' AND startDate > @startDate)

How would I represent this query in linq?

alexmac · Accepted Answer · 2014-03-25T06:44:22.480

Try this linq. To exclude duplicates from minorClients, I've used Contains method. To union all objects - Union method:

var majorClients = from maj in dbContext.MajorClients
                   where maj.startdate > startDate
                        && maj.status == "Active"
                   select new Client
                    {
                        EntityPK = maj.mjPrimaryKey,
                        Name = maj.name, 
                        Type = "Maj"
                    };

var minorClients = from min in dbContext.MinorClients
                   where min.startdate > startDate
                        && min.status == "Active" || min.status== "Inactive"
                        && !(from maj in dbContext.MajorClients
                             where maj.startdate > startDate
                                && maj.status == "Active"
                             select maj.name).Contains(min.Name)
                   select new Client
                        {
                            EntityPK = min.mnPrimaryKey,
                            Name = min.name, 
                            Type = "Min"
                        };

var allClients = majorClients.Union(minorClients);

Thank you for your response. When I run your code I get the following error: The specified type member 'Name' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. I think it is indicating that "Name" in the new Client object is not related to the database equivalent Name. — breakerdotnet, Mar 25 '14 at 06:40
Yes, i forget about it. I changed answer, please check again. — alexmac, Mar 25 '14 at 06:45
Thank you it works. A pity that the query is run twice though, I do understand that the sql version runs twice as well. In this example I removed the other where conditions for simplicity. Adding those 5 or so additional conditions does make it a bit harder to read. Regardless, Thank you for the solution. — breakerdotnet, Mar 25 '14 at 07:05

Linq union all equivalent of sql code

1 Answers1