Devart Forums

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

dotConnect for Zoho CRM - Linq to Entities Broken

https://forums.devart.com/viewtopic.php?f=30&t=39703

Page 1 of 1

dotConnect for Zoho CRM - Linq to Entities Broken

Posted: Fri 20 Dec 2019 18:40
by bob_vidabox
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

Re: dotConnect for Zoho CRM - Linq to Entities Broken

Posted: Mon 23 Dec 2019 19:42
by bob_vidabox
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.

Re: dotConnect for Zoho CRM - Linq to Entities Broken

Posted: Wed 25 Dec 2019 18:52
by Shalex
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.

Re: dotConnect for Zoho CRM - Linq to Entities Broken

Posted: Wed 29 Jan 2020 19:17
by Shalex

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.

Re: dotConnect for Zoho CRM - Linq to Entities Broken

Posted: Sat 08 Feb 2020 19:40
by bob_vidabox
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.

Re: dotConnect for Zoho CRM - Linq to Entities Broken

Posted: Tue 18 Feb 2020 13:49
by Shalex

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.

Re: dotConnect for Zoho CRM - Linq to Entities Broken

Posted: Mon 02 Mar 2020 15:33
by Shalex
Filtering lookup fields is supported in v1.10.1098.
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/