Devart Forums

magnus

Thanks for the reply. My personal feeling is that DataMember should be omitted on many-to-many associations since they are effectively relations that are "reverse on both sides".

By the way, this is how we implemented this in our little hack:

Code: Select all

if (WcfDataContractAttributes)
  if (relationProperty.Multiplicity != Multiplicity.Many)
    GenerateRelationWcfDataContractAttributes(relationProperty);

We added the second if-statement.

magnus

We have a change request for the DbContext template that may put us in the minority, but we would like to have the option of omitting the DataMember attribute on reverse navigation properties when using the WCF Data Contract option in the DbContext template, but we do want the attribute on regular properties, and of course on regular entity fields.

As an example, if there's an entity Order and another entity OrderLine, and OrderLine has a foreign key to Order, we want the Order property on the OrderLine entity to have DataMember, but the OrderLines property on the Order entity should not.

We have two reasons for this:
1. We're using the entity classes as DTO classes as well as in our internal code, and although we do use the navigation properties internally, we have no desire to expose the reverse navigation properties to anyone else.
2. We experienced some pretty severe issues when trying to import WSDLs using the data contract hierarchies with reverse navigation properties into SoapUI, presumably owing to the complexity of the hierarchy or possibly something to do with circular references. This may just be a bug with that program, but we can't be certain that the same problem won't affect other programs -- particularly other Java-based programs.

We've implemented this as a one-liner in our modified version of the DbContext template, although I'm sure that if you were to implement this it would have to be as a configurable option.

magnus

We just ran into a very obscure and somewhat comical issue where we were getting a StackOverflowException in the OnModelCreating method in certain cases. As it turned out it was caused by the OnModelCreating method having gotten so big that it was breaking the default IIS 32-bit stack limit of 256 KB, presumably because the calls to Entity<T> command etc. keep growing the stack. We have about 200 entities in our model, and we're clearly somewhere between the aforementioned IIS limit and the more standard .NET limit of 1 MB.

We've now modified our version of the DbContext template so each entity gets its own method, and that fixed the issue. But we'd prefer it if this was the standard behavior of your DbContext template.

On a related note, Visual Studio often struggles really badly when you try to open the generated file with the DbContext in it, presumably because it's hard for it to seamlessly compile all the fluent mapping code on the fly. Perhaps there could be an option that allowed splitting the DbContext across multiple files as well? This is a much less important point than the one about splitting OnModelCreating into smaller methods though.

magnus

For legacy reasons, we have a lot of entities where the entity fields need to have different names than the names in the storage model. When you update the property name under "Classes" in Entity Developer, it updates the corresponding field in the storage model as well. Usually this is precisely what you want, but for me it means that I have to change all the property names twice, since I then I have to go back and change the storage model names back to what they were originally. It would be great to have a model setting like "Update storage model names when entity names are changed". Then I could set that setting to false whenever I'm working with one of the entities where the names need to differ.

If this already exists, I would love to know about it!

magnus

Using dotConnect for Oracle 6.60.283.0 and Entity Framework 4.1, I want to do a COUNT(DISTINCT foo), or something equivalent. I'm aware that there's no out-of-the-box support for this in Entity Framework, but I've seen people suggest the following idiom:

Code: Select all

from r in RetailerDeliveries
group r by r.RetailerNumber into grouped
select new
{
    DistinctCount = grouped.Select(d => d.Date).Distinct().Count()
}

(For the record, the entity "RetailerDelivery" is mapped to a table called "LVLEVERANSER", "RetailerNumber" is mapped to a field called "LVKNR", and "Date" is mapped to "LVDAT". The above is a big simplification of my real query, but it should get to the heart of the problem.)

I've tested this against MSSQL, where it generates a query that looks like this:

Code: Select all

SELECT 
[Distinct1].[LVKNR] AS [LVKNR], 
(SELECT 
	COUNT(1) AS [A1]
	FROM ( SELECT DISTINCT 
		[Extent2].[LVDAT] AS [LVDAT]
		FROM [INFOSOFT].[LVLEVERANSER] AS [Extent2]
		WHERE [Distinct1].[LVKNR] = [Extent2].[LVKNR]
	)  AS [Distinct2]) AS [C1]
