Hi all,

We're still loving using LLBLGen - what a professional, productive product! We solely use DevExpress components (Win + ASP.NET) these days, and are extremely satisfied with these as well.

Its just a shame when your users need to work with those large datasets (>100,000 records) because there seems to be no way to get DevEx's incredible 1-3 second response times and performance (particularly the filtering and summaries) without using their XPO solution (which is quite cumbersome, compared to LLBLGen entities).

I see this has been discussed before, and understand Frans approach to focus on the bigger picture. (It would be interesting to know how many other LLBL customers this does impact tho )

Anyway there's been some more info. presented by DevEx on the IListServer interface + doco, which I thought others might be interested in..

Interface info: http://www.devexpress.com/Support/Center/KB/p/A1022.aspx?searchtext=ilistserver&p=T4|P0 Sample: http://www.devexpress.com/Support/Center/e/E1307.aspx

Can't you use the Linq provider in grid Server mode ? That does work natively with LLBLGen...

grtz, Danny

We can't implement the interface as that would mean we've to ship the assembly (and all customers as well!) which is a bit much. the linqdatasource should indeed solve your problem.

Jamanga wrote:

Hi all,

We're still loving using LLBLGen - what a professional, productive product! We solely use DevExpress components (Win + ASP.NET) these days, and are extremely satisfied with these as well.

Its just a shame when your users need to work with those large datasets (>100,000 records) because there seems to be no way to get DevEx's incredible 1-3 second response times and performance (particularly the filtering and summaries) without using their XPO solution (which is quite cumbersome, compared to LLBLGen entities).

I see this has been discussed before, and understand Frans approach to focus on the bigger picture. (It would be interesting to know how many other LLBL customers this does impact tho )

Anyway there's been some more info. presented by DevEx on the IListServer interface + doco, which I thought others might be interested in..

Interface info: http://www.devexpress.com/Support/Center/KB/p/A1022.aspx?searchtext=ilistserver&p=T4|P0 Sample: http://www.devexpress.com/Support/Center/e/E1307.aspx

We're using XtraGrid in server mode (LinqServerModeSource), it works very well with LinqMetaData, and with good performances (in production we have table with 40000 records)...

Hi all thanks for the feedback I really appreciate it.

Otis wrote:

We can't implement the interface as that would mean we've to ship the assembly (and all customers as well!) which is a bit much. the linqdatasource should indeed solve your problem.

Thanks Frans, yep I perfectly understand. I guess a flexible scenario would be to allow the LLBL Datasource to be extended via an interface. And thanks for the Linq tip..

Antonio wrote:

We're using XtraGrid in server mode (LinqServerModeSource), it works very well with LinqMetaData, and with good performances (in production we have table with 40000 records)...

Great, will check out DevEx's LinqServerModeSource now - don't know how I missed this... thanks again!

Hi all,

I'm investigating this in my spare time at work and am pretty new to LINQ so please bear with me.

LLBLGen 2.6 (Oct 6, 2008 ) Runtime versions: SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll : 2.6.9.313 SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll : 2.6.9.317 SD.LLBLGen.Pro.DQE.SybaseAse.NET20.dll : 2.6.8.819

Because of the single-row retrieval bug in Sybase I rebuilt LLBL's ORMSupport & LINQProvider assemblies btw. thanks Frans for making this area of the code a seperate method

The following is the query that worked - it was very quick and supported the standard and custom filtering - yay!

protected void dsLinq_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) {
    e.KeyExpression = ResourceFields.ResourceId.Name;
    DataAccessAdapter myAdapter = new DataAccessAdapter(OrYxWebAccessFunctions.GetDefaultConnectionString());
    e.QueryableSource = new LinqMetaData(myAdapter).Resource; // works!
}

And generated the following query: Query:

