Devart Forums

Posted: **Fri 06 Jan 2012 21:43**

Using dotConnect with Entity Framework Code First I get an error when trying to walk up a relationship to a parent table.

I have an Account table and a Request table. The request table has let's say CreatedBy and ModifiedBy on it. On the Account entity I have an ICollection RequestsCreatedByMe and an ICollection RequestsModifiedByMe. The collections are annotated with [InverseProperty("CreatedBy")] and [InverseProperty("ModifiedBy")]

See below for tables and objects, but when I execute the following code:
_context.Accounts.Where(a => a.RequestsCreatedByMe.Any())
in order to get all accounts that have requests created the sql generated is bad:
SELECT
"GroupBy1".A1 AS C1
FROM ( SELECT Count(1) AS A1
FROM ADME.ACCOUNT "Extent1"
WHERE EXISTS (SELECT
1 AS C1
FROM ADME.REQUEST "Extent2"
WHERE "Extent1".ID = "Extent2"."Account_ID"
)
) "GroupBy1"

Clearly the code should have been "Extend2"."CreatedByID" as that is what I stated to use in the InverseProperty and the Account_ part is completely bogus. Am I missing something or is this a bug?

Tables:
CREATE TABLE Account (ID INT, Name NVARCHAR2(200));
CREATE TABLE Request (ID INT, CreatedByID INT, ModifiedByID INT);

INSERT INTO ACCOUNT VALUES (1,'Foo');
INSERT INTO ACCOUNT VALUES (2, 'Bar');

INSERT INTO Request VALUES (1, 1, 2);
INSERT INTO Request VALUES (2, 2, 1);
INSERT INTO Request VALUES (3, 2, 1);
INSERT INTO Request VALUES (4, 2, 1);

Classes:
[Table("ACCOUNT", Schema = "MYSCHEMA")]
public class Account
{
[Key]
public int ID { get; set; }
public string Name { get; set; }

[InverseProperty("CreatedByID")]
public virtual ICollection RequestsCreatedByMe { get; set; }
[InverseProperty("ModifiedByID")]
public virtual ICollection RequestsModifiedByMe { get; set; }
}

[Table("REQUEST", Schema="MYSCHEMA")]
public class Request
{
[Key]
public int ID { get; set; }
public int CreatedByID { get; set; }
public virtual Account CreatedBy { get; set; }
public int ModifiedByID { get; set; }
public virtual Account ModifiedBy { get; set; }
}

Posted: **Fri 13 Jan 2012 13:15**

As we understood, you already have tables in your database and create classes/mapping manually. In this case, we recommend creating model with the help of Entity Developer: http://www.devart.com/blogs/dotconnect/ ... plate.html. Turn on the Fluent Mapping option of template to generate classes and the most detailed mapping which avoid uncomplete configuration and influence of convensions. This is the easiest way because manual mapping often leads to mistakes, some of which you are experiencing:

1. Incorrect associations configuration. The way to identify the problem: generate DDL and check the database structure:

Code: Select all

var ddl = ((IObjectContextAdapter)_dbContext).ObjectContext.CreateDatabaseScript();

You will see that there are 4 FKs (instead of 2) and 2 additional columns in Request in the generated script.
Solution: write explicit mapping for associations by adding the following code into OnModelCreating:

Code: Select all

            modelBuilder.Entity()
              .HasRequired(r => r.CreatedBy)
              .WithMany(r => r.RequestsCreatedByMe)
              .HasForeignKey(r => r.CreatedByID);

            modelBuilder.Entity()
              .HasRequired(r => r.ModifiedBy)
              .WithMany(r => r.RequestsModifiedByMe)
              .HasForeignKey(r => r.ModifiedByID);

As a result, redundant columns and FKs will go away.

2. The names of your tables and columns in database are in uppercase, but your class properties in .NET are in CamelCase. The easiest way to solve the issue is to turn on the DisableQuoting option:

Code: Select all

  var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
  config.Workarounds.DisableQuoting = true;

For more information about configuring EF-provider, please refer to http://www.devart.com/blogs/dotconnect/ ... iders.html.

Devart Forums

Error when using InverseProperty?

Error when using InverseProperty?