dotConnect for Zoho CRM - Linq to Entities Broken

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
bob_vidabox
Posts: 9
Joined: Fri 13 Dec 2019 00:31

dotConnect for Zoho CRM - Linq to Entities Broken

Post by bob_vidabox » Fri 20 Dec 2019 18:40

Finding issues with Linq to Entities similar to this topic. Using IQueryable extension methods for string properties gives inconsistent behavior depending on if a string literal or variable is used. For some string properties that are lookup fields it doesn't matter if a literal or variable is used for comparison. For numeric properties all are problematic.

In cases where it is broken it is acting like context.Entity.AsEnumerable() or context.Entity.Select(e=>e.property).AsEnumerable() and using Linq to Objects on the returned data set. A few examples are shown below, but this behavior is seen in all other extension methods as well. All examples should be able to be translated to a simple search records using an equals criteria as in the first example.

Query / Api Requests
var product = await context.Products.SingleOrDefaultAsync(p => p.Product_Code == "VB_VESA_AIR_BLK");
...
GET /crm/v2/Products/search?criteria=(Product_Code:equals:VB_VESA_AIR_BLK)&per_page=2&page=1


Query / Api Requests
var code = "VB_VESA_AIR_BLK";
var product = await context.Products.SingleOrDefaultAsync(p => p.Product_Code == code);
...
GET /crm/v2/Products?per_page=200&page=1
GET /crm/v2/Products?per_page=200&page=2
GET /crm/v2/Products?per_page=200&page=3
...


Query / Api Requests
var products = await context.Products.Where(p =>p.Owner == 1234).ToListAsync();
...
GET /crm/v2/Products?per_page=200&page=1
GET /crm/v2/Products?per_page=200&page=2
GET /crm/v2/Products?per_page=200&page=3
...


Query / Api Requests
var isModifiedByMe = await context.Products.AnyAsync(p => p.Modified_By_Name == "John Smith");
...
GET /crm/v2/Products?fields=Modified_By&per_page=200&page=1
GET /crm/v2/Products?fields=Modified_By&per_page=200&page=2
GET /crm/v2/Products?fields=Modified_By&per_page=200&page=3
...



version: 1.9.1034
EF provider: Devart.Data.Zoho.Entity.EF6

bob_vidabox
Posts: 9
Joined: Fri 13 Dec 2019 00:31

Re: dotConnect for Zoho CRM - Linq to Entities Broken

Post by bob_vidabox » Mon 23 Dec 2019 19:42

Any response to this. There is basically no EF support for this product, at least for EF6. I would guess this is an issue for all versions though since it appears to be a fundamental flaw in how expression trees are converted to api requests.

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

Re: dotConnect for Zoho CRM - Linq to Entities Broken

Post by Shalex » Wed 25 Dec 2019 18:52

We will check SQL-92 statements generated by EF6 provider and the corresponding API requests sent by ADO.NET provider and notify you about the result.

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

Re: dotConnect for Zoho CRM - Linq to Entities Broken

Post by Shalex » Wed 29 Jan 2020 19:17

Code: Select all

    var code = "VB_VESA_AIR_BLK";
    var result2 = context.Products.SingleOrDefault(p => p.ProductName == code);

->

SELECT ... FROM Products AS Extent1 WHERE (Extent1."Product Name" = :p__linq__0) OR ((Extent1."Product Name" IS NULL) AND (:p__linq__0 IS NULL)) LIMIT 2

->

GET /crm/v2/Products?per_page=200&page=1
result2: Zoho API doesn't allow comparing to NULL, so you should avoid using NULL comparison in the generated SQL. In EF6, you can do that with ObjectContextOptions.UseCSharpNullComparison=false:

Code: Select all

            using (var context = new Entities())
            {
                ((IObjectContextAdapter)context).ObjectContext.ContextOptions.UseCSharpNullComparisonBehavior = false;

                var code = "VB_VESA_AIR_BLK";
                var result2_updated = context.Products.SingleOrDefault(p => p.ProductName == code);
            }

->

SELECT ... FROM Products AS Extent1 WHERE Extent1."Product Name" = :p__linq__0 LIMIT 2

->

GET /crm/v2/Products/search?criteria=(Product_Name:equals:VB_VESA_AIR_BLK)&per_page=2&page=1

Code: Select all

    var result3 = context.Products.Where(p => p.Owner == 1234).ToList();

->

SELECT ... FROM Products AS Extent1 WHERE 1234 = Extent1.Owner

->

GET /crm/v2/Products?per_page=200&page=1
result3: No way to search by OwnerLookup via Zoho API.

Code: Select all

    var result4 = context.Products.Any(p => p.ProductName == "John Smith");

->

SELECT CASE WHEN NOT (NOT (EXISTS (SELECT 1 AS C1 FROM Products AS Extent1 WHERE 'John Smith' = Extent1."Product Name"))) THEN 1 ELSE 0 END AS C1 FROM (SELECT 1) AS SingleRowTable1

->

GET /crm/v2/Products?fields=Product_Name&per_page=200&page=1
result4: Too complex SQL to translate it to API call.

bob_vidabox
Posts: 9
Joined: Fri 13 Dec 2019 00:31

Re: dotConnect for Zoho CRM - Linq to Entities Broken

Post by bob_vidabox » Sat 08 Feb 2020 19:40

bob_vidabox wrote:
Fri 20 Dec 2019 18:40

