Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

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

https://forums.devart.com/viewtopic.php?f=38&t=38110

Page 1 of 1

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

Posted: Wed 28 Nov 2018 11:47
by 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!

Re: Query to Salesforce raises EntityCommandExecutionException due to 'Unknown column' although column is defined in EDM

Posted: Thu 29 Nov 2018 14:00
by 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

Re: Query to Salesforce raises EntityCommandExecutionException due to 'Unknown column' although column is defined in EDM

Posted: Thu 29 Nov 2018 14:57
by 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

Re: Query to Salesforce raises EntityCommandExecutionException due to 'Unknown column' although column is defined in EDM

Posted: Mon 03 Dec 2018 20:22
by 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".

Re: Query to Salesforce raises EntityCommandExecutionException due to 'Unknown column' although column is defined in EDM

Posted: Tue 04 Dec 2018 09:56
by 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.

Re: Query to Salesforce raises EntityCommandExecutionException due to 'Unknown column' although column is defined in EDM

Posted: Tue 04 Dec 2018 10:15
by 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.

Re: Query to Salesforce raises EntityCommandExecutionException due to 'Unknown column' although column is defined in EDM

Posted: Wed 05 Dec 2018 11:41
by 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".

Re: Query to Salesforce raises EntityCommandExecutionException due to 'Unknown column' although column is defined in EDM

Posted: Wed 05 Dec 2018 16:47
by 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.

Re: Query to Salesforce raises EntityCommandExecutionException due to 'Unknown column' although column is defined in EDM

Posted: Wed 12 Dec 2018 16:38
by 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.
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/