Making a UNION query more efficient in LINQ

Question

I am currently working on a project leveraging EF and I am wondering if there is a more efficient or cleaner way to handle what I have below.

In SQL Server I could get the data I want by doing something like this:

SELECT tbl2.* FROM 
dbo.Table1 tbl
INNER JOIN dbo.Table2 tbl2 ON tbl.Column = tbls2.Colunm
WHERE tbl.Column2 IS NULL 

UNION

SELECT * FROM 
dbo.Table2
WHERE Column2 = value 

Very straight forward. However in LINQ I have something that looks like this:

var results1 = Repository.Select<Table>()
            .Include(t => t.Table2)
            .Where(t => t.Column == null);

var table2Results = results1.Select(t => t.Table2);
var results2 = Repository.Select<Table2>().Where(t => t.Column2 == "VALUE");

table2Results  = table2Results.Concat(results2);

return results2.ToList();

First and foremost the return type of the method that contains this code is of type IEnumerable< Table2 > so first I get back all of the Table2 associations where a column in Table1 is null. I then have to select out my Table2 records so that I have a variable that is of type IEnumerable. The rest of the code is fairly straightforward in what it does.

This seems awfully chatty to me and, I think, there is a better way to do what I am trying to achieve. The produced SQL isn't terrible (I've omitted the column list for readability)

SELECT 
[UnionAll1].*
FROM  (SELECT 
[Extent2].*
FROM  [dbo].[Table1] AS [Extent1]
INNER JOIN [dbo].[Table2] AS [Extent2] ON [Extent1].[Column] = [Extent2].[Column]
WHERE [Extent1].[Column2] IS NULL
UNION ALL
SELECT 
[Extent3].*
FROM [dbo].[Table2] AS [Extent3]
WHERE VALUE = [Extent3].[Column]) AS [UnionAll1]

So is there a cleaner / more efficient way to do what I have described? Thanks!

Answer 1

Well, one problem is that your results may not return the same data as your original SQL query. Union will select distinct values, Union All will select all values. First, I think your code could be made a lot clearer like so:

// Notice the lack of "Include". "Include" only states what should be returned
// *with* the original type, and is not necessary if you only need to select the
// individual property.
var firstResults = Repository.Select<Table>()
                             .Where(t => t.Column == null)
                             .Select(t => t.Table2);

var secondResults = Repository.Select<Table2>()
                              .Where(t => t.Column2 == "Value");

return firstResults.Union(secondResults);

If you know that it's impossible to have duplicates in this query, use Concat instead on the last line (which will produce the UNION ALL that you see in your current code) for reasons described in more detail here. If you want something similar to the original query, continue to use Union like in the example above.

It's important to remember that LINQ-to-Entities is not always going to be able to produce the SQL that you desire, since it has to handle so many cases in a generic fashion. The benefit of using EF is that it makes your code a lot more expressive, clearer, strongly typed, etc. so you should favor readability first. Then, if you actually see a performance problem when profiling, then you might want to consider alternate ways to query for the data. If you profile the two queries first, then you might not even care about the answer to this question.