Query: Translate IQueryable.Concat/Union/Intersect/Except/etc. to server

[simpleway2016]

EF version:1.0.1.0

var q1 = from m in db.Table1
                     select new
                     {
                         name = m.Name,
                     };
            var q2 = from m in db.Table2
                     select new
                     {
                         name = m.Name,
                     };
            q1 = q1.Concat(q2);
            var result = q1.ToArray();

I watch the sql string by ILogger, the sql string is not union two table, the dbconnection will excute two dbcommand for each table like this:
command 1:

SELECT "m"."Name"
FROM "Table1" AS "m"

command 2:

SELECT "m"."Name"
FROM "Table2" AS "m"

at EF 6.1 version, it's make a sql string like this:

(SELECT "m"."Name"
FROM "Table1" AS "m") union
(SELECT "m"."Name"
FROM "Table2" AS "m")

[divega]

@simpleway2016 In general the LINQ implementation of EF Core only attempts to translate specific patterns to equivalent SQL expressions for relational databases.

In the particular case of Queryable.Concat() an appropriate translation could be two SQL queries with UNION ALL in between but instead we concatenate the results in memory.

Changing this to producing a single SQL query on the server isn't currently a top priority because we believe that for many common scenarios (e.g. for the sample queries you posted) there isn't a significant difference.

Could you elaborate on what scenario you think performing the union on the server would be more useful for you?

[simpleway2016]

var q1 = from m in db.Table1
                     select new
                     {
                         name = m.Name,
                     };
            var q2 = from m in db.Table2
                     select new
                     {
                         name = m.Name,
                     };
            q1 = q1.Concat(q2);
var result = q1.OrderBy(m=>m.Name).Skip(1000).Take(10).ToArray();

If i use the ef core like that,it also make two sql string , excute two dbcommand,and concatenate the results in memory, and then excute "OrderBy(m=>m.Name).Skip(1000).Take(10)",
I think it's bad performance

[negatedx]

I would really like to see this feature too. As @simpleway2016 said above, paging results of a union in the database is much more efficient.

[kingmotley]

It would have a significant performance difference if there is a large amount of latency between the client and the server (such as being in a different timezone).

[nicholasyin]

Consider a banking application where it needs to display a number of types of transactions, each of which has its own specific properties but also shares some common data with other types, in one page, with paging and sorting. This is a perfect case where translating the concat query into one union all statement would be needed.

[John0King]

Please put this feature on a higher priority ,We really need this feature, it should have much higher priority than Lazy loading, Imagine there are 100,000 rows of data and we only need top 10

[John0King]

@ajcvickers what's the state of this ?

[ajcvickers]

@John0King It's still scheduled for 2.1, but as far as I am aware @smitpatel has not implemented it yet. (It's always possible it won't make 2.1 given we don't know the full schedule yet.)

[djuuuuus]

Prompt to me, where I can read all supported things for EF Core? (e.t. Concat....)

[kingmotley]

@roji please reverify that SQL Server does not support take/order by within subset operations. I believe it should, but not if you only specify order by without a top/take.

I believe:
(SELECT TOP 1 1 as x) UNION (SELECT TOP 1 2)
and
(SELECT TOP 1 1 as x ORDER BY 1) UNION (SELECT TOP 1 2)
should both work, while
(SELECT 1 as x ORDER BY 1) UNION (SELECT TOP 1 2)
makes very little sense because the union doesn’t guarantee order, so having the order by in the first subset operation is useless.

[roji]

@kingmotley trying to run your 2nd query ((SELECT TOP 1 1 as x ORDER BY 1) UNION (SELECT TOP 1 2)) errors with "Incorrect syntax near the keyword 'ORDER'". (MSSQL 2017). TOP is indeed allowed in set operands (as I wrote above).

Are you seeing a different behavior?

[kingmotley]

Sorry, I didn't have access to an instance of Sql Server when I originally posted, but was pretty certain that you could do two unions of a limited set. I assumed (wrongly) that since views have the requirement that they can only have a TOP clause when there is also an ORDER BY, then the 2nd query would work. But in practice, using a view like that in a query (and then inlining it) would look more like the following, which does work:

SELECT * FROM (SELECT TOP 1 1 as x ORDER BY 1) t1 UNION SELECT * FROM (SELECT TOP 1 2 as x ORDER BY 1) t2

[chester89]

Guys, you should try https://github.com/tuespetre/Impatient

That's good suggestion for Sql Server, but we use MySQL