LLBLGen 5.0, EF6 Multiple SQL Server Databases, Single Model

Posts   
 
    
Posts: 47
Joined: 05-Feb-2008
# Posted on: 22-Oct-2016 15:51:02   

Hi there,

We are using 5.0 (5.0.7) RTM Build Date 18-Oct-2016 (congrats on the release of version 5.0 by the way) and trying to target EF6 (as the client would prefer to use this instead of LLBLGen Pro Runtime Framework), but when trying to create a single Entity Model containing multiple different SQL Server Databases (all on the same SQL Server instance, but completely separate databases with different tables etc), we are getting the designer validation error...

"The entity model is mapped to more than one catalog for database 'SQL Server'. This isn't supported by the Entity Framework."

Switching the target framework to LLBLGen Pro Runtime Framework then removes this error obviously. It seems fairly clear cut, but just to confirm then can we not have a single LLBLGen Pro project that targets EF6 and utilises multiple SQL Server databases in a single model?

Thanks as always.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Oct-2016 10:17:53   

Sadly, EF6 doesnt support multiple catalogs in one model: it doesn't have ways to map elements to multiple catalogs, just the one connected to through the connection string. It supports multiple schemas per catalog though.

Not sure if the catalogs can be merged as schemas inside one catalog. If so, that can solve the problem. You could of course create multiple projects, each map a model to 1 catalog, but that is a bit tedious too, as at runtime, you have to switch between different DbContext instances as it has 1 modelbuilder cached per database provider (SQL Server, postgresql etc.)

Frans Bouma | Lead developer LLBLGen Pro
Posts: 47
Joined: 05-Feb-2008
# Posted on: 23-Oct-2016 10:38:10   

Thanks for the speedy response, it's appreciated. There are basically multiple existing databases at the client together with multiple new ones related to the new solution we are building. Use of all of them is meant to be getting centralised via new RESTful services we are creating, so the solution currently has multiple separate LLBLGen projects, one per database, each generating their own set of EF6 Model and Persistence projects. This should be okay for simpler scenarios, using multiple DBContext instances like you said, but we need to create our GenericRepository, UnitOfWork etc classes with this in mind now so that we can hopefully only have one of each still overall. Also the following point to the type of thing we may have to do to have transactions that span the multiple DBContext instances...

http://stackoverflow.com/questions/21202982/one-transaction-with-multiple-dbcontexts http://stackoverflow.com/questions/8763248/how-to-force-only-one-transaction-within-multiple-dbcontext-classes

...unless you have other suggestions/recommendations in this regard.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 24-Oct-2016 13:05:58   

patramadass wrote:

Thanks for the speedy response, it's appreciated. There are basically multiple existing databases at the client together with multiple new ones related to the new solution we are building. Use of all of them is meant to be getting centralised via new RESTful services we are creating, so the solution currently has multiple separate LLBLGen projects, one per database, each generating their own set of EF6 Model and Persistence projects. This should be okay for simpler scenarios, using multiple DBContext instances like you said, but we need to create our GenericRepository, UnitOfWork etc classes with this in mind now so that we can hopefully only have one of each still overall.

Is your client really willing to go through that pain? I mean why do all that, while there's a solution available that works out of the box? EF6 is dead anyway, and dog slow! I'm curious, what are their arguments to choose in favor of MS' tech instead of ours, if you add all this custom code you have to write (and they or you have to maintain!) to the long list of EF6 limitations?

Also the following point to the type of thing we may have to do to have transactions that span the multiple DBContext instances...

http://stackoverflow.com/questions/21202982/one-transaction-with-multiple-dbcontexts http://stackoverflow.com/questions/8763248/how-to-force-only-one-transaction-within-multiple-dbcontext-classes ...unless you have other suggestions/recommendations in this regard.

that's indeed another problem. You could use System.Transactions.TransactionScope though. the ado.net transaction is then enlisted in the ambient transaction. It's not faster though.

An LLBLGen Pro project where you have all catalogs in 1 project with 1 model on top of it is much easier.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 47
Joined: 05-Feb-2008
# Posted on: 24-Oct-2016 13:26:30   

