Specified Method is not Supported error when doing a Union

[Paul_Ibis]

...and another error in code that worked in LinqToSQL, but doesn't work in LinqConnect
The full error that is shown is "System.NotSupportedException
HResult=0x80131515
Message=Specified method is not supported.
Source=<Cannot evaluate the exception source>
StackTrace:
<Cannot evaluate the exception stack trace>"
It's a pretty complex query, and I don't really want to post the whole thing here, so I have simplified it as below

Code: Select all

IQueryable<ServiceResAvail> serviceResAvailFilter = sc.DB.ServiceResAvail.Where(p => p.AvailStartDateTime >= model.StartDate && p.AvailStartDateTime < model.StartDate.AddDays(1));


IQueryable<ServiceResAvail> serviceResAvailFilter = sc.DB.ServiceResAvail.Where(p => p.AvailStartDateTime >= model.StartDate && p.AvailStartDateTime < model.StartDate.AddDays(1));
var pickups = from sra in serviceResAvailFilter
                          from sr in sc.DB.ServiceResource.Where(p => p.PrimaryKey == sra.ResourceID)
                           ...
                           select new RptResourceManifest()
                           {
                               ServiceId = sr.ServiceID,
                               ...
                           };
var dropoffsSector = (from sra in serviceResAvailFilter
                                  from sr in sc.DB.ServiceResource.Where(p => p.PrimaryKey == sra.ResourceID)
                                  .....
                                  select new RptResourceManifest()
                                  {
                                      ServiceId = sr.ServiceID,
                                      ....
                                  }).Distinct();
                                 
            var dropoffs = (from sra in serviceResAvailFilter
                            from sr in sc.DB.ServiceResource.Where(p => p.PrimaryKey == sra.ResourceID)
                            .....
                            select new RptResourceManifest()
                            {
                                ServiceId = sr.ServiceID,
                                .....
                            }).Distinct();


            var pickupsAndDropOffs = pickups.Union(dropoffs).Union(dropoffsSector).ToList();

The error is happening on the last line.

[Paul_Ibis]

So the error seems to be that each of the separate queries - pickups, dropoffs and dropoffsSector uses the same numbering of parameters. So for example, in the first query, there are these two lines generated in parts of the SQL

Code: Select all

 WHEN [t1].[CustomerID] IS NULL THEN @p1
 ....
 WHERE ([t2].[AvailStartDateTime] >= @p7)
 

in the second query

Code: Select all

WHEN [t14].[CustomerID] IS NULL THEN @p7
....
AND ([t7].[BookStatusE] IN (@p0,@p1,@p2))

so I am assuming that when it tries to do a union of those two queries it fails, since the the parameters are completely different e.g. .in the first query @p1 = '' and in the second @p1 = 40. In the first query @p7 = ' 1 Jan 2020' and in the second @p7 = ''

The query can run successfully if each of the queries is turned into a list first and the union is then done in the code
e.g. change this

Code: Select all

var pickupsAndDropOffs = pickups.Union(dropoffs).Union(dropoffsSector).ToList();

to this

Code: Select all

var pickupsAndDropOffs = pickups.ToList().Union(dropoffs).ToList().Union(dropoffsSector).ToList();

However, I would expect LinqConnect to deal with this and create the union query in SQL. It could also lead to subtle errors if the parameter type in each of the queries was the same, then the query may run without error but it would be running with incorrect values for one of the queries.

[Shalex]

Please localize the issue in a small test project and upload it with the corresponding DDL/DML script to some file exchange server (e.g.: www.dropbox.com) so that we can reproduce the error in our environment.

[Paul_Ibis]

I haven't created a project, but I have managed to track down the issue, and it is quite specific, and not what I initially thought was the issue.

Consider the following simple table

Code: Select all

CREATE TABLE [dbo].[Test](
	[TestID] [int] IDENTITY(1,1) NOT NULL,
	[TestText] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
	[TestID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

and model

Code: Select all

public class RptTest
    {
        public int TestID { get; set; }
        public bool Thing { get; set; }
    }

Then look at this query, where DB is the DataContext

Code: Select all

var test = (from t in sc.DB.Test
                       select new RptTest()
                       {
                           TestID = t.TestID,
                           Thing = true
                       }).Distinct();

            var test2 = (from t in sc.DB.Test
                         select new RptTest()
                         {
                             TestID = t.TestID,
                             Thing = false
                         });


            var test3 = test.Union(test2).ToList();

This fails with the error message "Specified method is not supported" and does not produce any SQL.
If you remove the Distinct() on the first query, it runs.
If you remove setting the property "Thing" and leave the Distinct() it also runs.
if you change

Code: Select all

var test3 = test.Union(test2).ToList()

to

Code: Select all

var test3 = test.ToList().Union(test2.ToList()).ToList();

it will also run.
I have not tested the returned data to check if it is returning what is expected though.

Please let me know if you can reproduce this.

[Shalex]

Thank you for the detailed steps. We have reproduced the issue and are investigating it.