I am using entity framework 6, dotConnect MySql 8.3.407.6.
Here is LINQ query I am running from C# app:
Code: Select all
processedDocumentIDs = db.ProcessedDocuments
.Where(o => o.IsForwardResponded == DBBool.False
&& o.IsReadyToRespond == DBBool.True)
.Select(o => new { o.ProcessedDocumentID, o.ReceivedDocument.OwnerInstanceID })
.Where(o => o.OwnerInstanceID == InstanceContext.Instance.RunningInstanceID || o.OwnerInstanceID == null)
.Select(o => o.ProcessedDocumentID)
.Take(100000)
.ToList();
Code: Select all
public DBBool IsForwardResponded { get; set; }
public DBBool IsReadyToRespond { get; set; }
Code: Select all
public enum DBBool : byte
{
[Display(Name = "No")]
False = 0,
[Display(Name = "Yes")]
True = 1
}
Code: Select all
IsForwardResponded TINYINT UNSIGNED NOT NULL,
IsReadyToRespond TINYINT UNSIGNED DEFAULT 0 NOT NULL,
Now the question. When I run query mentioned above, and use SHOW FULL PROCESSLIST, I see the following query executed in MySQL:
Code: Select all
SELECT
Limit1.ProcessedDocumentID
FROM ( SELECT
Extent1.ProcessedDocumentID
FROM ProcessedDocument AS Extent1
INNER JOIN ReceivedDocument AS Extent2 ON Extent1.ReceivedDocumentID = Extent2.ReceivedDocumentID
WHERE (((CAST(Extent1.IsForwardResponded AS SIGNED)) = 0) AND ((CAST(Extent1.IsReadyToRespond AS SIGNED)) = 1)) AND (((Extent2.OwnerInstanceID = 3) OR ((Extent2.OwnerInstanceID IS NULL) AND (3 IS NULL))) OR (Extent2.OwnerInstanceID IS NULL))
LIMIT 100000
) AS Limit1;
Question is - why in this query unnecessary cast to SIGNED is added, i.e. (CAST(Extent1.IsForwardResponded AS SIGNED) ? Both values are unsigned tinyint (or byte on c# side). This cast causes performance issues (mysql fails to use proper index, and query with cast runs several seconds (same query, but without cast runs fractions of second). Is there any setting in devart mysql provider to prevent this behavior (unnecessary cast)?
Mysql version is 5.6.22
Thanks,
Artem.