Hello

I have an issue with the code that generates the EF Fluent API model binding code for a many-to many relationship . Here is the scenario: I have a table called "Person" that has a FK relationship to a join table with a composite key (PersonId, PhoneId) that has a relationship with a table called "Phones" joined on PhoneId:

Table: Person (one record with Id = 1): PK: Id Other fields such as FirstName, LastName et..

Table: PersonPhones (2 records (1) PersonId = 1, PhoneId = 2, (2) PersonId = 1, PhoneId = 3) PK PersonId, PhoneId

Table: Phone ( 3 records.. Id =1, 2, 3 respectively) PK: Id Number: string (75)

I created a many-to-many relationship in the designer by clicking on "Person" and adding a new many-to many relationship passing through 'PersonPhones'

When I run the app and try and get the data for Person.Phones property I get nothing. I took a look at the generated code and I see this in the 'MapPerson" method:

config.HasMany(t => t.Phones).WithMany(t => t.Persons) .Map(m => { m.ToTable("PersonPhones"); m.MapLeftKey("PhoneId"); m.MapRightKey("PersonId"); });

I did some reading on how to create a m:m relationship in EF fluent API and discovered the key mapping is wrong in the generated LLGLGen Pro code. If I manually edit this method and set the MapKey methods to:

config.HasMany(t => t.Phones).WithMany(t => t.Persons) .Map(m => { m.ToTable("PersonPhones"); m.MapLeftKey("PersonId"); m.MapRightKey("PhoneId"); });

and run the app, voila! I get the correct two 'Phone" objects returned from the Person.Phones property.

I tried seeing if I could somehow edit the relationship in the designer, but there really isn't much you can edit other that the generated navigation property names.

If I am missing something in the designer on how to get the generated code to generate the correct MapKey methods, please let me know.

If this is an issue, please tell me how to edit the appropriate .lpt file to get the generated code to output correctly. I can't go in and manually edit all the m:m relationships every time I need to generate the code due to a schema change, as you can imagine

Thank you

More info:

LLBLGen Pro version: 4.2 Final - Trial Target Framework: Entity Framework v6 Target Language: C# Target platform: .NET 4.5.2 Selected Preset: SD.EntityFramework v6 (Code First)

Could you please share the Designer Release Date? (Help/About)

June 9th, 2015

I just downloaded it from the website 2 days ago..

Looks like a bug, we're looking into it.

I think we made the same mistake as http://stackoverflow.com/questions/16490334/how-to-define-many-to-many-relationship-through-fluent-api-entity-framework

The documentation on MapLeft/RightKey is just confusing, IMHO. 'Navigation property in HasMany' is the navigator specified, so the keys were specified right in that light, but apparently the documentation refers to something else, and confusion arises.

I misread the docs completely, and several times. I missed 'parent entity' in the docs, so I assumed in .HasMany(x=>x.Foo), 'Foo' was referred to by the MapLeftKey but it was 'x'. And that of course makes it wrong.

I'll correct the template and get back to you.

Copy the attached template into <llblgen pro installation folder>\Frameworks\Entity Framework\Templates\V6\C# (as administrator!) overwriting the existing one, then regenerate the code.

Thank you, Otis. Once again you have been very quick and professional in your response!

And, above all, and unlike a lot of companies, you have the courage to admit flaws and bugs. I for one, respect that and that is why you have had my business for so many years. I'm not sure if you remember me or not, but I have been using LLBLGen since 2003 (when I used to live in Barcelona) and every time I have had a problem or question, I have posted to the forum and received very quick and helpful responses from you (I think way back then it was just you running the shop - correct?)

It is no wonder why you have such a loyal customer base!

Steve

Stoop wrote:

Thank you, Otis. Once again you have been very quick and professional in your response!

thank you!

And, above all, and unlike a lot of companies, you have the courage to admit flaws and bugs. I for one, respect that and that is why you have had my business for so many years. I'm not sure if you remember me or not, but I have been using LLBLGen since 2003 (when I used to live in Barcelona) and every time I have had a problem or question, I have posted to the forum and received very quick and helpful responses from you (I think way back then it was just you running the shop - correct?)

Long time! Yeah your name already ringed a bell, now I know why . Back in the day it was just me, now we have 2 support guys (Walaa and David), still small shop but not more people needed (unlike some Microsoft ORM development teams ).