FROM ( SELECT DISTINCT 
	[Extent1].[LVKNR] AS [LVKNR]
	FROM [INFOSOFT].[LVLEVERANSER] AS [Extent1]
)  AS [Distinct1]

Note the double subquery. On MSSQL, you can use values from the outermost query inside a doubly nested subquery, so the test [Distinct1].[LVKNR] = [Extent2].[LVKNR] works fine. But Oracle doesn't seem to support this, so when similar SQL is generated, it fails. You don't even need to include any tables in the query to get to the root of it; this simple pair of queries should also illustrate the difference:

MSSQL (works):

Code: Select all

select
    (select Col from (select '1' as Col where Col = Bar.Col) Foo)
    from (select '1' as Col) Bar;

Oracle (fails with ORA-00904: "BAR"."COL": invalid identifier):

Code: Select all

select
    (select Col from (select '1' as Col from dual where Col = Bar.Col) Foo)
    from (select '1' as Col from dual) Bar;

I think this is expected behavior from Oracle, and I'm not sure if there's anything you guys can do about it on your end. I found this old forum post about the same issue: http://forums.devart.com/viewtopic.php?t=17320, but there didn't seem to be a resolution to it.

My question is basically: Do you know of some alternative way I can achieve a COUNT DISTINCT directly with Linq To Entities? Also, is there perhaps some way you can make dotConnect tell me that what I'm trying to do is not supported instead of giving a pretty obscure error message?

magnus