SELECT DISTINCT TOP 64 LPLA_1.RESOURCEID AS ResourceId, LPLA_1.RESOURCECLIENTID AS ClientId, LPLA_1.RESOURCETIMECODEID AS Resourcetimecodeid, LPLA_1.COMPANY_ID AS CompanyId, LPLA_1.RESOURCETYPECODE AS TypeCode, LPLA_1.RESOURCELOUTHID AS LouthId, LPLA_1.RESOURCECLIENTREF AS ClientRef, LPLA_1.RESOURCETAPENUMBER AS TapeNumber, LPLA_1.RESOURCETITLE AS Title, LPLA_1.RESOURCESHOWNAME AS Showname, LPLA_1.RESOURCESERIESNO AS SeriesNo, LPLA_1.RESOURCEEPISODENO AS EpisodeNo, LPLA_1.RESOURCEDURATION AS Duration, LPLA_1.RESOURCENOMINALDURATION AS NominalDuration, LPLA_1.RESOURCESTARTTIME AS StartTime, LPLA_1.RESOURCEENDTIME AS EndTime, LPLA_1.RESOURCESEGMENTCOUNT AS SegmentCount, LPLA_1.RESOURCESTARTDATE AS ContractStartDate, LPLA_1.RESOURCEENDDATE AS ContractEndDate, LPLA_1.RESOURCERECEIVEDATE AS ReceiveDate, LPLA_1.RESOURCERETURNDATE AS ReturnDate, LPLA_1.RESOURCERETURNREASON AS ReturnReason, LPLA_1.RESOURCEONAIRDATE AS OnAirDate, LPLA_1.RESOURCEOFFAIRDATE AS OffAirDate, LPLA_1.RESOURCECLASSIFICATION AS Classification, LPLA_1.RESOURCECLASSIFICATIONDETAILS AS ClassificationDetails, LPLA_1.RESOURCETRANSITION AS TransitionEffect, LPLA_1.RESOURCEBUGPOS AS Bugpos, LPLA_1.RESOURCESYNOPSIS AS Synopsis, LPLA_1.RESOURCETAPETYPE AS TapeType, LPLA_1.RESOURCEGENRECODE AS GenreCode, LPLA_1.RESOURCESUBGENRECODE AS SubGenreCode, LPLA_1.RESOURCEQAFAILEDFLAG AS QaFailed, LPLA_1.RESOURCELIVEFLAG AS Live, LPLA_1.RESOURCEPRODUCTIONYEAR AS ProductionYear, LPLA_1.RESOURCELANGUAGE AS Language, LPLA_1.RESOURCESUBTITLEFLAG AS Subtitle, LPLA_1.RESOURCECLOSEDCAPTIONFLAG AS ClosedCaption, LPLA_1.RESOURCEORIGINCOUNTRY AS OriginCountry, LPLA_1.RESOURCESIZERATIO AS SizeRatio, LPLA_1.RESOURCEBUSINESSCATEGORY AS BusinessCategory, LPLA_1.RESOURCELOCATIONSITE AS LocationSite, LPLA_1.RESOURCELOCATION AS Location, LPLA_1.RESOURCENOTES AS Notes, LPLA_1.RESOURCESCHEDULENOTES AS ScheduleNotes, LPLA_1.RESOURCECREATEDATE AS CreateDate, LPLA_1.RESOURCEBROADCASTTYPE AS BroadcastType FROM TMS.dbo.RESOURCES LPLA_1 ORDER BY LPLA_1.RESOURCEID ASC

I then started playign around e.g.

e.QueryableSource = from r in new LinqMetaData(myAdapter).Resource select r;

And this didn't work An exception was caught during the execution of a retrieval query: The column 'ResourceId' in the outer query is not present in the derived table expression. . Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Generated Query (aliases reversed?)

