Oracle egger loading EF 4.1 Code First with Table-per-type

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
jared
Posts: 10
Joined: Wed 12 Oct 2011 22:12

Oracle egger loading EF 4.1 Code First with Table-per-type

Post by jared » Thu 13 Oct 2011 22:17

I have come across an issue when egger loading multiple collection properties and the collection type has inherited classes.

Let me illustrate, given the root class:

Code: Select all

    public class RootItem
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        public List ChildItems1 { get; set; }
        public List ChildItems2 { get; set; }
    }
With child classes:

Code: Select all

    [Table("ChildItem1")]
    public class ChildItem1
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
    }
And:

Code: Select all

    public class ChildItem2
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
    }
And with inherited class:

Code: Select all

    [Table("InheritedChild1")]
    public class InheritedChild1 : ChildItem1
    {
        [MaxLength(10)]
        public string TheColumnThatCausesErrors { get; set; }
    }
With Context:

Code: Select all

    public class MyDbContext : DbContext
    {

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove();

            modelBuilder.Conventions.Remove();
        }

        public DbSet RootItems { get; set; }
        public DbSet ChildItems1 { get; set; }
        public DbSet ChildItems2 { get; set; }

    }
Then if you run the following command:

Code: Select all

    using (MyDbContext context = new MyDbContext())
    {
        var query = (from p in
                            context.RootItems
                                .Include(r => r.ChildItems1)
                                .Include(r => r.ChildItems2)
                        select p).ToList();

    }
You will get a "ORA-12704: character set mismatch" error.

The following query is sent to the database:

Code: Select all

SELECT 
"UnionAll1"."Id" AS C1, 
"UnionAll1"."Id1" AS C2, 
"UnionAll1".C1 AS C3, 
"UnionAll1"."Id2" AS C4, 
"UnionAll1".C2 AS C5, 
"UnionAll1"."Id3" AS C6, 
"UnionAll1".C3 AS C7, 
"UnionAll1"."RootItem_Id" AS C8, 
"UnionAll1".C4 AS C9, 
"UnionAll1".C5 AS C10, 
"UnionAll1".C6 AS C11
FROM  (SELECT 
	CASE WHEN "Join1"."Id1" IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C1, 
	"Extent1"."Id" AS "Id", 
	"Extent1"."Id" AS "Id1", 
	"Join1"."Id1" AS "Id2", 
	CASE WHEN "Join1"."Id1" IS NULL THEN TO_CHAR(NULL) WHEN  NOT (("Join1".C1 = 1) AND ("Join1".C1 IS NOT NULL)) THEN '2X' ELSE '2X0X' END AS C2, 
	"Join1"."Id1" AS "Id3", 
	CASE WHEN "Join1"."Id1" IS NULL THEN TO_CHAR(NULL) WHEN  NOT (("Join1".C1 = 1) AND ("Join1".C1 IS NOT NULL)) THEN TO_CHAR(NULL) ELSE "Join1"."TheColumnThatCausesErrors" END AS C3, 
	"Join1"."RootItem_Id" AS "RootItem_Id", 
	TO_NUMBER(NULL) AS C4, 
	TO_NUMBER(NULL) AS C5, 
	TO_NUMBER(NULL) AS C6
	FROM  "RootItems" "Extent1"
	LEFT OUTER JOIN  (SELECT 
		"Extent2"."Id" AS "Id1", 
		"Extent2"."RootItem_Id" AS "RootItem_Id", 
		"Project1"."Id" AS "Id2", 
		"Project1"."TheColumnThatCausesErrors" AS "TheColumnThatCausesErrors", 
		"Project1".C1 AS C1
		FROM  "ChildItem1" "Extent2"
		LEFT OUTER JOIN  (SELECT 
			"Extent3"."Id" AS "Id", 
			"Extent3"."TheColumnThatCausesErrors" AS "TheColumnThatCausesErrors", 
			1 AS C1
			FROM "InheritedChild1" "Extent3" ) "Project1" ON "Extent2"."Id" = "Project1"."Id" ) "Join1" ON "Extent1"."Id" = "Join1"."RootItem_Id"
UNION ALL
	SELECT 
	2 AS C1, 
	"Extent4"."RootItem_Id" AS "RootItem_Id", 
	"Extent4"."RootItem_Id" AS "RootItem_Id1", 
	TO_NUMBER(NULL) AS C2, 
	TO_CHAR(NULL) AS C3, 
	TO_NUMBER(NULL) AS C4, 
	TO_NCHAR(NULL) AS C5, 
	TO_NUMBER(NULL) AS C6, 
	"Extent4"."Id" AS "Id", 
	"Extent4"."Id" AS "Id1", 
	"Extent4"."RootItem_Id" AS "RootItem_Id2"
	FROM "ChildItem2" "Extent4"
	WHERE "Extent4"."RootItem_Id" IS NOT NULL) "UnionAll1"
ORDER BY "UnionAll1"."Id1" ASC, "UnionAll1".C1 ASC

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

Post by Shalex » Tue 18 Oct 2011 14:14

