All geek questions in one place

Union (or Concat etc.) With constant values ​​and projections

I found a very nasty trick with Linq-to-sql, and I'm not sure what the best solution is.

If you accept the simple L2S Union operator and include the L2S code in one direction and the constants in the other, then the constants are not included in the SQL connection and are only projected into the output after SQL, which leads to an SQL error regarding the number of columns that are not considered for joining.

As an example:

(from d in dc.mytable where foo == "bar" select new {First = d.Foo, Second = d.Roo})
.Union(from e in dc.mytable where foo == "roo" select new {First= "", Second = e.Roo})

This will result in the error "All queries combined using the UNION, INTERSECT, or EXCEPT operator must have an equal number of expressions in their target lists.

This is especially insidious (and crazy), because the list clearly has the same number of expressions, but when you look at SQL, you will notice that it does not generate a column for the "First" in the second half of the Union. This is due to the fact that the "First" is inserted into the projection AFTER the request.

Well, a simple solution is to simply convert each part to Enumerables or Lists or something else, and then do a join in memory, not SQL, and this is great if you are dealing with a small amount of data. However, if you are working with a large set of data, which you then plan to additionally filter (in sql) before returning, this is not ideal.

I suppose I'm looking for a way to get L2S to include a column in SQL. Is it possible?

UPDATE:

, . , , - -.

, L2S .

+3
2

, . , .

+1

Linq2SQL. LinqPad .

(from d in dc.mytable where foo == "bar" select new {First = d.Foo, Second = d.Roo})  
.Union(from e in dc.mytable where foo == "roo" select new {First= "", Second = e.Roo})

- :

SELECT [t2].[Foo], [t2].[Roo]
FROM (
    SELECT [t0].[Foo], @p0 AS [value]
    FROM [dc].[Mytable] AS [t0]
    UNION ALL
    SELECT [t1].[Foo], [t1].[Roo]
    FROM [dc].[Mytable] AS [t1]
    ) AS [t2]

, "" "Roo", .

, ,

(from e in dc.mytable where foo == "roo" select new {First= "", Second = e.Roo})  
.Union(from d in dc.mytable where foo == "bar" select new {First = d.Foo, Second = d.Roo})

, T-SQL , , T-SQL .

. . LinqPad .

+1

Source: https://habr.com/ru/post/1759250/

More articles:


All Articles