SELECT DISTINCT TOP 64 LPA_L1.ResourceId, LPA_L1.ClientId, LPA_L1.Resourcetimecodeid, LPA_L1.CompanyId, LPA_L1.TypeCode, LPA_L1.LouthId, LPA_L1.ClientRef, LPA_L1.TapeNumber, LPA_L1.Title, LPA_L1.Showname, LPA_L1.SeriesNo, LPA_L1.EpisodeNo, LPA_L1.Duration, LPA_L1.NominalDuration, LPA_L1.StartTime, LPA_L1.EndTime, LPA_L1.SegmentCount, LPA_L1.ContractStartDate, LPA_L1.ContractEndDate, LPA_L1.ReceiveDate, LPA_L1.ReturnDate, LPA_L1.ReturnReason, LPA_L1.OnAirDate, LPA_L1.OffAirDate, LPA_L1.Classification, LPA_L1.ClassificationDetails, LPA_L1.TransitionEffect, LPA_L1.Bugpos, LPA_L1.Synopsis, LPA_L1.TapeType, LPA_L1.GenreCode, LPA_L1.SubGenreCode, LPA_L1.QaFailed, LPA_L1.Live, LPA_L1.ProductionYear, LPA_L1.Language, LPA_L1.Subtitle, LPA_L1.ClosedCaption, LPA_L1.OriginCountry, LPA_L1.SizeRatio, LPA_L1.BusinessCategory, LPA_L1.LocationSite, LPA_L1.Location, LPA_L1.Notes, LPA_L1.ScheduleNotes, LPA_L1.CreateDate, LPA_L1.BroadcastType FROM (SELECT LPLA_1.RESOURCEID, LPLA_1.RESOURCECLIENTID, LPLA_1.RESOURCETIMECODEID, LPLA_1.COMPANY_ID, LPLA_1.RESOURCETYPECODE, LPLA_1.RESOURCELOUTHID, LPLA_1.RESOURCECLIENTREF, LPLA_1.RESOURCETAPENUMBER, LPLA_1.RESOURCETITLE, LPLA_1.RESOURCESHOWNAME, LPLA_1.RESOURCESERIESNO, LPLA_1.RESOURCEEPISODENO, LPLA_1.RESOURCEDURATION, LPLA_1.RESOURCENOMINALDURATION, LPLA_1.RESOURCESTARTTIME, LPLA_1.RESOURCEENDTIME, LPLA_1.RESOURCESEGMENTCOUNT, LPLA_1.RESOURCESTARTDATE, LPLA_1.RESOURCEENDDATE, LPLA_1.RESOURCERECEIVEDATE, LPLA_1.RESOURCERETURNDATE, LPLA_1.RESOURCERETURNREASON, LPLA_1.RESOURCEONAIRDATE, LPLA_1.RESOURCEOFFAIRDATE, LPLA_1.RESOURCECLASSIFICATION, LPLA_1.RESOURCECLASSIFICATIONDETAILS, LPLA_1.RESOURCETRANSITION, LPLA_1.RESOURCEBUGPOS, LPLA_1.RESOURCESYNOPSIS, LPLA_1.RESOURCETAPETYPE, LPLA_1.RESOURCEGENRECODE, LPLA_1.RESOURCESUBGENRECODE, LPLA_1.RESOURCEQAFAILEDFLAG, LPLA_1.RESOURCELIVEFLAG, LPLA_1.RESOURCEPRODUCTIONYEAR, LPLA_1.RESOURCELANGUAGE, LPLA_1.RESOURCESUBTITLEFLAG, LPLA_1.RESOURCECLOSEDCAPTIONFLAG, LPLA_1.RESOURCEORIGINCOUNTRY, LPLA_1.RESOURCESIZERATIO, LPLA_1.RESOURCEBUSINESSCATEGORY, LPLA_1.RESOURCELOCATIONSITE, LPLA_1.RESOURCELOCATION, LPLA_1.RESOURCENOTES, LPLA_1.RESOURCESCHEDULENOTES, LPLA_1.RESOURCECREATEDATE, LPLA_1.RESOURCEBROADCASTTYPE FROM TMS.dbo.RESOURCES LPLA_1) LPA_L1 ORDER BY LPA_L1.ResourceId ASC

Any ideas? TIA

A bit more info, this query did work...

e.QueryableSource = 
    from r in new LinqMetaData(myAdapter).Resource 
    where r.CompanyId == 4
    select r;

with paging/filtering etc, but when dragging a column up for grouping (e.g by ResourceId), I got the same error as above. btw. I should have mentioned, I'm using 2008.3.6 version of DevEx's components.

Please enable LinqSupportClasses tracing (at level 3 (three), not 4). This will give the expression tree in text in the debug output when ran in debug mode.

Also, please make the query as simple as possible so we can try to reproduce it. The more information you can give the better, like if entities use inheritance etc.

Hi Frans,

Thanks for the response, expression details + query below I know it didn't look like it, but that was actually a simple query i.e. 1:1 relationship with table, no inheritance. I was testing it because this table has a good variety of datatypes and heaps of data. But I take your point

I'll using the Client table now - its just a single table query and has a 1:1 Entity relationship, no inheritance. I'm not at the stage of even trying joins/prefetchs yet... Plesae let me know if you need anymore details.

