'Where' condition is not passed to Salesforce

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Cloud Applications
Post Reply
vitalii
Posts: 4
Joined: Fri 24 Jan 2014 08:55

'Where' condition is not passed to Salesforce

Post by vitalii » Fri 24 Jan 2014 09:08

Hi

After upgrading our dotConnect for Salesforce from 2.0 to 2.4 and EntityFramework from 5 to 6 some of the queries that we use have changed their behavior. For example,

Code: Select all

var user = dbContext.Users.Where(x => x.Email == 'xxx').FirstOrDefault();
will now get translated to something like

Code: Select all

SELECT Id, ....  FROM User
without the condition stated in 'Where' clause. This effectively starts batch-downloading all User objects in Salesforce which is extremely slow. Before the upgrade this query worked perfectly well:

Code: Select all

SELECT Id, ....  FROM User WHERE Email = 'xxx'
Can you please suggest what can be the reason for this and how I can fix it?
Thanks

Shalex
Site Admin
Posts: 9035
Joined: Thu 14 Aug 2008 12:44

Re: 'Where' condition is not passed to Salesforce

Post by Shalex » Fri 24 Jan 2014 14:56

We cannot reproduce the problem in our environment.
vitalii wrote:

Code: Select all

var user = dbContext.Users.Where(x => x.Email == 'xxx').FirstOrDefault();
will now get translated to something like

Code: Select all

SELECT Id, ....  FROM User
Please turn on the dbMonitor tool and make sure that EF-provider actually generates the query without the WHERE clause:
http://www.devart.com/dotconnect/salesf ... nitor.html
http://www.devart.com/dbmonitor/dbmon3.exe

If this confirms the cause of the problem, send us a small test project for reproducing.

vitalii
Posts: 4
Joined: Fri 24 Jan 2014 08:55

Re: 'Where' condition is not passed to Salesforce

Post by vitalii » Mon 27 Jan 2014 14:32

Hi
The queries that I posted were taken by Fiddler so that's basically the raw web service calls to Salesforce - after all providers have worked their magic. I have narrowed down the possible cause to this difference:
1. Code like

Code: Select all

using (var ctx = new SalesforceEntities())
{
    var user = ctx.Users.SingleOrDefault(x => x.Email == "bashirasad@syria.com");
}
will generate this SQL in dbMonitor:

Code: Select all

SELECT Extent1.Id AS Id, Extent1.Email AS Email, ... FROM User AS Extent1 WHERE 'bashirasad@syria.com' = Extent1.Email LIMIT 2
which in turn will send the correct query to Salesforce WITH the WHERE clause:

Code: Select all

<queryString>SELECT Id, Email, ... FROM User WHERE Email = 'bashirasad@syria.com' LIMIT 2</queryString>
2. Now, if I put the email into a variable instead of constant:

Code: Select all

using (var ctx = new SalesforceEntities())
{
    var email = "bashirasad@syria.com";
    var user = ctx.Users.SingleOrDefault(x => x.Email == email);
}
it will generate this SQL in dbMonitor:

Code: Select all

SELECT Extent1.Id AS Id, Extent1.Email AS Email, ... FROM User AS Extent1 WHERE (Extent1.Email = :p__linq__0) AND (NOT (:p__linq__0 IS NULL)) LIMIT 2
with parameter p__linq__0, Input, String, bashirasad@syria.com

This will send a request to Salesforce WITHOUT the WHERE clause:

Code: Select all

<queryString>SELECT Id, Email, ... FROM User</queryString>
Can you please try to reproduce this issue or will I need to supply a test project?
Thanks

Shalex
Site Admin
Posts: 9035
Joined: Thu 14 Aug 2008 12:44

Re: 'Where' condition is not passed to Salesforce

Post by Shalex » Tue 28 Jan 2014 16:49

vitalii wrote:

Code: Select all

using (var ctx = new SalesforceEntities())
{
    var email = "bashirasad@syria.com";
    var user = ctx.Users.SingleOrDefault(x => x.Email == email);
}
Your LINQ query above is translated into the following SQL:
vitalii wrote:

Code: Select all

SELECT Extent1.Id AS Id, Extent1.Email AS Email, ... FROM User AS Extent1 WHERE (Extent1.Email = :p__linq__0) AND (NOT (:p__linq__0 IS NULL)) LIMIT 2
The SQL query above is translated by dotConnect for Salesforce into the following SOQL which will be executed in salesforce.com:
vitalii wrote:

Code: Select all

<queryString>SELECT Id, Email, ... FROM User</queryString>
The WHERE clause is omitted if provider cannot translate it in SOQL for some reason. So all records are loaded in the local cache (SQLite database file). After this, the SQL query (with WHERE clause) is applied for SQLite database. Your code should obtain an expected result with one record where x.Email == email.

vitalii
Posts: 4
Joined: Fri 24 Jan 2014 08:55

Re: 'Where' condition is not passed to Salesforce

Post by vitalii » Tue 28 Jan 2014 17:03

Shalex wrote:The WHERE clause is omitted if provider cannot translate it in SOQL for some reason.
Can we determine a reason why this happens? It worked perfectly fine before the upgrade. Loading all records locally is not an option for performance reasons.

Shalex
Site Admin
Posts: 9035
Joined: Thu 14 Aug 2008 12:44

Re: 'Where' condition is not passed to Salesforce

Post by Shalex » Thu 30 Jan 2014 14:48

vitalii wrote:Can we determine a reason why this happens? It worked perfectly fine before the upgrade.
The difference is caused by Entity Framework version used in your project.

EFv5 generates expression tree which translated by our provider in the following SQL:

Code: Select all

SELECT Extent1.Id AS Id, Extent1.Email AS Email, ... FROM User AS Extent1 WHERE Extent1.Email = :p__linq__0 LIMIT 2
The expression tree of EFv6.0.2 includes an additional condition which results in generating extra AND (NOT (:p__linq__0 IS NULL)) in SQL:

Code: Select all

SELECT Extent1.Id AS Id, Extent1.Email AS Email, ... FROM User AS Extent1 WHERE (Extent1.Email = :p__linq__0) AND (NOT (:p__linq__0 IS NULL)) LIMIT 2
The problem is that SOQL allows only fieldName on the first place in the fieldExpression. Thats why provider cannot transate this WHERE clause of SQL into WHERE clause of SOQL.

As a workaround for EFv6.0.2, set UseDatabaseNullSemantics = true (to avoid applying an additional condition):

Code: Select all

using (var ctx = new SalesforceEntities())
{
    var email = "bashirasad@syria.com";
    ctx.Configuration.UseDatabaseNullSemantics = true;
    var user = ctx.Users.SingleOrDefault(x => x.Email == email);
}
The issue with an additional condition in expression tree is fixed in the newer (6.1.0-alpha1) version of Entity Framework.

So please use a workaround with EFv6.0.2 or upgrade to EFv6.1.0-alpha1.

vitalii
Posts: 4
Joined: Fri 24 Jan 2014 08:55

Re: 'Where' condition is not passed to Salesforce

Post by vitalii » Fri 31 Jan 2014 14:43

Thank you, the workaround has resolved my issue

Post Reply