Query / Api Requests
var product = await context.Products.SingleOrDefaultAsync(p => p.Product_Code == "VB_VESA_AIR_BLK");
...
GET /crm/v2/Products/search?criteria=(Product_Code:equals:VB_VESA_AIR_BLK)&per_page=2&page=1

Query / Api Requests
var code = "VB_VESA_AIR_BLK";
var product = await context.Products.SingleOrDefaultAsync(p => p.Product_Code == code);
...
GET /crm/v2/Products?per_page=200&page=1
GET /crm/v2/Products?per_page=200&page=2
GET /crm/v2/Products?per_page=200&page=3
...
Shalex wrote:
Wed 29 Jan 2020 19:17
result2: Zoho API doesn't allow comparing to NULL, so you should avoid using NULL comparison in the generated SQL. In EF6, you can do that with ObjectContextOptions.UseCSharpNullComparison=false:

Code: Select all

            using (var context = new Entities())
            {
                ((IObjectContextAdapter)context).ObjectContext.ContextOptions.UseCSharpNullComparisonBehavior = false;

                var code = "VB_VESA_AIR_BLK";
                var result2_updated = context.Products.SingleOrDefault(p => p.ProductName == code);
            }

->

SELECT ... FROM Products AS Extent1 WHERE Extent1."Product Name" = :p__linq__0 LIMIT 2

->

GET /crm/v2/Products/search?criteria=(Product_Name:equals:VB_VESA_AIR_BLK)&per_page=2&page=1
The first query using the same method doesn't have an issue when using a string literal. What is the SQL being generated for this method? The SQL is being generated by the provider so I would argue that there is an issue with the generated SQL. Dropping down to the ObjectContext is really something that shouldn't be required for a simple request such as this. The simpler way to go would just be to avoid using OrDefault on any requests and create nulls based on whether something is returned or not. I have moved past using the dotConnector so don't have the setup to test if there are the same issues with using a string literal vs a variable for the equality parameter in a Single request..


bob_vidabox wrote:
Fri 20 Dec 2019 18:40
Query / Api Requests
var products = await context.Products.Where(p =>p.Owner == 1234).ToListAsync();
...
GET /crm/v2/Products?per_page=200&page=1
GET /crm/v2/Products?per_page=200&page=2
GET /crm/v2/Products?per_page=200&page=3
...
Shalex wrote:
Wed 29 Jan 2020 19:17
result3: No way to search by OwnerLookup via Zoho API.
This is just not correct. The Owner field is a lookup field. All lookup fields(Owner, Created_By, Modified_By,Custom_Lookup, etc.) can be queried via the API using the Id. For example

Code: Select all

GET https://www.zohoapis.com/crm/v2/Products/search?criteria=(Owner:equals:12345678)
GET https://www.zohoapis.com/crm/v2/Products/search?criteria=(Created_By:equals:12345678)


bob_vidabox wrote:
Fri 20 Dec 2019 18:40
Query / Api Requests
var isModifiedByMe = await context.Products.AnyAsync(p => p.Modified_By_Name == "John Smith");
...
GET /crm/v2/Products?fields=Modified_By&per_page=200&page=1
GET /crm/v2/Products?fields=Modified_By&per_page=200&page=2
GET /crm/v2/Products?fields=Modified_By&per_page=200&page=3
...
Shalex wrote:
Wed 29 Jan 2020 19:17
result4: Too complex SQL to translate it to API call.
Again I would argue that if the SQL being generated is too complex that the issue lies with the provider. There are other ways the information being requested could be gotten such as using Single, but the potential issues as shown in the first example may exist.

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

Re: dotConnect for Zoho CRM - Linq to Entities Broken

Post by Shalex » Tue 18 Feb 2020 13:49

Code: Select all

var product = await context.Products.SingleOrDefaultAsync(p => p.Product_Code == "VB_VESA_AIR_BLK");
...
GET /crm/v2/Products/search?criteria=(Product_Code:equals:VB_VESA_AIR_BLK)&per_page=2&page=1
The first query using the same method doesn't have an issue when using a string literal. What is the SQL being generated for this method?
The SQL is:

Code: Select all

SELECT ... FROM Products AS Extent1 WHERE 'VB_VESA_AIR_BLK' = Extent1."Product Name" LIMIT 2
You can trace the generated SQL with the dbMonitor tool.
The SQL is being generated by the provider so I would argue that there is an issue with the generated SQL. Dropping down to the ObjectContext is really something that shouldn't be required for a simple request such as this. The simpler way to go would just be to avoid using OrDefault on any requests and create nulls based on whether something is returned or not.
With context.Configuration.UseDatabaseNullSemantics=true; the SQL would be:

Code: Select all

SELECT ... FROM Products AS Extent1 WHERE Extent1."Product Name" = :p__linq__0 LIMIT 2
Also, it is better to use FirstOrDefault() instead of SingleOrDefault() to read the only object.
The Owner field is a lookup field. All lookup fields(Owner, Created_By, Modified_By,Custom_Lookup, etc.) can be queried via the API using the Id.
Filtering lookup fields is supported. We will notify you when the new public build of dotConnect for Zoho CRM is available for download.

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

Re: dotConnect for Zoho CRM - Linq to Entities Broken

Post by Shalex » Mon 02 Mar 2020 15:33

Filtering lookup fields is supported in v1.10.1098.

Post Reply