query did not work: = from c in new LinqMetaData(myAdapter).Client select c;

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[OrYx.TVShopping.TMS.EntityClasses.ClientEntity]).Select(c => c).OrderBy( => .ClientId).Count()
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT LPLA_1.CLIENTID, LPLA_1.COMPANY_ID, LPLA_1.CLIENTNAME, LPLA_1.CLIENTCODE, LPLA_1.CLIENTADDRESS, LPLA_1.CLIENTCONTACT, LPLA_1.CLIENTPHONE, LPLA_1.CLIENTCOLOR, LPLA_1.CLIENTACTIVE, LPLA_1.CLIENTPHONE2, LPLA_1.CLIENTMOBILE, LPLA_1.CLIENTEMAIL, LPLA_1.CLIENTNOTES, LPLA_1.CONTRACTSTARTDATE, LPLA_1.CONTRACTENDDATE FROM TMS.dbo.CLIENTS LPLA_1

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT DISTINCT TOP 1 COUNT(*) AS LPAV_ FROM (SELECT LPLA_1.CLIENTID, LPLA_1.COMPANY_ID, LPLA_1.CLIENTNAME, LPLA_1.CLIENTCODE, LPLA_1.CLIENTADDRESS, LPLA_1.CLIENTCONTACT, LPLA_1.CLIENTPHONE, LPLA_1.CLIENTCOLOR, LPLA_1.CLIENTACTIVE, LPLA_1.CLIENTPHONE2, LPLA_1.CLIENTMOBILE, LPLA_1.CLIENTEMAIL, LPLA_1.CLIENTNOTES, LPLA_1.CONTRACTSTARTDATE, LPLA_1.CONTRACTENDDATE FROM TMS.dbo.CLIENTS LPLA_1) LPA_L1

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[OrYx.TVShopping.TMS.EntityClasses.ClientEntity]).Select(c => c).OrderBy( => .ClientId).Take(64)
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT LPLA_1.CLIENTID, LPLA_1.COMPANY_ID, LPLA_1.CLIENTNAME, LPLA_1.CLIENTCODE, LPLA_1.CLIENTADDRESS, LPLA_1.CLIENTCONTACT, LPLA_1.CLIENTPHONE, LPLA_1.CLIENTCOLOR, LPLA_1.CLIENTACTIVE, LPLA_1.CLIENTPHONE2, LPLA_1.CLIENTMOBILE, LPLA_1.CLIENTEMAIL, LPLA_1.CLIENTNOTES, LPLA_1.CONTRACTSTARTDATE, LPLA_1.CONTRACTENDDATE FROM TMS.dbo.CLIENTS LPLA_1

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT DISTINCT TOP 64 LPA_L1.ClientId, LPA_L1.CompanyId, LPA_L1.Name, LPA_L1.Code, LPA_L1.Address, LPA_L1.Contact, LPA_L1.Phone, LPA_L1.Color, LPA_L1.Active, LPA_L1.Phone2, LPA_L1.Mobile, LPA_L1.Email, LPA_L1.Notes, LPA_L1.ContractStartDate, LPA_L1.ContractEndDate FROM (SELECT LPLA_1.CLIENTID, LPLA_1.COMPANY_ID, LPLA_1.CLIENTNAME, LPLA_1.CLIENTCODE, LPLA_1.CLIENTADDRESS, LPLA_1.CLIENTCONTACT, LPLA_1.CLIENTPHONE, LPLA_1.CLIENTCOLOR, LPLA_1.CLIENTACTIVE, LPLA_1.CLIENTPHONE2, LPLA_1.CLIENTMOBILE, LPLA_1.CLIENTEMAIL, LPLA_1.CLIENTNOTES, LPLA_1.CONTRACTSTARTDATE, LPLA_1.CONTRACTENDDATE FROM TMS.dbo.CLIENTS LPLA_1) LPA_L1 ORDER BY LPA_L1.ClientId ASC

-> raised AseException exception: The column 'ClientId' in the outer query is not present in the derived table expression

query did work: = new LinqMetaData(myAdapter).Client;

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[OrYx.TVShopping.TMS.EntityClasses.ClientEntity]).OrderBy( => .ClientId).Count()
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT DISTINCT TOP 1 COUNT(*) AS LPAV_ FROM TMS.dbo.CLIENTS LPLA_1

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[OrYx.TVShopping.TMS.EntityClasses.ClientEntity]).OrderBy( => .ClientId).Take(64)
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT DISTINCT TOP 64 LPLA_1.CLIENTID AS ClientId, LPLA_1.COMPANY_ID AS CompanyId, LPLA_1.CLIENTNAME AS Name, LPLA_1.CLIENTCODE AS Code, LPLA_1.CLIENTADDRESS AS Address, LPLA_1.CLIENTCONTACT AS Contact, LPLA_1.CLIENTPHONE AS Phone, LPLA_1.CLIENTCOLOR AS Color, LPLA_1.CLIENTACTIVE AS Active, LPLA_1.CLIENTPHONE2 AS Phone2, LPLA_1.CLIENTMOBILE AS Mobile, LPLA_1.CLIENTEMAIL AS Email, LPLA_1.CLIENTNOTES AS Notes, LPLA_1.CONTRACTSTARTDATE AS ContractStartDate, LPLA_1.CONTRACTENDDATE AS ContractEndDate FROM TMS.dbo.CLIENTS LPLA_1 ORDER BY LPLA_1.CLIENTID ASC

