Union All Issue
Posted: Tue 18 Nov 2014 13:03
Hi,
This is the code we are using:
Following query generated by dotConnect for MySQL (with EF6 (6.1.1));
And it cause "Illegal mix of collations for operation 'UNION' exception
Bellow link is google drive shared projects zip files;
https://drive.google.com/file/d/0B6eYIL ... sp=sharing
Please some one help, this is an urgent case. I wrote two times to devart support about this issue. But there is no answer...
Regards...
E. Faruk Pehlivanlı
This is the code we are using:
Code: Select all
var query =
context.AuditLogs.Where(al => ids.Contains(al.PkAuditLogId))
.Include(al => al.AuditLogDetails)
.Include(al => al.AuditLogRelations);
Code: Select all
{SELECT
UnionAll1.C2 AS C1,
UnionAll1.PkAuditLogId AS C2,
UnionAll1.ApplicationId AS C3,
UnionAll1.RelatedUserId AS C4,
UnionAll1.CreatedOn AS C5,
UnionAll1.AuditType AS C6,
UnionAll1.OwnerTableName AS C7,
UnionAll1.C1 AS C8,
UnionAll1.PkAuditLogDetailId AS C9,
UnionAll1.FkAuditLogId AS C10,
UnionAll1.FieldName AS C11,
UnionAll1.OldValue AS C12,
UnionAll1.NewValue AS C13,
UnionAll1.C3 AS C14,
UnionAll1.C4 AS C15,
UnionAll1.C5 AS C16,
UnionAll1.C6 AS C17
FROM (SELECT
CASE WHEN Extent2.PkAuditLogDetailId IS NULL THEN CAST(NULL AS SIGNED) ELSE 1 END AS C1,
1 AS C2,
Extent1.PkAuditLogId,
Extent1.ApplicationId,
Extent1.RelatedUserId,
Extent1.CreatedOn,
Extent1.AuditType,
Extent1.OwnerTableName,
Extent2.PkAuditLogDetailId,
Extent2.FkAuditLogId,
Extent2.FieldName,
Extent2.OldValue,
Extent2.NewValue,
CAST(NULL AS SIGNED) AS C3,
CAST(NULL AS SIGNED) AS C4,
CAST(NULL AS CHAR) AS C5,
CAST(NULL AS CHAR) AS C6
FROM AuditLog AS Extent1
LEFT OUTER JOIN AuditLogDetail AS Extent2 ON Extent1.PkAuditLogId = Extent2.FkAuditLogId
WHERE Extent1.PkAuditLogId IN (1,3,4,5)
UNION ALL
SELECT
2 AS C1,
2 AS C2,
Extent3.PkAuditLogId,
Extent3.ApplicationId,
Extent3.RelatedUserId,
Extent3.CreatedOn,
Extent3.AuditType,
Extent3.OwnerTableName,
CAST(NULL AS SIGNED) AS C3,
CAST(NULL AS SIGNED) AS C4,
CAST(NULL AS CHAR) AS C5,
CAST(NULL AS CHAR) AS C6,
CAST(NULL AS CHAR) AS C7,
Extent4.PkAuditRelationLogId,
Extent4.FkAuditLogId,
Extent4.RelatedTableName,
Extent4.RelatedRowId
FROM AuditLog AS Extent3
INNER JOIN AuditLogRelation AS Extent4 ON Extent3.PkAuditLogId = Extent4.FkAuditLogId
WHERE Extent3.PkAuditLogId IN (1,3,4,5)) AS UnionAll1
ORDER BY UnionAll1.PkAuditLogId ASC, UnionAll1.C1 ASC}
Bellow link is google drive shared projects zip files;
https://drive.google.com/file/d/0B6eYIL ... sp=sharing
Please some one help, this is an urgent case. I wrote two times to devart support about this issue. But there is no answer...
Regards...
E. Faruk Pehlivanlı