Admitting failures is indeed something that's sometimes hard, but an old friend once told me "If you stand for the high quality you want to provide, you have to make sure you fix your mistakes". Ever since I strive to live by that: fixing mistakes is what the user wants after all, not an excuse (although sadly we can't always avoid not fixing things due to e.g. breaking changes or other reasons outside our control).

It is no wonder why you have such a loyal customer base!

Have a good weekend, Steve

Hi

I have recently switched from model first (EDMX) to code first (fluent) (but still hope to use LLBLGEN project as the master as the designer is superb).

I'm using LLBLGEN PRO v4.2 and EF6.1.3

I'm having lots of problems with relationships (just 1 to manys). I also have TPT inheritance (3 levels)

I've compared the db generated by llblgen pro sql script and that generated from the EF fluent db context, and the latter is missing several FK constraints I think.

Could this be the same issue, and does it have the same fix?

what does 'lots of problems' mean exactly? Could you enable 'Emit Fk fields' in the settings for entity framework in the project settings in the designer? This might help already quite a bit.

CF is a bit of a hit/miss, lots of things 'work' but there are a lot of edge cases which make EF give up at times

ALso, indeed to make sure it's not the same issue, be sure to use the latest build of the v4.2 designer

Hi

I had/have 'Emit Fk fields' set already.

How do I tell I have got the latest designer? (it states "4.2 Final, July 21st 2015" in titlebar)

Some of the issues: - missing FK (no navigator available to express it)

- several missing cascade on delete, eg: sql generated by llblgen:

ALTER TABLE [dbo].[BILLING_ADraftBillingDocLine] 
    ADD CONSTRAINT [FK_ADraftBillingDocLineBillingProcessDAO] FOREIGN KEY
    (
        [ProcessDataId] 
    )
    REFERENCES [dbo].[BILLING_ABillingProcessData]
    (
        [Id] 
    )
    ON DELETE CASCADE
    ON UPDATE NO ACTION

