Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Bug with sorting on anonymous type expression in combination with paging
 

Pages: 1
LLBLGen Pro Runtime Framework
Bug with sorting on anonymous type expression in combination with paging
Page:1/1 

  Print all messages in this thread  
Poster Message
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 30-Sep-2010 12:10:51.  
Hi,

we have two tables with the following structure:
Code:
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:

Code:
[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
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
MTrinder
User



Location:
London by day, Milton Keynes by night.
Joined on:
08-Oct-2008 17:55:47
Posted:
1461 posts
# Posted on: 30-Sep-2010 21:47:19.  
Please can you post the SQL that is generated for both the working page and the one which fails ?

Matt


  Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 04-Oct-2010 13:53:11.  
Hi Matt,

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

Code:
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).

Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 04-Oct-2010 14:14:01.  
Ah, we didn't specify the SqlServerDQECompatibilityLevel in our unit test configuration file.

Adding this solves the issue in our SQL 2008 configuration:
Code:
<add key="SqlServerDQECompatibilityLevel" value="2" />

Thanks,

Wiebe


Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.