Please tell us the exact version (x.xx.xxx) of dotConnect for Oracle you have generated your model with. Tell us the version of your current build. Does it work with the workaround that is mentioned at http://www.devart.com/forums/viewtopic.php?t=22137? If not, please send us a small test project with the corresponding DDL/DML script to reproduce the issue in our environment.

jared
Posts: 10
Joined: Wed 12 Oct 2011 22:12

Post by jared » Tue 18 Oct 2011 17:11

I am using version 6.50.228.0, and with the simple model that I have described here, calling modelBuilder.Conventions.Remove() does resolve the issue, but with the much more complex model that I am programming, it still does not fully resolve the issue, and calling this causes other issues, like strings with no MaxLength are created as VARCHAR2 and not CLOB. I will wait for the new version, and test my more complex model, and if I still have issues I will send it to you.

jared
Posts: 10
Joined: Wed 12 Oct 2011 22:12

Post by jared » Mon 24 Oct 2011 21:26

I have now tested with dotConnect for Oracle 6.50.237, and I still get the error with the following code:

Code: Select all

[Table("ChildItem1")]
public abstract class Child1
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public int GrandChild1Id { get; set; }
    public GrandChild1 GrandChild1 { get; set; }

    [MaxLength(10)]
    public string DoesNotError { get; set; }
}

Code: Select all

[Table("InheritedChild1")]
public class Child1A : Child1
{
    [MaxLength(10)]
    public string DoesError { get; set; }
}

Code: Select all

[Table("ChildItem2")]
public class Child2
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [MaxLength(10)]
    public string DoesNotError { get; set; }
}

Code: Select all

[Table("GrandChild1")]
public abstract class GrandChild1
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [MaxLength(10)]
    public string DoesNotError { get; set; }
}

Code: Select all

[Table("GrandChild1A")]
public class GrandChild1A : GrandChild1
{
    [MaxLength(10)]
    public string DoesError { get; set; }
}

Code: Select all

[Table("GrandChild1B")]
public class GrandChild1B : GrandChild1
{
    [MaxLength(10)]
    public string DoesError { get; set; }
}

Code: Select all

public class Root
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [MaxLength(10)]
    public string DoesNotError { get; set; }

    public List ChildItems1 { get; set; }
    public List ChildItems2 { get; set; }
}

Code: Select all

public class MyDbContext : DbContext
{

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove();

        modelBuilder.Conventions.Remove();
    }

    public DbSet RootItems { get; set; }
    public DbSet ChildItems1 { get; set; }
    public DbSet ChildItems2 { get; set; }
    public DbSet GrandChild1 { get; set; }
}

Code: Select all

class Program
{

    static void Main(string[] args)
    {
           
        var monitor = new OracleMonitor() { IsActive = true };

        var config = OracleEntityProviderConfig.Instance;

        config.Workarounds.IgnoreSchemaName = true;

        config.DatabaseScript.Schema.DeleteDatabaseBehaviour = DeleteDatabaseBehaviour.ModelObjectsOnly;

        System.Data.Entity.Database.SetInitializer(new DropCreateDatabaseAlways());

        using (MyDbContext context = new MyDbContext())
        {
            var query = (from p in
                                context.RootItems
                                    .Include(r => from c in r.ChildItems1
                                                select c.GrandChild1)
                                    .Include(r =>   r.ChildItems2 )
                            select p).ToList();
        }
    }

}
The following query is produced (and throws error: ORA-12704: character set mismatch):

Code: Select all

