'Where' condition is not passed to Salesforce

[vitalii]

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]

We cannot reproduce the problem in our environment.

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]

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 == "[email protected]");
}

will generate this SQL in dbMonitor:

Code: Select all

SELECT Extent1.Id AS Id, Extent1.Email AS Email, ... FROM User AS Extent1 WHERE '[email protected]' = 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 = '[email protected]' 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 = "[email protected]";
    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, [email protected]

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]

Code: Select all

using (var ctx = new SalesforceEntities())
{
    var email = "[email protected]";
    var user = ctx.Users.SingleOrDefault(x => x.Email == email);
}

Your LINQ query above is translated into 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) 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:

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]

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]

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 = "[email protected]";
    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]

Thank you, the workaround has resolved my issue