The mandate is only to use Microsoft and free third party tools/frameworks unfortunately. Same applies for the UI in terms of using paid for UI component suites etc even though they would reduce development team and improve UX. I agree with you though and we would prefer using the LLBLGen Pro Runtime framework, but the problem is they would obviously need to purchase a license to generate code etc. Will see how this setup works for now and perhaps revisit the discussion with them again before going to much further as this RESTful layer we are creating may end up spanning a very large amount of databases and tables as it grows.

Do you have any direct comparisons for LLBLGen Pro vs EF etc that we could perhaps pass on?

On a side note, what are your plans for EF going forward? Will you be looking to support EF Core in the future for example?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 25-Oct-2016 09:40:47   

patramadass wrote:

The mandate is only to use Microsoft and free third party tools/frameworks unfortunately. Same applies for the UI in terms of using paid for UI component suites etc even though they would reduce development team and improve UX. I agree with you though and we would prefer using the LLBLGen Pro Runtime framework, but the problem is they would obviously need to purchase a license to generate code etc. Will see how this setup works for now and perhaps revisit the discussion with them again before going to much further as this RESTful layer we are creating may end up spanning a very large amount of databases and tables as it grows.

Yeah because it's a bit of a penny wise pound foolish situation: you now have to write custom code to hammer EF6 into a shape to behave like you want, which takes time, money and they then have to maintain it for the lifespan of the app.

Do you have any direct comparisons for LLBLGen Pro vs EF etc that we could perhaps pass on?

Not in a ready to hand-out slide, but in short it's something like: EF features llblgen pro doesn't support: - complex types (DDD value types) (the designer supports them for our framework, they're inlined at code generation time), - union queries, - multiple inheritance hierarchy types per inheritance hierarchy (no-one uses those), - entity mapped to multiple tables, - entity actions (CRUD) mapped onto stored procedures (which is very limiting in practice)

LLBLGen Pro features not supported by EF (incomplete list, available today!): - separate unit of work, - multiple catalogs per model, - per call catalog/schema switching, - multiple query systems which are easy to extend, - type converters, - action combinations per entity (e.g. readonly/read/delete etc.), - built-in auditing/validation/authorization/flexible concurrency system, - read-only types mapped on non-pk targets (typed views), - sequences, - m:n relationships which are objectified (intermediate entity has non-pk fields), - datascopes, - value versioning inside entities, - in-entity change tracking (so no need for a central context, and the dreaded attach API of EF), - advanced eager loading/prefetching with filters/sorting/joins per graph node, - auto-tuning of eager loaded/prefetch queries - nested/hierarchy queries in linq - SQL Server 2012/2016 feature support (like the upcoming temporal tables, or newsequentialid()). EF hasn't received specific sql server feature support since 2008. - resultset caching, - field excluding per query which can then efficiently fetched afterwards, - ability to directly execute update/delete statements to execute update/delete actions in bulk, - support for having clauses with group by, - more advanced linq support (custom in-memory code in projection is dealt with fine, EF crashes), - transaction savepoints.
- SQL Server user defined types support

On top of that, we actually fix bugs in a reasonable timeframe, if possible (e.g. within the same week, most of the time within a day). You get an answer from us directly within 24 hours. We actively develop the product and keep you running, MS has practically abandoned EF6 users and moved on to EFcore, which isn't compatible with EF6. MS also is slow in answering questions and bugs are fixed (if they're fixed!) over a very long period of time, sometimes even a year or longer.

On a side note, what are your plans for EF going forward? Will you be looking to support EF Core in the future for example?

EF core support will actually ship this week in the 5.1 beta. During the week I spent on this, I ran into 3 major bugs in EFcore 1.0.1. I reported them, and they're all put on the backburner for 1.2. That's 2017. It's also a very limited framework, where most microORMs have more features. I have no idea why people would use this in production. I mean, left joins (DefaultIfEmpty() in linq) and group by are done in-memory. It rewrites queries to use defaultifempty if needed, and thus you'll never know if it will run all code in memory, or will pull half your DB into memory. This will improve a bit in 1.1, but not that much. This is basic stuff all other ORMs have nailed down for many years now, they're solved problems.

People should do what they think is best, and for their clients, but as a specialist in data-access, I can't recommend EF core to anyone at this point, and I don't see this change soon.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 47
Joined: 05-Feb-2008
# Posted on: 26-Oct-2016 11:53:52   

Otis wrote:

Yeah because it's a bit of a penny wise pound foolish situation: you now have to write custom code to hammer EF6 into a shape to behave like you want, which takes time, money and they then have to maintain it for the lifespan of the app.

Agreed, total cost of ownership will definitely be higher while also limiting what can be easily achieved overall. It's also meant to be the foundation for a new integration layer that will hopefully be used for years to come, so getting it wrong at this level will have considerable knock on effects. We are going to use the information you have provided below and broach the subject with them again.

Otis wrote:

Not in a ready to hand-out slide, but in short it's something like: EF features llblgen pro doesn't support: - complex types (DDD value types) (the designer supports them for our framework, they're inlined at code generation time), - union queries, - multiple inheritance hierarchy types per inheritance hierarchy (no-one uses those), - entity mapped to multiple tables, - entity actions (CRUD) mapped onto stored procedures (which is very limiting in practice)

LLBLGen Pro features not supported by EF (incomplete list, available today!): - separate unit of work, - multiple catalogs per model, - per call catalog/schema switching, - multiple query systems which are easy to extend, - type converters, - action combinations per entity (e.g. readonly/read/delete etc.), - built-in auditing/validation/authorization/flexible concurrency system, - read-only types mapped on non-pk targets (typed views), - sequences, - m:n relationships which are objectified (intermediate entity has non-pk fields), - datascopes, - value versioning inside entities, - in-entity change tracking (so no need for a central context, and the dreaded attach API of EF), - advanced eager loading/prefetching with filters/sorting/joins per graph node, - auto-tuning of eager loaded/prefetch queries - nested/hierarchy queries in linq - SQL Server 2012/2016 feature support (like the upcoming temporal tables, or newsequentialid()). EF hasn't received specific sql server feature support since 2008. - resultset caching, - field excluding per query which can then efficiently fetched afterwards, - ability to directly execute update/delete statements to execute update/delete actions in bulk, - support for having clauses, - more advanced linq support (custom in-memory code in projection is dealt with fine, EF crashes), - transaction savepoints.
- SQL Server user defined types support

Thanks for this, it's really worth having it on a page somewhere like "Why LLBLGen Pro?". Without trying to attack EF or other ORMs directly, it would make it a lot easier for people to understand why, where and how your solution is better overall.

Otis wrote:

On top of that, we actually fix bugs in a reasonable timeframe, if possible (e.g. within the same week, most of the time within a day). You get an answer from us directly within 24 hours. We actively develop the product and keep you running, MS has practically abandoned EF6 users and moved on to EFcore, which isn't compatible with EF6. MS also is slow in answering questions and bugs are fixed (if they're fixed!) over a very long period of time, sometimes even a year or longer.

Yes, we have been using your solution since the 2.x days from the looks of it and your support (including this discussion) has always been excellent. It is a massive plus knowing that you have the backing of the key developers and that they are directly involved in issue resolution with fixes/updates being possible without layers of red tape etc. Will also motivate with this in mind.

Otis wrote:

EF core support will actually ship this week in the 5.1 beta. During the week I spent on this, I ran into 3 major bugs in EFcore 1.0.1. I reported them, and they're all put on the backburner for 1.2. That's 2017. It's also a very limited framework, where most microORMs have more features. I have no idea why people would use this in production. I mean, left joins (DefaultIfEmpty() in linq) and group by are done in-memory. It rewrites queries to use defaultifempty if needed, and thus you'll never know if it will run all code in memory, or will pull half your DB into memory. This will improve a bit in 1.1, but not that much. This is basic stuff all other ORMs have nailed down for many years now, they're solved problems.

People should do what they think is best, and for their clients, but as a specialist in data-access, I can't recommend EF core to anyone at this point, and I don't see this change soon.

We looked at EF Core briefly for this project, but yes it's too risky to even consider at this stage which really left EF6 as the default option, but like I said we are going to chat to the client and see what can be done. It's early enough in the development for the change to be made. It will be more time and effort from our side now, but it will be worthwhile considering the future plans for this Web API etc.

Thank you again for all your input here as always, it is appreciated. Let's see what the client says about this all and hopefully we can make the switch still. One other thing I wanted to mention is that we are using your Derived Models feature in version 5.0 for our DTOs, seems to be working well so far, so good job there.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 27-Oct-2016 10:20:05   

Thanks for the feedback/kind words simple_smile

Frans Bouma | Lead developer LLBLGen Pro