SELECT 
"UnionAll2"."Id" AS C1, 
"UnionAll2"."Id1" AS C2, 
"UnionAll2"."DoesNotError" AS C3, 
"UnionAll2".C1 AS C4, 
"UnionAll2"."Id2" AS C5, 
"UnionAll2".C2 AS C6, 
"UnionAll2"."Id3" AS C7, 
"UnionAll2"."GrandChild1Id" AS C8, 
"UnionAll2"."DoesNotError1" AS C9, 
"UnionAll2"."DoesError" AS C10, 
"UnionAll2".C3 AS C11, 
"UnionAll2".C4 AS C12, 
"UnionAll2"."DoesNotError2" AS C13, 
"UnionAll2".C5 AS C14, 
"UnionAll2".C6 AS C15, 
"UnionAll2"."Root_Id" AS C16, 
"UnionAll2".C7 AS C17, 
"UnionAll2".C8 AS C18, 
"UnionAll2".C9 AS C19, 
"UnionAll2".C10 AS C20
FROM  (SELECT 
	CASE WHEN "Join3"."Id1" IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C1, 
	"Extent1"."Id" AS "Id", 
	"Extent1"."Id" AS "Id1", 
	"Extent1"."DoesNotError" AS "DoesNotError", 
	"Join3"."Id1" AS "Id2", 
	CASE WHEN "Join3"."Id1" IS NULL THEN TO_CHAR(NULL) ELSE '2X0X' END AS C2, 
	"Join3"."Id1" AS "Id3", 
	"Join3"."GrandChild1Id" AS "GrandChild1Id", 
	"Join3"."DoesNotError1" AS "DoesNotError1", 
	"Join3"."DoesError1" AS "DoesError", 
	CASE WHEN "Join3"."Id1" IS NULL THEN TO_CHAR(NULL) WHEN "Join3"."Id2" IS NULL THEN TO_CHAR(NULL) WHEN "Join3".C2 = 1 THEN '5X0X' ELSE '5X1X' END AS C3, 
	"Join3"."Id2" AS C4, 
	"Join3"."DoesNotError2" AS "DoesNotError2", 
	CASE WHEN "Join3"."Id1" IS NULL THEN TO_CHAR(NULL) WHEN "Join3"."Id2" IS NULL THEN TO_CHAR(NULL) WHEN "Join3".C2 = 1 THEN "Join3".C1 END AS C5, 
	CASE WHEN "Join3"."Id1" IS NULL THEN TO_NCHAR(NULL) WHEN "Join3"."Id2" IS NULL THEN TO_NCHAR(NULL) WHEN "Join3".C2 = 1 THEN TO_NCHAR(NULL) ELSE "Join3"."DoesError2" END AS C6, 
	"Join3"."Root_Id" AS "Root_Id", 
	TO_NUMBER(NULL) AS C7, 
	TO_NUMBER(NULL) AS C8, 
	TO_CHAR(NULL) AS C9, 
	TO_NUMBER(NULL) AS C10
	FROM  "Roots" "Extent1"
	LEFT OUTER JOIN  (SELECT 
		"Extent2"."Id" AS "Id1", 
		"Extent2"."DoesError" AS "DoesError1", 
		"Extent3"."Id" AS "Id3", 
		"Extent3"."GrandChild1Id" AS "GrandChild1Id", 
		"Extent3"."DoesNotError" AS "DoesNotError1", 
		"Extent3"."Root_Id" AS "Root_Id", "Join2"."Id2", "Join2".C1, "Join2"."DoesError2", "Join2".C2, "Join2"."Id4", "Join2"."DoesNotError2"
		FROM   "InheritedChild1" "Extent2"
		INNER JOIN "ChildItem1" "Extent3" ON "Extent2"."Id" = "Extent3"."Id"
		LEFT OUTER JOIN  (SELECT 
			"UnionAll1"."Id" AS "Id2", 
			"UnionAll1".C1 AS C1, 
			"UnionAll1"."DoesError" AS "DoesError2", 
			"UnionAll1".C2 AS C2, 
			"Extent6"."Id" AS "Id4", 
			"Extent6"."DoesNotError" AS "DoesNotError2"
			FROM   (SELECT 
				"Extent4"."Id" AS "Id", 
				TO_CHAR(NULL) AS C1, 
				"Extent4"."DoesError" AS "DoesError", 
				0 AS C2
				FROM "GrandChild1B" "Extent4"
			UNION ALL
				SELECT 
				"Extent5"."Id" AS "Id", 
				"Extent5"."DoesError" AS "DoesError", 
				TO_CHAR(NULL) AS C1, 
				1 AS C2
				FROM "GrandChild1A" "Extent5") "UnionAll1"
			INNER JOIN "GrandChild1" "Extent6" ON "UnionAll1"."Id" = "Extent6"."Id" ) "Join2" ON "Extent3"."GrandChild1Id" = "Join2"."Id2" ) "Join3" ON "Extent1"."Id" = "Join3"."Root_Id"
UNION ALL
	SELECT 
	2 AS C1, 
	"Extent7"."Id" AS "Id", 
	"Extent7"."Id" AS "Id1", 
	"Extent7"."DoesNotError" AS "DoesNotError", 
	TO_NUMBER(NULL) AS C2, 
	TO_CHAR(NULL) AS C3, 
	TO_NUMBER(NULL) AS C4, 
	TO_NUMBER(NULL) AS C5, 
	TO_CHAR(NULL) AS C6, 
	TO_CHAR(NULL) AS C7, 
	TO_CHAR(NULL) AS C8, 
	TO_NUMBER(NULL) AS C9, 
	TO_CHAR(NULL) AS C10, 
	TO_NCHAR(NULL) AS C11, 
	TO_NCHAR(NULL) AS C12, 
	TO_NUMBER(NULL) AS C13, 
	"Extent8"."Id" AS "Id2", 
	"Extent8"."Id" AS "Id3", 
	"Extent8"."DoesNotError" AS "DoesNotError1", 
	"Extent8"."Root_Id" AS "Root_Id"
	FROM  "Roots" "Extent7"
	INNER JOIN "ChildItem2" "Extent8" ON "Extent7"."Id" = "Extent8"."Root_Id") "UnionAll2"
ORDER BY "UnionAll2"."Id1" ASC, "UnionAll2".C1 ASC

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

Post by Shalex » Tue 25 Oct 2011 15:36

We are investigating the issue.

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

Post by Shalex » Thu 27 Oct 2011 10:38

The bug with character set mismatch when UNION is used for CASEs is fixed. We will post here when the corresponding build of dotConnect for Oracle is available for download.

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

Post by Shalex » Fri 11 Nov 2011 17:16

New build of dotConnect for Oracle 6.50.244 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=22547 .

Post Reply