I'm checking into the LLBGen designer after a long time again and it's damn nice to work with for model design and syncing! Great tooling and I love the way your error tracking points right back to the issues in the model. Nice work Frans.

Question: I'm using the designer with an EF Core model that I've imported using the designer. It did a good job with this import. Now I'm making changes to the model and it's not quite clear to me what I can change where and how to get the changes synced.

From database -> model seems pretty obvious and that works - make a change resync and the changes show up in the model. That's good.

But what about the other way?

If I want to add additional properties or navigation properties, how do I get these synced back to the DB or generate DB Update scripts?

I've tried:

• Validate

Which correctly tells me that my model is out of sync. In the case of an added field it points me at the field, but when I get there I can't add the field to the database (see attachment).

I also tried Generate Schema Update Script, but when I run that it tells me that there are no changes to apply.

Is this something that's supported (with EF Core) or am I missing a step?

rstrahl wrote:

I'm checking into the LLBGen designer after a long time again and it's damn nice to work with for model design and syncing! Great tooling and I love the way your error tracking points right back to the issues in the model. Nice work Frans.

Question: I'm using the designer with an EF Core model that I've imported using the designer. It did a good job with this import. Now I'm making changes to the model and it's not quite clear to me what I can change where and how to get the changes synced.

From database -> model seems pretty obvious and that works - make a change resync and the changes show up in the model. That's good.

But what about the other way?

If I want to add additional properties or navigation properties, how do I get these synced back to the DB or generate DB Update scripts?

I've tried:

• Validate

Which correctly tells me that my model is out of sync. In the case of an added field it points me at the field, but when I get there I can't add the field to the database (see attachment).

I also tried Generate Schema Update Script, but when I run that it tells me that there are no changes to apply.

Is this something that's supported (with EF Core) or am I missing a step?

It's in the sync tab, if you go to the sync tab the sync source for the schema should either be 'model' (if it only contains tables) or 'mixed' (if it contains tables and procs/views/tvfs). If it is set to model or mixed, the sync tab will create a task for you, sync model with relational model, and you can then simply click 'perform tasks' on the sync tab to execute the sync task that's there. It will then update the relational model data from the entity model. After that you can then export an Update DDL SQL script.

