Hi,

we have two tables with the following structure:

Vehicle
    Id int
    
Subscription
    VehicleId int
    ProviderId int
    PublicationStateId int
    
FK: Vehicle 1=>n Subscription

Now we're trying to sort the Vehicles on the existence of a specific value in the related subscriptions table with a query like this:

[TestMethod]
public void ReproduceBugGetActiveVehiclesSortedByProviderId()
{
    var filter = new VehicleFilter();
    filter.PageSize = 10;
    filter.PageNumber = 2;
    filter.ProviderId = 0;
    
    IQueryable<VehicleEntity> vehicles = linqMetaData.Vehicle;

        var activeVehicles = vehicles.Select( o => new 
        {
            Id = o.Id,
            IsSelectedProvider = o.Subscriptions.Any(s => s.ProviderId == filter.ProviderId),
            Subscriptions = o.Subscriptions.Select(s => new { PublicationStateId = s.PublicationStateId }),
        });

    activeVehicles = activeVehicles.OrderBy(o => !o.IsSelectedProvider);
    activeVehicles = activeVehicles.TakePage(filter.PageNumber, filter.PageSize);

    foreach (var o in activeVehicles)
    {
        Console.WriteLine(o);
    }
}

Getting page 1 works fine, but when we try to get page 2 we get a SQL error like this: "Column, parameter, or variable #4: Cannot find data type Variant."

We're using LLBLGen 2.6 adapter with the most recent runtime libaries (downloaded today)

Can you help us out? Need any more info?

BTW congrats on the new website, looking sharp!

Thanks!

Wiebe

Please can you post the SQL that is generated for both the working page and the one which fails ?

Matt

Hi Matt,

This is the failing query (rewritten so you can directly execute it in SQL management studio):

declare @ProviderId1 int; set @ProviderId1 = 0;
declare @LO12 int; set @LO12 = 1;
declare @IsSelectedProvider3 bit; set @IsSelectedProvider3 = 1;
declare @__rownoStart int; set @__rownoStart = 10;
declare @__rownoEnd int; set @__rownoEnd = 20;
CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT NULL,[Id][Int] NULL,[IsSelectedProvider][Bit] NULL,[LPFA_5][Variant] NULL);
INSERT INTO #TempTable ([Id],[IsSelectedProvider],[LPFA_5]) SELECT TOP 21 [LPA_L1].[Id], [LPA_L1].[IsSelectedProvider], [LPA_L1].[LPFA_5] FROM (SELECT [LPLA_1].[ID] AS [Id], CASE WHEN  EXISTS (SELECT [LPLA_2].[ResultPage] FROM [dbo].[OrganizationProviderVehicles] [LPLA_2]  WHERE ( [LPLA_1].[ID] = [LPLA_2].[VehicleID] AND ( [LPLA_2].[ProviderID] = @ProviderId1))) THEN 1 ELSE 0 END AS [IsSelectedProvider], @LO12 AS [LPFA_5] FROM [dbo].[Vehicles] [LPLA_1] ) [LPA_L1] ORDER BY CASE WHEN NOT ( [LPA_L1].[IsSelectedProvider] = @IsSelectedProvider3) THEN 1 ELSE 0 END ASC;
SELECT [Id],[IsSelectedProvider],[LPFA_5] FROM #TempTable WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd ORDER BY [__rowcnt] ASC;
DROP TABLE #TempTable

By the way, it would be really useful if the generated queries (as they appear in LLBLGen exceptions and tracing) would be executable directly (instead of the current "parameter: @variableName Int32 etc..." format).

Ah, we didn't specify the SqlServerDQECompatibilityLevel in our unit test configuration file.

Adding this solves the issue in our SQL 2008 configuration:

<add key="SqlServerDQECompatibilityLevel" value="2" />

Thanks,

Wiebe