Query to Salesforce raises EntityCommandExecutionException due to 'Unknown column' although column is defined in EDML

[nbcol]

Hello,

I defined a local model representing Salesforce using Devart's EntityDeveloper. We are able to update the model when the source changes. New columns are added to the model.

A query is defined:

Code: Select all

context.SubCommissions.Select(x => x.InvoiceNo_SubCommission).ToList()

When I try to query the source using the new column I receive the following exceptions:

• Outer exception is EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.

• Inner exception is InvalidOperationException: Unknown column 'Extent1.InvoiceNo_SubCommission'.

Stacktrace is:

at Devart.Data.Salesforce.ai.a.c(String A_0, String A_1)
at Devart.Data.Salesforce.ai.a.a()
at Devart.Data.Salesforce.ai.a.c(SqlSelect A_0)
at Devart.Data.Salesforce.ai.a(a A_0, SqlSelect A_1)
at Devart.Data.Salesforce.i.a(SqlSelect A_0, ParameterInfo[] A_1, Boolean A_2)
at Devart.Data.Salesforce.i..ctor(ab A_0, Boolean A_1, SqlSelect A_2, ParameterInfo[] A_3, Boolean A_4)
at Devart.Data.Salesforce.g.a(Object A_0, Boolean A_1, String A_2, Boolean A_3, ParameterInfo[] A_4)
at Devart.Data.Salesforce.SalesforceCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.Salesforce.Entity.d.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

I' running dbmonitor while running the query. The last statement before the exception is

Prepare: SELECT 1 AS C1, Extent1.Id AS Id, Extent1.Invoice AS Invoice, Extent1.Name AS Name, Extent1.Company AS Company, Extent1.ContactPerson AS ContactPerson, Extent1.Classification AS Classification, Extent1.AmountNet AS AmountNet, Extent1.InvoiceNo_SubCommission AS InvoiceNo_SubCommission FROM SubCommissions AS Extent1 WHERE NOT (0 = Extent1.AmountNet)

The statement is highlighted red.

Using Fiddler I can see that the statement is never sent to Salesforce. Devart seems to determine that the statement is invalid and raises an exception.

I tried to modify and run the offending statement in the Salesforce Developer Console (adding __C) and it's run without an error.

thank you for your support!

[nbcol]

Hello,

the same problem is now appearing with the system objects.

First I changed the connectionstring to include the system objects (System Objects=True; in connectionstring). Then I updated the schema from the database. The system columns of the Salesforce objects werde imported into the edml model. The classes representing the cutom objects became visible. I complied the solution without an error. When I try and use the generated assembly in a query, I get the same error as above.

Best

[nbcol]

Hello,

In Visual Studio, using the Model Explorer/Model/Classes I am able to click "Retrieve Data". The data of the table is then displayed including the exact column that causes the exception I describe above.
Using Fiddler, I am able to capture the exact SOQL query that Devart is generating in Visual Studio. That query has the column name exactly as specified in the error message: Unknown column 'CreatedDate'.
The SOQL query is
SELECT CreatedDate FROM Brand__c
Salesforce has no issue executing the query.

So using DbMonitor, I can see that the query is not being rejected by Salesforce, but by Devart in the Prepare phase.

I would really appreciate it if you help us solving this problem. Salesforce access without the system objects (for example CreatedDate) is needed for us to make use of the data retrieved.

Thank you

[Shalex]

Looks your local metadata cache is out-of-date: https://www.devart.com/dotconnect/sales ... rview.html. In this case, there are alternative ways to solve the issue:

1) execute the code:

Code: Select all

    SalesforceConnection conn = new SalesforceConnection(connString);
    conn.Open();
    conn.Cache.RefreshMetadata();

2) the "Refresh Metadata=true;" connection string parameter causes a refresh of Salesforce metadata each time when the connection is opened. Leads to the performance loses

3) remove the C:\Users\YOUR_OS_USER_NAME\AppData\Roaming\*.db files. dotConnect for Salesforce will recreate them on the next connection.Open()

If conn.Cache.RefreshMetaData() doesn't help, please open your *_metadata.db with some tool (e.g.: SQLite Expert) and make sure that the columns InvoiceNo_SubCommission and CreatedDate exist in the result set of the query "SELECT TABLE_NAME, COLUMN_NAME, API_NAME FROM SYS_COLUMNS".

[bco]

Thanks for your help, but it`s still not working:
In the connection string we set "Refresh Metadata=true;" , and we deleted the *.db files in the AppRoaming folder as you suggested. Nevertheless, after running the query and recreating the db-files the fields added to the .edml model still are missing.

[bco]

Just for clarifying: Some system fields like CreatedDate or CreatedById still are missing, allthough they are visible in .edml. We added "System Objects=True;" in the connection string already. Another field we added to the model is queryable after recreating the *.db files.

[Shalex]

Please open your C:\Users\YOUR_OS_USER_NAME\AppData\Roaming\*_metadata.db database file with some tool (e.g.: SQLite Expert) and make sure that the columns CreatedDate and CreatedById exist in the result set of the query "SELECT TABLE_NAME, COLUMN_NAME, API_NAME FROM SYS_COLUMNS".

[bco]

After removing the *_metadata.db files and running the script the files have been recreated. The missing system fields still are not existing in the metadata.db files, that we've analyzed by using SQLite.

[Shalex]

We have followed these steps:

1) removed the files
C:\Users\OS_USER_NAME\AppData\Roaming\*.db
C:\Users\OS_USER_NAME\AppData\Roaming\*_metadata.db

2) ran the code

Code: Select all

    using (SalesforceConnection conn = new SalesforceConnection())
    {
        conn.UserId = "***";
        conn.Password = "***";
        conn.SecurityToken = "***";
        conn.ConnectionString += "System Objects=true;";
        conn.Open();
        conn.Cache.RefreshMetadata();
    }

3) opened C:\Users\OS_USER_NAME\AppData\Roaming\*_metadata.db and executed "SELECT TABLE_NAME, COLUMN_NAME, API_NAME FROM SYS_COLUMNS".

The result set includes system columns (e.g.: Account.CreatedDate).

Please do exactly the same steps. If this doesn't help, specify the names of missing columns with names of Salesforce objects where you are looking these columns.