See e.g.:[https://www.llblgen.com/Documentation/5.6/Designer/How%20To/SyncRelationalModelData.htm](https://www.llblgen.com/Documentation/5.6/Designer/How%20To/SyncRelationalModelData.htm) https://www.llblgen.com/Documentation/5.6/Designer/Functionality%20Reference/SyncTab.htm

Which version are you using btw?

I'm on 5.6 just installed a few days ago...

So the issue I see is that when I bring up the Sync Database form, I see the first option to sync from SQL to Model, but not the second one to sync from Model to Db. Only one option shows.

I'm using EF Core 2.2 as the entity model framework, not sure if that matters, but I don't see why that should make a difference.

Another issue I have is that the SQL model is out of date. I updated the database by removing a field from one of the tables (to make it trigger a change for syncing specifically), but when I look on the right side at the SQL schema it still shows the field - even after a full restart.

Two things:

• there seems to be no refresh for the SQL Schema (which seems important)
• it's somehow showing an out-of-date SQL schema even after a restart/reload

Not sure what the designer is looking at to get out of date Schema data from the SQL source, but it's definitely not reflecting what's in the DB.

Take a look at the screenshot attached.

Ahhhh... I guess I should read the documentation more carefully:

Changing the sync source gives me the second option to sync to the DB.

I ran into a few problems however. Running for the first time I ended up with an exception:

Exception information.
=============================================================
LLBLGen Pro version: v5.6. Build: 5.6.0

Exception details:
=====================
Message: Internal Error Exception occured in: SD.LLBLGen.Pro.ApplicationCore.MetaData.CatalogRefresher.CopyTablesAndSequencesOfMixedSyncedSchemaToNewSchema: Schema 'FTDB_ORIG.dbo' contains tables obtained from the DB however it shouldn't
Source: SD.LLBLGen.Pro.ApplicationCore
Stack trace: 
   at SD.LLBLGen.Pro.ApplicationCore.MetaData.CatalogRefresher.CopyTablesAndSequencesOfMixedSyncedSchemaToNewSchema(DBSchema oldSchema)
   at SD.LLBLGen.Pro.ApplicationCore.MetaData.CatalogRefresher.CopyExistingTablesAndSequencesForSchemasWithMixedSyncMode()
   at SD.LLBLGen.Pro.ApplicationCore.MetaData.CatalogRefresher.MigrateProjectToNewCatalog()
   at SD.LLBLGen.Pro.ApplicationCore.MetaData.DatabaseMetaData.RefreshMetaData(IEnumerable`1 populatedCatalogs, LogNode refreshLogNode, Project containingProject, UserConfiguration preferences, Dictionary`2 callBacks)
   at SD.LLBLGen.Pro.ApplicationCore.MetaData.MetaDataStore.RefreshMetaData(String driverID, IEnumerable`1 populatedCatalogs, Project containingProject, UserConfiguration preferences, Dictionary`2 callBacks)
   at SD.LLBLGen.Pro.ApplicationCore.MetaData.CatalogRefresher.RefreshMetaData(Project containingProject, String driverID, List`1 populatedCatalogs, UserConfiguration preferences, Dictionary`2 callBacks)
   at SD.LLBLGen.Pro.ApplicationCore.Synchronization.SyncWithDatabaseTask.MigrateModelToNewPopulatedCatalogs()
   at SD.LLBLGen.Pro.ApplicationCore.Synchronization.SyncTaskBase.PerformWork(LogNode parentLogNode)
   at SD.LLBLGen.Pro.ApplicationCore.Synchronization.SyncTaskBase.PerformSync()
   at SD.LLBLGen.Pro.Gui.Classes.SyncTaskRunner.PerformDbSyncTask(SyncWithDatabaseTask task, LogNode parentNode)

Inner exception: <null>

If I try to run it then a second time, it says there are no changes.

So then I went ahead and did a SQL to Model sync again, and then and only then it seemed like the Schema actually updated to its real state. Once I did that and re-ran it properly detected the differences and generated the proper update script.

+++ Rick ---

Did you switch to Mixed or Model? Mixed means the db originating elements (views, procs) are still fetched from the db.

Hmm, ok, so to reproduce this, you had a schema with tables (and views? ),obtained from the DB, you switched the sync source to model or mixed, you then saw there were 2 sync tasks, one still for the db. then you executed these tasks and got this exception?

The exception is odd though. Will try to see where this originates from.

rstrahl wrote:

I'm on 5.6 just installed a few days ago...

So the issue I see is that when I bring up the Sync Database form, I see the first option to sync from SQL to Model, but not the second one to sync from Model to Db. Only one option shows.

I'm using EF Core 2.2 as the entity model framework, not sure if that matters, but I don't see why that should make a difference.

Another issue I have is that the SQL model is out of date. I updated the database by removing a field from one of the tables (to make it trigger a change for syncing specifically), but when I look on the right side at the SQL schema it still shows the field - even after a full restart.

The relational model data you see in the designer is what's in the project. The designer doesn't show you a live model, it's disconnected. So it reads the relational model data from the db in a sync with the db (or when you create a project, or in your case you import it from an edmx), and if you change the tables in the database, you have to sync it with that again.

We don't live-sync to a schema, as in most cases people want to review a ddl sql script anyway, test it before applying etc. so we sync from DB to relational model data in the view and from entity model to relational model data. the latter then can be exported as a DDL SQL UPDATE script.

Your confusion is good feedback though (And our apologies for that confusion). We'll see if we can make it more clear what to do as it currently doesn't seem to be as intuitive as we think.

Thanks Frans,

This all makes sense, but it took a bunch of experimenting to understand what the designer is doing. I totally get the 'disconnected' data model, but it's not obvious how to get it to resync.

I get it now, but I guess for me the confusion is exactly what this intermediate model represents. Now I understand that it's the current state of what the designer thinks the physical model looks like and from there I can create script to update.

The other issue is that if you 'Re-Sync' you can potentially lose some changes that were made, if you didn't apply the changes to the database.

Mind you I'm experimenting so I'm doing things that I normally would not do. Like switching back and forth between Model first and Db First of making changes. It's very cool that this is supported though, but I guess you have to be vigilent of applying changes before switching or else some changes may get overwritten or modified.

rstrahl wrote:

Thanks Frans,

This all makes sense, but it took a bunch of experimenting to understand what the designer is doing. I totally get the 'disconnected' data model, but it's not obvious how to get it to resync.

I get it now, but I guess for me the confusion is exactly what this intermediate model represents. Now I understand that it's the current state of what the designer thinks the physical model looks like and from there I can create script to update.

Yes it's something that's not intuitive if you look at it as a live view of what's there, where when you make a change to the model it will automatically change the DB for you as well. A workitem has been scheduled to make this it more clear what's going on.

We do have 'feature hints' which gives suggestions, like when you add an entity using the model first tools like QuickModel, it will show a suggestion that you should synchronize the relational model with a handy link you can click on and it will go to the UI to do so or perform the task immediately if no user intervention is needed. It's docked at the bottom but you likely unpinned it and it's docked away and you didn't see it.

The other issue is that if you 'Re-Sync' you can potentially lose some changes that were made, if you didn't apply the changes to the database.

Mind you I'm experimenting so I'm doing things that I normally would not do. Like switching back and forth between Model first and Db First of making changes. It's very cool that this is supported though, but I guess you have to be vigilent of applying changes before switching or else some changes may get overwritten or modified.

The losing changes part can happen if you make changes model first to the entity model, then sync them to the relational model data in the project (so the table meta data is updated in the project) and you switch the sync source to 'database'. This means the source of truth for the table meta data is 'the schema in the database'. The designer currently just happily switches over, ignoring the changes you made to the table in the project. The main reason for that is that the source of truth is external now and if the table in the schema in the database differs from the one in the project, the one in the database is overruling anyway. (it's the source of truth at that moment ).

I can see however that this might not be what you want. e.g. you might want to export the changes made to the table in the project first, apply those to the schema in the db, then switch over the sync source to 'database' so the changes are pulled in. Although there's a 'sync needed' remark next to the catalog name, it's easily missed. We'll add a check for this so switching the sync source from mixed/model to database will signal the user if there are changes and whether these have to be exported first or can be ignored.

I've managed to reproduce the internal error you ran into. It's caused by selecting tables in the database sync when the sync source is 'mixed'. In the 'mixed' situation, tables are synced with the entity model, (so they're all unchecked in the wizard which allows you to select which tables/views/procs to obtain the meta-data of) but checking the tables in the wizard is still possible and the driver happily pulls the table data. This then causes the exception as it checks for it, and it shouldn't happen but as the UI allows it, it can.

We'll make changes to the UI so the table section is disabled so table selection isn't possible anymore so this won't happen in the future

The ability to select tables, even though the schema/catalog were set to 'mixed' during syncing with the database (for views/procs) was caused by a bug where it tried to pull the container for the catalog using case sensitive name compares while for a sqlserver system the names are case insensitive. When that's corrected, the UI properly disables the name selection of tables and also mentions why

Fix for the issue where you can select tables for database sync when the schema sync source is 'mixed' is now available (v5.5.5 hotfix, and v5.6.1 hotfix builds)

So I'm running into another issue with syncing. I'm following the advice of the above conversation to sync database changes to see changes and then explicitly write out the data changes or assume the model and data are up to date before resynching. That mostly works.

But I ran into another scenario here:

• Field in Model that was originally mapped to a short
• Change to int in database
• Ran Sync Relational Model db->Model

When I ran this it showed no changes and sure enough the updated entity model continues to show the value as System.Byte even after resyncing.

(I haven't updated to a pre-release build but I suspect this is different than what we talked about before anyway)

+++ Rick ---

I meant to attach an image - a bit cumbersome here :-)

We'll look into it.

The reason it's not updated is because 'short' is compatible with int target fields without a conversion is needed, so it leaves them as-is. If you e.g. change the type to an incompatible type, e.g. you changed the type to a varchar field, the current model field type wouldn't be compatible with it, so it would be changed to string. The designer tries to keep the entity field types the same unless it can't otherwise to avoid breaking code. I admit, it's a choice that could be less rigid, e.g. we have settings which control whether you want entity fields' length/precision/scale to follow the mapped target field or not. A setting could be added for this, but I doubt a lot of people would use it. Nevertheless it's something we could improve in the future

On the Field mappings tab, you can correct it if you want, using the 'Sync selected...' button which operates on the selected field mappings.

The reason it's not updated is because 'short' is compatible with int target fields

So if it was the other way around, it would update the field I assume (ie. integer in the model and byte in the table then sync model->db) because that's not compatible right.

Ok that makes sense - still a little unexpected I guess.

The quick fix is just to fix that in the model manually I suppose.

rstrahl wrote:

The reason it's not updated is because 'short' is compatible with int target fields

So if it was the other way around, it would update the field I assume (ie. integer in the model and byte in the table then sync model->db) because that's not compatible right.

It's kind of odd, and no it doesn't auto-change it. So I looked into it, and it's actually a feature

It checks with the ORM framework to see whether it supports 'implicit numeric conversions', so whether the framework will convert an int to a short along the way. If the framework supports numeric conversions, it will allow type mismatches for numeric types in the model (no error is raised) and during sync (db first or model first). If the framework doesn't support implicit numeric conversions, it will raise an error in the model and will change types during sync (db first -> it will adjust the entity field's type, model first, it will change the table field's type).

So my previous answer was incomplete.

EF Core 2.1 or higher and our framework support implicit numeric conversions, the rest doesn't. So if you switch to the target framework EF 6 for instance (right-click the ORM name in the project explorer), you'll see it behaves differently as it doesn't support implicit numeric conversions.

Ok that makes sense - still a little unexpected I guess.

The quick fix is just to fix that in the model manually I suppose.

On the mappings tab of the entity, select the fields you want to correct, and click 'Sync selected' and then 'sync model field with target field'.

It's 'implicit' so it's done for you but I agree in the case you don't want a conversion but simply have a matching type, there's no way to automate this and there should be one, so a setting to automate this is still something that's preferable.