CF code:

        protected virtual void MapADraftBillingDocLine(EntityTypeConfiguration<ADraftBillingDocLine> config)
        {
            config.ToTable("BILLING_ADraftBillingDocLine");
:
            config.Property(t => t.ProcessDataId).IsRequired();
:
            config.HasRequired(t => t.ProcessData).WithMany(t => t.DocumentLines).HasForeignKey(t => t.ProcessDataId).[b]WillCascadeOnDelete[/b](true);

sql generated from CF:

alter table [dbo].[BILLING_ADraftBillingDocLine] add constraint [ADraftBillingDocLine_ProcessData] foreign key ([ProcessDataId]) references [dbo].[BILLING_ABillingProcessData]([Id]);

- timestamps comeout as varbainary sql generated by llblgen:

[Timestamp] [timestamp] NULL,

sql from CF:

[Timestamp] [varbinary](max) null,

**- decimals come out as 18,2 by default, but designer generated them as 18,9 (probably as we specified) **

- Xml data type comes out as nvarchar, eg:

[XmlData] [nvarchar] (max) COLLATE Latin1_General_CI_AS NOT NULL

fpdave100 wrote:

Hi

I had/have 'Emit Fk fields' set already.

How do I tell I have got the latest designer? (it states "4.2 Final, July 21st 2015" in titlebar)

The website is often a good start -> latest build is from September 5th, with designer build from August 2nd.

Looking at the changelogs (http://www.llblgen.com/Pages/ChangelogBrowser.aspx) there aren't any changes in EF templates since July 17th, so you have these.

Some of the issues: - missing FK (no navigator available to express it)

In CF you need to specify both navigators. Did you remove one of them?

- several missing cascade on delete, eg: sql generated by llblgen:

ALTER TABLE [dbo].[BILLING_ADraftBillingDocLine] 
    ADD CONSTRAINT [FK_ADraftBillingDocLineBillingProcessDAO] FOREIGN KEY
    (
        [ProcessDataId] 
    )
    REFERENCES [dbo].[BILLING_ABillingProcessData]
    (
        [Id] 
    )
    ON DELETE CASCADE
    ON UPDATE NO ACTION

CF code:

        protected virtual void MapADraftBillingDocLine(EntityTypeConfiguration<ADraftBillingDocLine> config)
        {
            config.ToTable("BILLING_ADraftBillingDocLine");
:
            config.Property(t => t.ProcessDataId).IsRequired();
:
            config.HasRequired(t => t.ProcessData).WithMany(t => t.DocumentLines).HasForeignKey(t => t.ProcessDataId).[b]WillCascadeOnDelete[/b](true);

Isn't that correct? The FK defines delete cascade, so does the relationship? (it's been a long day, I might overlook something)

sql generated from CF:

alter table [dbo].[BILLING_ADraftBillingDocLine] add constraint [ADraftBillingDocLine_ProcessData] foreign key ([ProcessDataId]) references [dbo].[BILLING_ABillingProcessData]([Id]);

That, I can't help with, if EF generates the wrong SQL... Though I can't say the CF code is wrong, so that the ddl sql generated by CF is different... that's something I can't fix for you. (the DDL SQL generated by the designer is actually better, as it's from the DB, not a reverse engineering on reverse engineering)

- timestamps comeout as varbainary sql generated by llblgen:

[Timestamp] [timestamp] NULL,

sql from CF:

[Timestamp] [varbinary](max) null,

What's the CF code for the field?

**- decimals come out as 18,2 by default, but designer generated them as 18,9 (probably as we specified) **

Yes, and again, if EF generates SQL from it, it might be the information in the model used for that isn't as precise as the info we have in the designer, e.g. because they reverse engineer from .net types and we don't specify specific db type specifics in the CF code as it's not needed at runtime. (so it has less information)

- Xml data type comes out as nvarchar, eg:

[XmlData] [nvarchar] (max) COLLATE Latin1_General_CI_AS NOT NULL

Same as above.

We don't generate DB type specifics into the CF code as it's not needed for the runtime and the code is generated from the model which has much precise ddl sql output. (we do the same with Nhibernate btw).

This also doesn't tie your CF mappings to a specific DB type, but that aside.

Anyway, the timestamp type is odd as well as the missing cascading delete directive as it's in the CF code, other than that, the DDL SQL generated from CF model is less ideal at the moment as there's not enough information emitted in the code as that information isn't needed at runtime. The schema create/update scripts can be generated from the designer which is more precise and by not generating the type specifics in the CF code, it's less cluttered with info it doesn't need at runtime.

Of course, if the CF code is meant to create the DB at startup, it has a bit of a problem as there's less info than needed available for that in some cases (although it should be able to generate better sql than what you got from it )

Does that weed out a bit of the problems you have?

I didnt want to have "quote hell", hope below is clear enough:

Note that I'm kind of using the SQL generated as a proxy for what EF has in its in-memory model, as I'm getting EF errors on SaveChanges() rather than SQL errors!

- missing FK (no navigator available to express it): I didnt want a navigator. I think this is a limitation of CF - I guess may have to introduce one - very annoying!!

- several missing cascade on delete: yes, the CF code you generated is correct, but the sql generated by EF is NOT - I guess this not your issue.

- timestamps comeout as varbinary: CF code:

config.Property(t => t.Timestamp).IsConcurrencyToken();

It maybe should be:

config.Property(t => t.Timestamp).IsConcurrencyToken().HasColumnType(SqlDbType.Timestamp.ToString());

- decimals come out as 18,2 by default, but designer generated them as 18,9 (probably as we specified) in the designer we have specified scale=18, precision=9. cf code gen should maybe generate:

config.Property(t => t.GrossValue).IsRequired().HasPrecision(18, 9);

I appreciate that if we use the designer ddl gen (as I do) then all is OK. However, you did state that one reason to support CF is that it removes a dependency on llblgen. I work in the **product **dev department, but I want to provide CF so that specific project teams can make tweaks to the model and mappings, and they will be doing that using the fluent, and so will want to generate DDL from that, so it is important it is correct.

Can I modify mappings/types for individual fields using the model builder hooks?

fpdave100 wrote:

I didnt want to have "quote hell", hope below is clear enough:

Note that I'm kind of using the SQL generated as a proxy for what EF has in its in-memory model, as I'm getting EF errors on SaveChanges() rather than SQL errors!

That's indeed unacceptable. Could you create a helpdesk thread and attach your llblgenproj file there (so only we can see it) and some code which inserts an entity so the savechanges sql errors are triggered? We can generate the DB from the llblgenproj file. Please refer to this thread in that thread.

- missing FK (no navigator available to express it): I didnt want a navigator. I think this is a limitation of CF - I guess may have to introduce one - very annoying!!

If the navigator is on the FK side, the relationship effectively doesn't exist. I think they need the pk side navigator there too.

- timestamps comeout as varbinary: CF code:

config.Property(t => t.Timestamp).IsConcurrencyToken();

It maybe should be:

config.Property(t => t.Timestamp).IsConcurrencyToken().HasColumnType(SqlDbType.Timestamp.ToString());

that might be the case, which ties it to sqlserver specifically though. If the Timestamp property is a Timestamp field though, they should simply produce a Timestamp field... Odd.

- decimals come out as 18,2 by default, but designer generated them as 18,9 (probably as we specified) in the designer we have specified scale=18, precision=9. cf code gen should maybe generate:

config.Property(t => t.GrossValue).IsRequired().HasPrecision(18, 9);

That is indeed another issue we can correct. Not sure the precision is used at runtime in EF.

I appreciate that if we use the designer ddl gen (as I do) then all is OK. However, you did state that one reason to support CF is that it removes a dependency on llblgen. I work in the **product **dev department, but I want to provide CF so that specific project teams can make tweaks to the model and mappings, and they will be doing that using the fluent, and so will want to generate DDL from that, so it is important it is correct.

That will be a problem indeed. We currently generate less info as it's not needed, but in your case it is.

Can I modify mappings/types for individual fields using the model builder hooks?

Yes. Though it's better to use a copy of the modelbuilder template and generate the code in, less writing and always up to date.

We'll look into this this weekend or monday (likely monday). The meta-data emitting into the modelbuilder class is perhaps not that hard to do, but first the issues with savechanges have to be resolved.

HI.

I suspect most of the issue might be to do with missing navigators. I will add them in and see how it goes before raising a helpdesk ticket. Thanks for your help so far

would it be possible for the db server type-specific bits of the mapping to be separated out into a separate method, to keep them apart?

fpdave100 wrote:

HI.

I suspect most of the issue might be to do with missing navigators. I will add them in and see how it goes before raising a helpdesk ticket. Thanks for your help so far

Ok, no problem There's an upcoming TPH mapping issue with CF fixed this morning, in case you run into that (missing target table in some cases) -> this is fixed and will be available this week.

would it be possible for the db server type-specific bits of the mapping to be separated out into a separate method, to keep them apart?

Yes, you can create a derived class from the modeler class and override the mapping methods and make changes in the overrides.

However we discussed this and the use case of CF creating the DB is one which is something we want to support so we'll add the definitions of the table fields to the CF mapping code hopefully this week (in v5 which is in development, and then backport it to v4.2). We'll add a setting to the frameworks settings (which is off by default) to generate the info into the CF code, so that saves you from manually adding code which has to be maintained.

Hi.

I have been sidetracked for the last several days onto fixing other tickets and doing change requests on other projects, but am now back on this for a few days.

I have installed the latest Llblgen (august 2nd)

I have added all the navigators that were missing.

I'm now getting a couple of errors: System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.InvalidOperationException: A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'Id'.

Regarding my question: would it be possible for the db server type-specific bits of the mapping to be separated out into a separate method, to keep them apart?

I actually meant that when you implement it, it might be an idea to keep the db specific bits seperate from the generic mapping code

With 'The db specific bits' you mean the db types etc. that are not really used at runtime except for when creating the schema at startup ?

About the errors: we'd like to have a repro case for that to investigate it and correct the issue.

Yes, I mean the db types that are not really used by the .net code, but are used by the db

I've posted everything to a helpdesk thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=23494

would it be possible to confim that you have now received my attachment and further comments - I havent heard anything today.

I have bit of a deadline on this as we are due to do a release by the end of the week and really need to get the fluent working reliably before then

Cheers

yes we've received the files. We hope to have a fixed cf mapping builder template tomorrow (tuesday).

Great :-)

If you have any difficulties at all with my files or reproducing the issues then please get back to me as soon as possible so I can help.

Looking forward to the results.

I saw one problem: the project has a typeimports file, referring to a couple of enums, but the only code file with enums contains just 3 of them. Please attach (in the other thread ) also the enums project / dll which is used in the designer

Otis wrote:

I saw one problem: the project has a typeimports file, referring to a couple of enums, but the only code file with enums contains just 3 of them. Please attach (in the other thread ) also the enums project / dll which is used in the designer

The dlls are in the dropbox folder (in a 27MB zip) I shared with you, and referred to by the Enums.typeimports file (you will have to modify the file paths - BTW, this doesnt seem to like relative paths). There are lots of dependencies and its a big solution (120 projects), altho if this causes problems I could probably split them out with some work.

sorry, moved the dropbox link - should be private only