Linq and nullable key relationships affecting UNION operation

Question

Here is an example:

Lets say I have 3 tables, Countries, People and Cities. City records have a non-nullable foreign key field identifying a country. People records have a nullable foreign key field identifying a country - they may be from an Eastern European country that no longer exists.

I want to create a combined list of countries from a list of people and a list of cities:

var people = dbContext.People.Where(...);
var cities = dbContext.Cities.Where(...);

var countries = cities.Select(c=>c.Country);
countries = countries.Union(people.Select(p=>p.Country));

The problem comes from that last line. Since not all people records have a matching country record, LINQ is (correctly) creating a query that ensures there will be a row filled with nulls for every countryless person. With a debugger it appears this is done by creating an entra dummy column call "[test]"

SELECT [t2].[test], [t2].[CountryID], [t2].[Name]
FROM [dbo].[People] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[CountryID], [t1].[Name]
    FROM [dbo].[Countries] AS [t1]
    ) AS [t2] ON [t2].[CountryID] = [t0].[CountryID]

While that extra column [test] is silently removed (the result is identified as IQueryable) on the code side, on the SQL side it is most definitely there, and is resulting in the query being rejected when I union it with a normal Country selection statement.

In the final case I don't want or need the extra dummy rows - in the full program I've already included people.Where(p=>p.CountryID != null).Select(p=>p.Country) as well as trying p=>Country != null.

However Linq does not recognize that this will prevent the null rows and so still inserts the test column. as the test column is invisible, I have no obvious way of "removing" it from what is otherwise reported as an IQueryable object. The end result is the run time error about my UNION construct having an unequal number of columns.

How can I force an INNER JOIN on on my nullable relationship, or otherwise make the union work as I want to by excluding the invisible test column?

Answer 1

I know that ideally you'd use the relationships in the mapping, but this might be a good work-around.

var people = dbContext.People.Where(...);
var cities = dbContext.Cities.Where(...);

var countryIds =
  cities
    .Select(c => c.CountryID)
  .Union(people
    .Select(p => p.CountryID)
    .Where(cID => cID.HasValue)
    .Select(cID => cID.Value));

var countries = dbContext.Countries
  .Where(c => countryIds.Contains(c.CountryID));