CODEWITHSUNDEEP
linqentity-framework
tschmit
tschmit

Reputation: 7800

EF: Is there way to force Linq using UNION instead of UNION ALL

I use Linq with entity-framework.

GetSet1<T>().Union(GetSet2<T>())

The GetSetX returns IQueryable.

The generated SQL is an UNION ALL. But I know a UNION is the good way to achieve my goal. Actually my workaround is:

GetSet1<T>().Union(GetSet2<T>()).Distinct()

in this case the generated sql is like :

select distinct Field...
from (
    select distinct Field...
    union all
    select distinct Field...
) unionall

I know (because that is the way is was thinked) that 

select Field...
union
select Field...

is the best query. So Is there a way (I can(t actually found) to have EntityFramework use UNION and not UNION ALL ?

==========

1: Adding the <T> for more precision

Upvotes: 5

Views: 8038

Answers (1)

Carvellis
Carvellis

Reputation: 4052

The Union() method should return the result without duplicates. Depending on the query, EF will generate either UNION ALL with DISTINCT or just UNION, so your explicit Distinct() is not necessary. The Linq equivalent of UNION ALL is Concat().

Besides that, EF has a history of generating inefficient SQL for queries. If you can, you should use EF 5.0 which has greatly improved query SQL generation.

Upvotes: 14

Related Questions