dotConnect for Zoho CRM - Linq to Entities Broken
-
- Posts: 9
- Joined: Fri 13 Dec 2019 00:31
dotConnect for Zoho CRM - Linq to Entities Broken
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
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
-
- Posts: 9
- Joined: Fri 13 Dec 2019 00:31
Re: dotConnect for Zoho CRM - Linq to Entities Broken
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
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
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
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
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
-
- Posts: 9
- Joined: Fri 13 Dec 2019 00:31
Re: dotConnect for Zoho CRM - Linq to Entities Broken
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
...
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..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
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
...
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
...
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
The SQL is: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?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
Code: Select all
SELECT ... FROM Products AS Extent1 WHERE 'VB_VESA_AIR_BLK' = Extent1."Product Name" LIMIT 2
With context.Configuration.UseDatabaseNullSemantics=true; the SQL would be: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.
Code: Select all
SELECT ... FROM Products AS Extent1 WHERE Extent1."Product Name" = :p__linq__0 LIMIT 2
Filtering lookup fields is supported. We will notify you when the new public build of dotConnect for Zoho CRM is available for download.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.
Re: dotConnect for Zoho CRM - Linq to Entities Broken
Filtering lookup fields is supported in v1.10.1098.