(I'm using dotConnect for Oracle version 6.60.283.0.)
(Edit: And the Oracle server I've been testing against is version 10.2.)

I should say up front that I don't have a lot of experience with working directly with ADO.NET, but I think I've discovered an issue related to the OracleDataAdapter class and table columns named "TYPE", although I'm not entirely sure if it's by design or not. I've sent you guys a project that illustrates the problem, but since it's a little complicated I figured I should explain the issue here as well.

To start with I've created a table with the following schema (I don't believe the data types and such should make any difference).

Code: Select all

create table TypeColumnTestTable
(
    pk int primary key,
    type varchar(10) not null
);

I also added a public synonym to ensure that the right table was being accessed.

If I'm not mistaken, the above statement will create a table where the column names are considered to be uppercase. However, if you don't put quotes around the column name, you can reference it using any casing. If you put quotes around the column name, you do have to use the right casing. In other words, the following queries work:

Code: Select all

SELECT type FROM TypeColumnTestTable;
SELECT TYPE FROM TypeColumnTestTable;
SELECT TyPe FROM TypeColumnTestTable;
SELECT "TYPE" FROM TypeColumnTestTable;

...but this one fails:

Code: Select all

SELECT "type" FROM TypeColumnTestTable;

Now I go on to create an OracleDataAdapter and assign a SelectCommand, but let the InsertCommand be handled by an OracleCommandBuilder. Then I call Update(). Roughly speaking:

Code: Select all

// Create an adapter and a command builder.
OracleDataAdapter adapter = new OracleDataAdapter();
OracleCommandBuilder commandBuilder = new OracleCommandBuilder(adapter);

// Create a connection, and a command to be used as a SelectCommand.
OracleConnection connection = new OracleConnection(*some connection string*);
OracleCommand command = connection.CreateCommand();
command.CommandText = *insert select statement here*
adapter.SelectCommand = command;

// Imagine some code that creates a data set and adds a new row to it here.

// Call Update on the adapter, passing it the data set.
adapter.Update(*the dataset*);

This is where I'm experiencing issues, depending on the specifics of the CommandText on the SelectCommand. The code runs fine if the SelectStatement is one of the following:

Code: Select all

command.CommandText = "SELECT pk, TYPE FROM TypeColumnTestTable";
command.CommandText = "SELECT pk, \"TYPE\" FROM TypeColumnTestTable";
command.CommandText = "SELECT * FROM TypeColumnTestTable";

Note how this works fine even though "pk" is lowercase. The following statements fail, as is to be expected:

Code: Select all

command.CommandText = "SELECT \"pk\", \"TYPE\" FROM TypeColumnTestTable";
command.CommandText = "SELECT pk, \"type\" FROM TypeColumnTestTable";

What I don't understand is why the following statement also fails:

Code: Select all

command.CommandText = "SELECT pk, type FROM TypeColumnTestTable";

The error I get is the following:

Code: Select all

Devart.Data.Oracle.OracleException was unhandled
  Message=ORA-01400: cannot insert NULL into ("INFOSOFT"."TYPECOLUMNTESTTABLE"."TYPE")
  Source=System.Data
  ErrorCode=-2147467259
  Code=1400
  Offset=0
  StackTrace:
       at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
       at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
       at ColumnNamedTYPEProject.Program.Main(String[] args) in c:\Users\magbakke\Documents\Visual Studio 2010\Projects\ColumnNamedTYPEProject\Program.cs:line 93
  InnerException:

Using dbMonitor I can see that the generated insert statement is the following:

Code: Select all

INSERT INTO TYPECOLUMNTESTTABLE (PK) VALUES (:p1)

In other words, the "TYPE" column is missing.

As I understand it, "type" is only a reserved keyword in PL/SQL -- not in Oracle in general (http://docs.oracle.com/cd/B10501_01/app ... 25/apb.htm) -- but it still seems like dotConnect is treating it in a special manner for some reason. I've tested that:
(1) The last statement passes if the column is called e.g. "FOO" instead of "TYPE".
(2) The last statement passes if I use System.Data.OracleClient instead of Devart.Data.Oracle.

magnus

We've tested the view generation, and on my computer it cuts the load time from 23 to 13 seconds, which is obviously good news. Thanks for the tip!

I should probably refer this question to Microsoft, but do you happen to have any idea why the generated EntityViewContainer class has to be in the same assembly as the database entities? We keep our entities and data context objects in separate assemblies, and since the default location of the generated view class was of course the assembly with the context class, we couldn't get the generated view to work at first. Is there some way to avoid this behavior, for instance by explicitly passing the view class to some built-in method?

magnus

The answer to this question may very well be "that's to be expected", but it can't hurt to ask.

We have a quite large model. There are roughly 200 entities, and some of the entities are themselves pretty big -- the average number of columns per entity is probably in the 10-20 range. We're using the DbContext template with database independent and fluent mapping set to true, and the generated OnModelCreating method is about 15,000 lines. We've customized the DbContext template somewhat, but I don't think there's anything we've done that should have an effect on any of this. We're using Entity Framework 4.2.

Right after the application starts, we create a DbContext and perform a dummy query to ensure that everything gets loaded. It takes over 20 seconds for the line with the dummy query to execute. The vast majority of that time is spent somewhere inside EntityFramework.dll. I can say for certain that it's not the OnModelCreating method itself that's the problem, as it executes in less than a second on my computer.

All subsequent Entity Framework operations are nice and quick, so this isn't the end of the world. Basically I'm just wondering if I have to expect this kind of load time with a model of my size, or if there's something you know about that I can do to shorten the load time?

magnus

OK, thanks for the information. The only thing we actually need this for is sequence numbers for primary keys, and using triggers is not an option because that would break legacy code. Can you think of some other reasonably simple way to achieve the same results while using fluent mapping, given a set of entities, (almost) all of which have a corresponding sequence with a predictable name?

magnus

The technique described in this blog post doesn't seem to work with the DbContext generated by Entity Developer when fluent mapping is enabled. I noticed that the example uses ObjectContext, but I've verified that it works fine with DbContext when fluent mapping is disabled, which I guess is because it finds the specified default values in the XML mapping files. Is this a known limitation, a bug, or is there something else I'm missing?

The example in the link produces the following SQL with ObjectContext, or with DbContext without fluent mapping:

Code: Select all

DECLARE
  updatedRowid ROWID;
BEGIN
INSERT INTO INFO.PRODUCTS("Id", "FunctionGeneratedValue", "ProductName", "Price", "ModifiedBy", "LastModified")
VALUES (MY_SEQUENCE.NEXTVAL, MY_FUNCTION(), :p0, :p1, USER, current_timestamp)
RETURNING ROWID INTO updatedRowid;
OPEN :outParameter FOR SELECT "Id", "FunctionGeneratedValue" FROM INFO.PRODUCTS WHERE ROWID = updatedRowid;
END;

And here's the generated (failing) SQL with fluent mapping enabled:

Code: Select all

DECLARE
  updatedRowid ROWID;
BEGIN
INSERT INTO INFO.PRODUCTS("ProductName", "Price")
VALUES (:p0, :p1)
RETURNING ROWID INTO updatedRowid;
OPEN :outParameter FOR SELECT "Id", "FunctionGeneratedValue", "ModifiedBy", "LastModified" FROM INFO.PRODUCTS WHERE ROWID = updatedRowid;
END;

The latter SQL statement, for obvious reasons, gives a DbUpdateException with the message: {"ORA-01400: cannot insert NULL into (\"INFO\".\"PRODUCTS\".\"Id\")\nORA-06512: at line 4"}

Here's the (auto-generated) fluent mapping code I'm using:

Code: Select all

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {

            #region Products

            modelBuilder.Entity()
                .HasKey(p => new { p.Id })
                .ToTable("PRODUCTS", "INFO");
            // Properties:
            modelBuilder.Entity()
                .Property(p => p.Id)
                    .IsRequired()
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            modelBuilder.Entity()
                .Property(p => p.ProductName)
                    .IsRequired()
                    .HasMaxLength(160)
                    .IsUnicode(false);
            modelBuilder.Entity()
                .Property(p => p.Price);
            modelBuilder.Entity()
                .Property(p => p.FunctionGeneratedValue)
                    .IsRequired()
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            modelBuilder.Entity()
                .Property(p => p.ModifiedBy)
                    .IsRequired()
                    .HasMaxLength(30)
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)
                    .IsUnicode(false);
            modelBuilder.Entity()
                .Property(p => p.LastModified)
                    .IsRequired()
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);

            #endregion

            #region Disabled conventions

            modelBuilder.Conventions.Remove();
            modelBuilder.Conventions.Remove();

            #endregion

        }

