Linq OrderBy behavior

Hello,

I am using Visual Studio 2017, winform client (DA to custom server) and I have a strange problem with pagination.

In detail:

  1. There are various tables in Postgres DB with a lot of records
  2. Custom DA server that expose this tables
  3. Custom winform client that show this tables using DevExpress Gridview with LinqServerModeSource

This works for first records, but when i scroll down, after a while, devexpress components give me an InconsistencyDetectedException.

I have a support request opened in Devexpress support center at url https://www.devexpress.com/Support/Center/Question/Details/T616177/using-gridcontrol-pagination-without-database-orm

In last comment DevExpress respond me:

This behavior takes place because the LinqServerModeSource’s queryable source (var result = (from t in …DataAdapter.GetTable()…) OrderBy method returns data in an inconsistent order.
Let’s consider an example. GridControl requested five rows from an underlaying data source. For the first time, the underlying data source returned these five rows in the following order: 1,2,3,4,5. For the second time, it returned 2,4,1,3,5. Then 5,1,4,2,3 etc.
To overcome this behavior, make sure that the underlaying data source OrderBy method always returns rows in the same order.

Can be a bug on ordering? Am I doing someting wrong?
Thanks

Hello

It is hard to say anything without additional information.

F.e. take a look at this data:

Let’s assume we have a table TABLE_1 with this data in it:

ID Value
1  100
2  110
3  110

It is quite possible that query SELECT ID FROM TABLE ORDER BY VALUE will return 1 2 3 while eventually the same query SELECT ID FROM TABLE_1 ORDER BY VALUE will return 1 3 2

Note that both results are valid but have different records order in them.

The easiest way to find out what’s going wrong is to add some additional logging to the DA server and run the client app. You’ll see the real queries being executed. Then you’ll need to analyze your actual data structure and the data to find out if there is something wrong with consistent ordering of data. Of course if the ORDER BY part will be different then that will be an issue in DA core and we’ll need to dig deeper. Yet in my (quite simple) tests I didn’t see any bugs.

So, you need to add this code to your data service class:

	public override Binary GetData(string[] tableNames, TableRequestInfo[] requestInfo)
	{
		// This will log the incoming DA SQL query
		if ((requestInfo.Length > 0) && (requestInfo[0] is TableRequestInfoV6))
		{
			System.Diagnostics.Debug.WriteLine("DA SQL: " + ((TableRequestInfoV6)requestInfo[0]).Sql);
		}

		return base.GetData(tableNames, requestInfo);
	}

This code will display the incoming request sent by the client

Then add this line to the data service’s constructor:

	public DataService()
	{
		this.InitializeComponent();

		this.BeforeExecutingGetDataReader += DataService_BeforeExecutingGetDataReader; // << The line to add
	}

and implement the event handler as

	private void DataService_BeforeExecutingGetDataReader(RemObjects.DataAbstract.Schema.ServiceSchema sender, RemObjects.DataAbstract.Schema.BeforeExecutingGetDataReaderEventArgs e)
	{
		System.Diagnostics.Debug.WriteLine("ADO.NET SQL: " + e.Command.CommandText);
	}

This code will reveal the actual SQL sent to the database. You can execute it later outside of your server and check what it actually returns.

Hope that helps

Thanks for support.

As I posted in devexpress support center, i can see this strange behaviour:

1 - I can view the first record size, but when i scroll down the grid for some record (when in same time new record is created in db), all record disappear (it’s as if the datasource emptied)
2 - same as 1, but when no record is created, after scrolling down (about 4500 records) new requested records are all empty(datasource is not emptied, previous loaded records are still present).
3 - If grid use embedded navigator, if i request “last page” i can view first records and last records, all the records in the middle are blanks

So I have created a separate project for testing it without other features, in first place i can see that there are exception thrown every time that i create a new row while scrolling down.
You can view the test project, src and bin, at: https://drive.google.com/open?id=1KWBPeDJqpVIW5CforESrRD1kIARJT7v2

For replicate it simple:

  • load a backup of postgres db from test_data_context.backup
    db = test_data_context
    user = test_data_context
    password = test_data_context
  • run server
  • run 1 instance of client and click on button for insert new rows
  • run 2 instance in same time and scolling down

Client give this exception

Si è verificata l’eccezione DevExpress.Data.Helpers.InconsistencyDetectedException
HResult=0x80131600
Messaggio=Key ‘39311’ fetched at index 100 does not match previously fetched key ‘39309’ for the same index; Key ‘39310’ fetched at index 101 does not match previously fetched key ‘39308’ for the same index; Key ‘39309’ fetched at index 102 does not match previously fetched key ‘39307’ for the same index; Key ‘39308’ fetched at index 103 does not match previously fetched key ‘39306’ for the same index
Origine=<Non è possibile valutare l’origine dell’eccezione>
Analisi dello stack:
in DevExpress.Data.Helpers.ServerModeCache.RaiseInconsistencyDetected(String message)

Server give this log:

DA SQL: SELECT COUNT()
FROM [generic.command] AS [t0]
ADO.NET SQL: SELECT COUNT(
) FROM “generic”.“command” “t0”
DA SQL: SELECT TOP (128) [t0].[datetime_created], [t0].[datetime_done], [t0].[datetime_planned], [t0].[done], [t0].[id], [t0].[id_alert], [t0].[id_cc], [t0].[id_code], [t0].[id_code_phase], [t0].[id_group_cc], [t0].[id_note], [t0].[id_order], [t0].[id_piece_phase], [t0].[id_piece_phase_abstract], [t0].[key], [t0].[month], [t0].[note], [t0].[year]
FROM [generic.command] AS [t0]
ORDER BY [t0].[id] DESC
ADO.NET SQL: SELECT “t0”.“datetime_created”, “t0”.“datetime_done”, “t0”.“datetime_planned”, “t0”.“done”, “t0”.“id”, “t0”.“id_alert”, “t0”.“id_cc”, “t0”.“id_code”, “t0”.“id_code_phase”, “t0”.“id_group_cc”, “t0”.“id_note”, “t0”.“id_order”, “t0”.“id_piece_phase”, “t0”.“id_piece_phase_abstract”, “t0”.“key”, “t0”.“month”, “t0”.“note”, “t0”.“year” FROM “generic”.“command” “t0” ORDER BY “t0”.“id” DESC LIMIT 128
‘WinFormsApplication42TestOrderBy.exe’ (CLR v4.0.30319: WinFormsApplication42TestOrderBy.exe): caricamento di ‘C:\WINDOWS\assembly\GAC_MSIL\RemObjects.SDK.ZLib\9.4.107.1363__3df3cad1b7aa5098\RemObjects.SDK.ZLib.dll’ completato. Simboli caricati.
DA SQL: SELECT COUNT()
FROM [generic.command] AS [t0]
ADO.NET SQL: SELECT COUNT(
) FROM “generic”.“command” “t0”
DA SQL: SELECT TOP (128) [t0].[datetime_created], [t0].[datetime_done], [t0].[datetime_planned], [t0].[done], [t0].[id], [t0].[id_alert], [t0].[id_cc], [t0].[id_code], [t0].[id_code_phase], [t0].[id_group_cc], [t0].[id_note], [t0].[id_order], [t0].[id_piece_phase], [t0].[id_piece_phase_abstract], [t0].[key], [t0].[month], [t0].[note], [t0].[year]
FROM [generic.command] AS [t0]
ORDER BY [t0].[id] DESC
ADO.NET SQL: SELECT “t0”.“datetime_created”, “t0”.“datetime_done”, “t0”.“datetime_planned”, “t0”.“done”, “t0”.“id”, “t0”.“id_alert”, “t0”.“id_cc”, “t0”.“id_code”, “t0”.“id_code_phase”, “t0”.“id_group_cc”, “t0”.“id_note”, “t0”.“id_order”, “t0”.“id_piece_phase”, “t0”.“id_piece_phase_abstract”, “t0”.“key”, “t0”.“month”, “t0”.“note”, “t0”.“year” FROM “generic”.“command” “t0” ORDER BY “t0”.“id” DESC LIMIT 128
DA SQL: SELECT TOP (201) SKIP (100) [t0].[id]
FROM [generic.command] AS [t0]
ORDER BY [t0].[id] DESC
ADO.NET SQL: SELECT “t0”.“id” FROM “generic”.“command” “t0” ORDER BY “t0”.“id” DESC LIMIT 201 OFFSET 100
Il thread 0x2bb4 è terminato con il codice 0 (0x0).

This exception will thrown with simple linq query but in production case there are join and other more complex requests, in test project i replicate both.
I can’t understand well whre the problem is, but when i see that there are problem with new rows, i created a linq with data filtering as:

from t in fDataModule.DataAdapter.GetTable<generic_command>()
where t.datetime_created < MyNow
select t

And this works without error, but in production project i haven’t always a secure column for filtering tables with large data.

Hello

I strongly suggest you to add to the DevEx ticket a mention that you do insert data into the dataset from one client app while browsing it in another client app. This is not DA specific as it should fail with any other SQL datasource as well.

It is quite expected that altering a base dataset will break pagination in a DevEx Grid because it seems that this grid expects rows to stay in place while applying pagination query. I suggest you to ask the DevEx team how one can somehow lift this restriction.

Regards

Hello,
I asked support to DevExpress and they found me a temporary solution, but for a permanent solution i must ask you to check the issue below.

Thank you for the callstacks. We have reviewed them and see that while GridControl fetches rows from your data base, RemObjects.SDK.SuperHttpClientChannelWorker sends the WM_Paint message to the GridControl. This forces the GridControl to query rows from the data base in order to draw cell values. In other words, while a query to the data base is being executed, RemObjects.SDK.SuperHttpClientChannelWorker forces the GridControl to execute a new query. A correct way to overcome this is to prevent RemObjects.SDK.SuperHttpClientChannelWorker from sending WM_Messages while data is being loaded from the server.
I would recommend sharing the below callstack with the RemObjects support team so they could address this issue:

        DevExpress.Utils.v18.1.dll!DevExpress.Utils.Drawing.ControlPaintHelper.PaintWithErrorHandling(System.Windows.Forms.PaintEventArgs paintEventArgs, int layer)  
        DevExpress.Utils.v18.1.dll!DevExpress.Utils.Drawing.ControlPaintHelper.ProcessWMPaintCore(bool beginPaint, System.IntPtr hdc = 0x0c0122d9, System.Drawing.Rectangle rectangle, System.Drawing.Rectangle[] clipBounds)  
        DevExpress.Utils.v18.1.dll!DevExpress.Utils.Drawing.ControlPaintHelper.ProcessWMPaint(ref System.Windows.Forms.Message m)  
        DevExpress.XtraGrid.v18.1.dll!DevExpress.XtraGrid.GridControl.CheckProcessMsg(ref System.Windows.Forms.Message m)  
        DevExpress.XtraGrid.v18.1.dll!DevExpress.XtraGrid.GridControl.WndProc(ref System.Windows.Forms.Message m = {System.Windows.Forms.Message})  
        System.Windows.Forms.dll!System.Windows.Forms.Control.ControlNativeWindow.OnMessage(ref System.Windows.Forms.Message m)  
        System.Windows.Forms.dll!System.Windows.Forms.Control.ControlNativeWindow.WndProc(ref System.Windows.Forms.Message m)  
        System.Windows.Forms.dll!System.Windows.Forms.NativeWindow.DebuggableCallback(System.IntPtr hWnd, int msg = 15, System.IntPtr wparam, System.IntPtr lparam)  
        [Transizione da nativo a gestito]  
        [Transizione da gestito a nativo]  
        mscorlib.dll!System.Threading.WaitHandle.InternalWaitOne(System.Runtime.InteropServices.SafeHandle waitableSafeHandle, long millisecondsTimeout, bool hasThreadAffinity, bool exitContext)  
        mscorlib.dll!System.Threading.WaitHandle.WaitOne(int millisecondsTimeout, bool exitContext)  
        RemObjects.SDK.dll!RemObjects.SDK.SuperHttpClientChannelWorker.DispatchMessage(System.IO.Stream request, RemObjects.SDK.IMessage response = {RemObjects.SDK.BinMessage}) Riga 560  
        RemObjects.SDK.dll!RemObjects.SDK.SuperHttpClientChannel.IntDispatch(System.IO.Stream request, RemObjects.SDK.IMessage response) Riga 297  
        RemObjects.SDK.dll!RemObjects.SDK.ClientChannel.Dispatch(RemObjects.SDK.IMessage message = {RemObjects.SDK.BinMessage}) Riga 314  
        RemObjects.SDK.dll!RemObjects.SDK.DynamicRequest.InternalMakeRequest() Riga 166  
        RemObjects.SDK.dll!RemObjects.SDK.DynamicRequest.MakeRequest() Riga 182  
        RemObjects.DataAbstract.dll!RemObjects.DataAbstract.Linq.LinqRemoteDataAdapter.FetchData(RemObjects.DataAbstract.Server.TableRequestInfo[] tableRequest = {RemObjects.DataAbstract.Server.TableRequestInfo[1]}, string[] tableNames = {string[1]}, System.Action<int, int[], System.Data.IDataReader> fillMethod = {Method = {System.Reflection.RuntimeMethodInfo}}) Riga 423  
        RemObjects.DataAbstract.dll!RemObjects.DataAbstract.Linq.RemoteTable<_xxx_Controller._xxx_PlanControllerDataset.generic_log_connection>.Execute(System.Linq.Expressions.Expression expression, RemObjects.DataAbstract.Server.DataParameter[] parameters) Riga 296  
        RemObjects.DataAbstract.dll!RemObjects.DataAbstract.Linq.RemoteTable<System.__Canon>.Execute(System.Linq.Expressions.Expression expression) Riga 265  
        RemObjects.DataAbstract.dll!RemObjects.DataAbstract.Linq.RemoteTableQuery<System.__Canon>.GetEnumerator2() Riga 80  
        DevExpress.Data.v18.1.dll!DevExpress.Data.Linq.Helpers.LinqServerModeCache.FetchRows(DevExpress.Data.Filtering.CriteriaOperator where, DevExpress.Data.ServerModeOrderDescriptor[] order, int take)

If you need I can give you (in private way) complete callstacks of threads and a project source of a winform client that point to a remote server for reproduce this issue.

The thing is SuperHttpClientChannelWorker has no code that anyhow interact with GUI and/or sends Win messages like WM_Paint and/or interact with the Windows message queue.

Actually try to change the server and client channel type to plain Http (aka IpHttpClientChannel and IpHttpServerChannel ) and retry your testcase (by its very nature IpHttpClientChannel cannot execute a new query until the currently running one is finished).

I you could send a simple client app to support@ (everything sent there is kept private) that would be good.

Hello

While IpSuperHttpClientChannel doesn’t call the WM_PAINT message by itself, it uses a WaitHandle.WaitOne call while sending a database request message to the server.

However depending on application settings Windows messages are still processing during the WaitOne call. This triggers the DevEx grid thread safety watchdog (it thinks that something tries to repaint it during the data request call).

The solution that worked for me in your testcase was to change

static class Program
{
    /// <summary>
    /// The main entry point for the application.
    /// </summary>
    [STAThread]
    static void Main()

to

static class Program
{
    /// <summary>
    /// The main entry point for the application.
    /// </summary>
    [MTAThread]
    static void Main()

You can read more about different apartments models in Apartments and Pumping in the CLR | Microsoft Learn

If for some reason you cannot change model from STA to MTA then the only resolution would be to try to use a different client channel type, namely IpHttpClientChannel. Note that in this case you will NOT need to do any changes to the server except of setting the ServePlainHttpRequests property of the server channel to true. This property will allow the SuperHttp server channel to serve both SuperHttp and plain Http request protocols

Regards

Thanks, using MTAThread seems works well.

You saved my day.

Glad that it helped.

Could you also add this to your DevEx support ticket:

"
It seems that if STAThread threading model is used by the application then if a 3rd party data source contains a WaitHandle.WaitOne call then while this call is processed (ie the data source waits for an answer from the server) it is quite possible that the Windows will raise the OnPaint event.

Unfortunately the DevEx Grid will raise an “Reentry or race condition detected” exception in this event handler because it will consider it as an event raised by the data source and thus violating the thread safety conditions.
"

Would be great if DevEx add some kind of support for such cases (after all even a ADO.NET driver can contain WaitOne calls)

Hello, i have replied to devexpress support ticket:

Thank you for sharing this information with us. I should say that we are not planning to handle this case on our side since it is not recommended to call the WaitOne method or similar methods blocking a thread from an app with the STAThread attribute. Starting with version 18.1.5, we have added a static ServerModeCache.DefaultForceStaSafeForReentryProtected property that should address the issue. We introduced this property as a workaround.

Regarding MTAThread, I should note that several important features like Clipboard and drag&drop will stop working after registering your app with this attribute.

I have tested all project parts, i found that MTAThread works well for this grid but give exception with other features as scheduler drag&drop, so I can’t use this solution:

System.InvalidOperationException: Registrazione DragDrop non riuscita. —> System.Threading.ThreadStateException: Prima di effettuare chiamate OLE, occorre impostare il thread corrente sulla modalità Single Thread Apartment (STA). Assicurarsi che la funzione Main sia contrassegnata con STAThreadAttribute.
in System.Windows.Forms.Control.SetAcceptDrops(Boolean accept)
— Fine della traccia dello stack dell’eccezione interna —
in System.Windows.Forms.Control.SetAcceptDrops(Boolean accept)
in System.Windows.Forms.Control.OnHandleCreated(EventArgs e)
in DevExpress.XtraScheduler.SchedulerControl.OnHandleCreated(EventArgs e)
in System.Windows.Forms.Control.WmCreate(Message& m)
in System.Windows.Forms.Control.WndProc(Message& m)
in DevExpress.XtraScheduler.SchedulerControl.WndProc(Message& m)
in System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
in System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
in System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Due to its very nature SuperHttp channel core cannot be implemented without these thread synchronization primitives.

Did that newly introduced property help in your case?

Or you can just consider to use IpHttpClientChannel on the client side instead of IpSuperHttpClientChannel (no chenges on the server-side are required except of allowing plain http requests via corresponding server channel property)

I am waiting release of devexpress 18.1.5 for testing this property, but they say it is only a temporary workaround.

Unfortunately in test project i have used only the features related to the issue, but the main project that I am developing is (really) not simple and almost finished, i am already using IpHttpClientChannel in a NET Client for secondary communications and in a server for communicate with another for standalone request, but the bahavior of this two channel types that i see is different.

There are short description in documentation pages for understanding necessary changes in my project:
https://docs.remotingsdk.com/API/NET/Classes/IpSuperHttpClientChannel/
https://docs.remotingsdk.com/API/NET/Classes/IpHttpClientChannel/

On starting development I have choose IpSuperHttpClientChannel for .NET clients, I think change it now can be very expensive, but if I have no choice i will try it.

Actually appolication can use more than 1 channel type to communicate with the same server. Mean, you can use IpSuperHttp for all server communications and at the same time use IpHttp for data adapters that provide data for DevEx components.

These connections will use the same server session if you’ll use the same BinMessage instance. Session used on the server is determined by the message ClientD property (it matches the server-side SessionID)