Improving the efficiency of a UNION query in LINQ
I am currently working on a project using EF and I am wondering if there is a more efficient or cleaner way to handle what I have.
In SQL Server, I could get the data I wanted 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 similar to 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 of all, the return type of the method that contains this code is of type IEnumerable <Table2> so first I return all the associations of Table2 where the column in table1 is NULL. Then I have to select my records in Table2 so that I have a variable of type IEnumerable. The rest of the code is pretty simple in what it does.
This seems terribly chatty to me and I think there is a better way to do what I am trying to achieve. The SQL produced is not terrible (I skipped 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 described? Thank!
source to share
Well, one problem is that your results may not return the same data as the original SQL query. Union
will select different values, Union All
will select all values. First, I think your code could be made much clearer:
// 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 it is not possible to duplicate in this query, use Concat
instead on the last line (which will result in the appearance Union All
you see in the current code) for reasons explained in more detail here . If you want something similar to the original query, continue using Union
as in the example above.
It's important to remember that LINQ-to-Entities may not always be able to produce the SQL you want, as it has to handle so many cases in a generic way. The advantage of using EF is that it makes your code much more expressive, crisp, strongly typed, etc., so you must consider readability first. Then, if you do see a performance issue when profiling, you can consider alternative ways to query the data. If you ask two queries first, then you might not even like the answer to this question.
source to share