Jamanga wrote:

I should have mentioned, I'm using 2008.3.6 version of DevEx's components.

Does the query work if you wouldn't use DevEx's components? For instance, making a normal ConsoloApp and doing a normal LINQ2LLBL qury.

Hi Daelmo

Yep doing the query manually doesn't raise any exception e.g.

DataAccessAdapter myAdapter = new DataAccessAdapter(OrYxWebAccessFunctions.GetDefaultConnectionString());
var qryTest = from c in new LinqMetaData(myAdapter).Client select c;
int iCount = qryTest.Count();

I put this in my PageLoad event, but no expressiontree info. came up in the Output this time... not sure why.

I think I know what the problem is:

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[OrYx.TVShopping.TMS.EntityClasses.ClientEntity]).Select(c => c).OrderBy( => .ClientId).Take(64)

....

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query:
    Query: SELECT DISTINCT TOP 64 LPA_L1.ClientId, LPA_L1.CompanyId, LPA_L1.Name, LPA_L1.Code, LPA_L1.Address, LPA_L1.Contact, LPA_L1.Phone, LPA_L1.Color, LPA_L1.Active, LPA_L1.Phone2, LPA_L1.Mobile, LPA_L1.Email, LPA_L1.Notes, LPA_L1.ContractStartDate, LPA_L1.ContractEndDate FROM (SELECT LPLA_1.CLIENTID, LPLA_1.COMPANY_ID, LPLA_1.CLIENTNAME, LPLA_1.CLIENTCODE, LPLA_1.CLIENTADDRESS, LPLA_1.CLIENTCONTACT, LPLA_1.CLIENTPHONE, LPLA_1.CLIENTCOLOR, LPLA_1.CLIENTACTIVE, LPLA_1.CLIENTPHONE2, LPLA_1.CLIENTMOBILE, LPLA_1.CLIENTEMAIL, LPLA_1.CLIENTNOTES, LPLA_1.CONTRACTSTARTDATE, LPLA_1.CONTRACTENDDATE FROM TMS.dbo.CLIENTS LPLA_1) LPA_L1 ORDER BY LPA_L1.ClientId ASC

As you can see, there's a derived table. LLBLGen Pro has code to make sure the fields in the outer query indeed reference fields in the derived table and corrects them if necessary.

With ASE there's a problem though: for some dumb reason, it can't deal with aliases in a derived table/subquery if there's no groupby. So the fields in the derived table have aliases and the fields in the outer query rely on that fact and refer to these aliases, however in the query produced these aliases aren't there as the subquery can't have aliases for fields.

The problem is: the outer fields don't have the real field name, they only refer to the alias as that's the name of the field in the derived table! So the routine which checks and corrects derivedtable referencing fields has to know up front if the aliases in the derived table are going to stay. Unfortunately, it doesn't know that at that point, as it doesn't know for which db it is creating the query for (as it's not at that stage yet).

I know the query above isn't optimal, one could simply remove the outer query and place the order by inside the inner query and it will result in the same thing. Linq queries however aren't optimized that way at the moment.

So I don't really see a solution for this in the linq department. the only thing you could do is manually handle the paging events from the grid in the code behind and write native LLBLGen pro query constructs to fetch the page. This will be more code, but it's the only way to do this (so bind it to an llblgen pro datasource and use livepersistence == false) for sybase ASE due to that restriction.

Ok thanks Frans,

That is disappointing. Is ASE the only provider that has this limitation do you know?

I'll checkout your paging idea, I hadn't thought of that. It's important that I maintain the inbuilt filtering/sorting for my users for these big datasets.

Jamanga wrote:

Ok thanks Frans,

That is disappointing. Is ASE the only provider that has this limitation do you know?

Sybase ASE is the only database we support that has this limitation, all other databases don't have this limitation, with the remark that Firebird 1.x doesn't support derived tables at all, so it also doesn't support the query you're running.

I'll checkout your paging idea, I hadn't thought of that. It's important that I maintain the inbuilt filtering/sorting for my users for these big datasets.

If you use our datasourcecontrol, with liverpersistence set to false, you can write the correct call to the adapter without a problem, as one would do in .NET 2.x . At least that's what it should do. I do recall that it might give problems that it doesn't supply the paging parameters in all cases to the datasourcecontrol, but I also do recall they did add a property to the grid to make this possible (so you lose some features but you can page on the server).