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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Cloud Applications
Post Reply
nbcol
Posts: 5
Joined: Mon 19 Nov 2018 08:23

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

Post by nbcol » Wed 28 Nov 2018 11:47

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
Posts: 5
Joined: Mon 19 Nov 2018 08:23

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

Post by nbcol » Thu 29 Nov 2018 14:00

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
Posts: 5
Joined: Mon 19 Nov 2018 08:23

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

Post by nbcol » Thu 29 Nov 2018 14:57

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
Site Admin
Posts: 8786
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Mon 03 Dec 2018 20:22

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
Posts: 4
Joined: Fri 30 Nov 2018 08:07

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

Post by bco » Tue 04 Dec 2018 09:56

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
Posts: 4
Joined: Fri 30 Nov 2018 08:07

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

Post by bco » Tue 04 Dec 2018 10:15

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
Site Admin
Posts: 8786
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Wed 05 Dec 2018 11:41

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
Posts: 4
Joined: Fri 30 Nov 2018 08:07

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

Post by bco » Wed 05 Dec 2018 16:47

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
Site Admin
Posts: 8786
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Wed 12 Dec 2018 16:38

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.

Post Reply