magnus

I think there's a problem with self-referential foreign keys in the DbContext template when using fluent mapping. Suppose you have a model Foo with the PK FooId, and a nullable foreign key FooReferenceId, which references FooId. The DbContext template then generates this code for the association:

Code: Select all

            modelBuilder.Entity()
                .HasOptional(p => p.Foo1)
                    .WithMany(c => c.Foos)
                .HasForeignKey(p => new { p.FooId })
                    .WillCascadeOnDelete(false);

When you try to use the DbContext it will say:

One or more validation errors were detected during model generation:

System.Data.Edm.EdmAssociationType: : Multiplicity conflicts with the referential constraint in Role 'Foo_Foo1_Target' in relationship 'Foo_Foo1'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'.
System.Data.Edm.EdmAssociationEnd: : Multiplicity is not valid in Role 'Foo_Foo1_Source' in relationship 'Foo_Foo1'. Because the Dependent Role refers to the key properties, the upper bound of the multiplicity of the Dependent Role must be �1�.

I think the generated code is wrong. As far as I can tell the HasForeignKey line should say:

Code: Select all

.HasForeignKey(p => new { p.FooReferenceId })

Devart Forums

Search found 11 matches

Re: Omitting DataMember on reverse navigation properties

Omitting DataMember on reverse navigation properties

Split up the OnModelCreating method into smaller methods (fluent mapping)

Feature request: Don't change storage name when changing property name

COUNT DISTINCT with Linq against Oracle

OracleDataAdapter.SelectCommand and columns named "TYPE"

First time model load is slow

StoreGeneratedPattern, DefaultValue and fluent mapping

Problem with self-referential foreign keys with DbContext