Entity Framework foreign keys naar andere database

[NickThissen]

Ik heb een bestaande MySQL database met een tabel met auto's. Deze tabel is niks meer dan een lijstje (Id, CarName) en wordt door een flink aantal verschillende applicaties gebruikt.

Nu wil ik graag een nieuwe database maken voor een nieuwe applicatie, maar in deze database wil ik foreign keys gebruiken naar de 'Cars' tabel in de oude database. In deze nieuwe applicatie heb ik het lijstje auto's enkel read-only nodig (misschien is dat belangrijk), ik wil gewoon voorkomen dat ik de tabel moet dupliceren in de nieuwe database omdat ik dan twee tabellen bijgewerkt moet houden (ik moet er regelmatig auto's aan toevoegen).

Ik lees overal (voorbeeld) dat MySQL prima foreign keys tussen twee databases ondersteunt en dat dat met SQL gewoon kan door de naam van de tabel er voor te stoppen; iets als "REFERENCES anderedb.Cars(Id)".


Ik wil deze nieuwe database echter code-first aanmaken via Entity Framework, en ook graag code-first bijhouden dmv migrations. Deze stap wil me niet lukken. In de migrations die Entity Framework aanmaakt heb ik volgens mij helemaal geen mogelijkheid om een andere database naam in te vullen. Ik probeer daarom de migrations gewoon handmatig aan te passen voordat ik het update-database commando doorvoer. Dit wel echter ook niet lukken...

EF genereert bijvoorbeeld de volgende migration code:

C#:

            CreateTable(
                "dbo.laptimes",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        UserId = c.Int(nullable: false),
                        CarId = c.Int(nullable: false),
                        Time = c.Int(nullable: false),
                        Fuel = c.Single(nullable: false),
                    })
                .PrimaryKey(t => t.Id)
                .ForeignKey("dbo.cars", t => t.CarId, cascadeDelete: true)
                .ForeignKey("dbo.users", t => t.UserId, cascadeDelete: true)
                .Index(t => t.UserId)
                .Index(t => t.CarId)

Hier maak ik dan handmatig het volgende van (verschil in regel 12):

C#:

            CreateTable(
                "dbo.laptimes",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        UserId = c.Int(nullable: false),
                        CarId = c.Int(nullable: false),
                        Time = c.Int(nullable: false),
                        Fuel = c.Single(nullable: false),
                    })
                .PrimaryKey(t => t.Id)
                .ForeignKey("AndereDatabase.cars", t => t.CarId, cascadeDelete: true)
                .ForeignKey("dbo.users", t => t.UserId, cascadeDelete: true)
                .Index(t => t.UserId)
                .Index(t => t.CarId)

Tijdens 'update-database' krijg ik echter de foutmelding:
The Foreign Key on table 'dbo.laptimes' with columns 'CarId' could not be created because the principal key columns could not be determined. Use the AddForeignKey fluent API to fully specify the Foreign Key.

Tenzij ik me heel erg vergis kan ik met de Fluent API ook geen andere database ingeven?

Ik heb ook geprobeerd om er "AndereDatabase.dbo.cars" van te maken maar dat pakt hij ook niet want dat is geen geldige tabel naam meer.
Database names must be of the form [<schema_name>.]<object_name>.

Is dit gewoon niet mogelijk?

Hoe kan ik dit toch voor elkaar krijgen?

[Alex)]

EF6 heeft geen ondersteuning voor cross-database queries, FK's, enzovoorts. Het kan gewoon niet

[Merethil]

Als het niet dezelfde database server is, dan kan je een linked server aanmaken: http://stackoverflow.com/...reate-linked-server-mysql

Zo wel, dan zou ik gewoon een stored procedure of function maken die je aanroept met EF, eventueel terug laat mappen naar een model indien nodig. Of eventueel een view op de andere database die je weer in EF inlaadt.

[EfBe]

Je code gebruikt SQLServer, je hebt het over mysql. Mysql heeft wel catalogs maar geen schemas, 'dbo' kent het niet. Alex) heeft het goede antwoord: EF ondersteunt geen cross catalog queries, alle metadata in EF bevat geen catalog informatie, dus je kunt geen 2 catalogs in je metadata hebben. Je moet daarvoor andere ORMs gebruiken. Laten die nou ook nog beter zijn dan EF, dus je slaat meerdere vliegen in 1 klap

Wil je per se doorgaan met de traagste ORM op .NET, dan is je enige optie schemas binnen een catalog, maar zoals gezegd dan is MySQL niet geschikt, die kent geen schemas.

[ Voor 17% gewijzigd door EfBe op 05-12-2015 11:02 ]

[Merethil]

EfBe schreef op zaterdag 05 december 2015 @ 10:59:
Je code gebruikt SQLServer, je hebt het over mysql. Mysql heeft wel catalogs maar geen schemas, 'dbo' kent het niet. Alex) heeft het goede antwoord: EF ondersteunt geen cross catalog queries, alle metadata in EF bevat geen catalog informatie, dus je kunt geen 2 catalogs in je metadata hebben. Je moet daarvoor andere ORMs gebruiken. Laten die nou ook nog beter zijn dan EF, dus je slaat meerdere vliegen in 1 klap

Wil je per se doorgaan met de traagste ORM op .NET, dan is je enige optie schemas binnen een catalog, maar zoals gezegd dan is MySQL niet geschikt, die kent geen schemas.

Een ander ORM is een optie, maar als je een MySQL database wilt queryen, slechts om read-only access te hebben op een simpele tabel waarin niets meer dan key-value-pairs staan en daarom van het "gemak" van EF af te stappen... Tja. Ik zou gewoon een linked server aanmaken en een stored procedure bouwen die de boel ophaalt. Waarom zou je de CRUD van een ORM willen hebben als je alleen read-only op die tabel wilt?

Ik ben bekend met het ORM dat jij bouwt, en ik snap dat dit ORM een stuk beter is dan EF, maar gelijk aanraden iets anders te gebruiken dan iets wat ze misschien al door de hele codebase heen (buiten dit project om) gebruiken of wat gewoon het simpelst is vanwege kosten/baten vind ik lichtelijk overdreven.

[NickThissen]

Jammer dat het niet kan. Ik blijf inderdaad bij EF, de rest van de code maakt daar al gebruik van en dit werkt helemaal prima (ook met MySQL).

Wat bedoelen jullie precies met een stored procedure / view gebruiken? Ik ben daar niet super bekend mee maar ik dacht dat dat in principe niets meer was dan een soort vooraf ingebouwde query? Wat schiet ik daar mee op, dan krijg ik een lijstje met (Id, CarName) maar heb ik nog steeds geen harde koppeling met de nieuwe database.

Ik kan natuurlijk gewoon elke keer bij opstarten van de applicatie het lijstje (Id, CarName) ophalen uit de oude database, en daarna verder werken op de nieuwe database. Maar het "probleem" is dan dat er geen harde koppeling is met CarId op de nieuwe database en dat ik die dus in de code zelf moet laten kloppen (altijd zorgen dat er geen ongeldig CarId in zit etc). Daarnaast kan ik dan niet "automatisch" de CarName laten ophalen en moet ik dus een of andere cache bouwen waar ik steeds de details van de auto op haal als die nodig zijn.

Is er een betere oplossing?

[Merethil]

NickThissen schreef op zaterdag 05 december 2015 @ 12:06:
Jammer dat het niet kan. Ik blijf inderdaad bij EF, de rest van de code maakt daar al gebruik van en dit werkt helemaal prima (ook met MySQL).

Wat bedoelen jullie precies met een stored procedure / view gebruiken? Ik ben daar niet super bekend mee maar ik dacht dat dat in principe niets meer was dan een soort vooraf ingebouwde query? Wat schiet ik daar mee op, dan krijg ik een lijstje met (Id, CarName) maar heb ik nog steeds geen harde koppeling met de nieuwe database.

Ik kan natuurlijk gewoon elke keer bij opstarten van de applicatie het lijstje (Id, CarName) ophalen uit de oude database, en daarna verder werken op de nieuwe database. Maar het "probleem" is dan dat er geen harde koppeling is met CarId op de nieuwe database en dat ik die dus in de code zelf moet laten kloppen (altijd zorgen dat er geen ongeldig CarId in zit etc). Daarnaast kan ik dan niet "automatisch" de CarName laten ophalen en moet ik dus een of andere cache bouwen waar ik steeds de details van de auto op haal als die nodig zijn.

Is er een betere oplossing?

Een stored procedure is een vooraf vastgestelde query die je kan uitvoeren om data te updaten/inserten/selecten. Aangezien jij een "read-only" variant van die ene tabel wilt hebben, kan je toch gewoon een stored procedure maken die de data ophaalt bij aanroepen van die procedure, en hem dan in geheugen als tabel benaderen? Het is in jouw geval echt niets meer dan Id en BrandName (oid), dus een hele simpele key-value-pair (netjes voor in een dictionary).

Een view leg je over een tabel (of meerdere) heen en daarmee stel je vooraf vast hoe je wilt dat deze "nieuwe tabel" (de view) de table toont; dit is handig als je alleen data wilt hoeven lezen en niets vast wilt leggen verder.

De view leg je aan in de database met welke je huidige applicatie werkt, en die ligt over de tabel van de andere server heen. Omdat het een andere server is (of is het alleen letterlijk een andere database?) moet je die wel als linked server (of de MySQL-variant daarvan) aanleggen voor je dat kan doen.
Een stored procedure zou je zo simpel kunnen maken als "SELECT * FROM [OtherServer].[CARS].[dbo].[Brand]" oid, daardoor haal je in je nieuwe applicatie die data altijd op dezelfde manier op en hoef je verder niet de hele CRUD van EF er overheen te leggen.

Hier staat uitgelegd hoe je de stored procedure zou kunnen aanroepen in EF: MSDN: Stored Procedures in the Entity Framework
Als je aangeeft wat er uit de stored procedure komt zou EF het zelfs makkelijk naar een object kunnen mappen voor je (gok ik, ik ben meer bekend met NHibernate en die kan dat prima, dus gok dat EF het ook wel kan).

Let vooral ook op mijn termen LINKED SERVER en de equivalent van MySQL daarvan (FEDERATED Engine in MySQL). Daarmee kan je een harde link tussen DatabaseServer1 en DatabaseServer2 leggen, en kunnen ze op elkaar queryen. Het enige wat je niet kan gebruiken is EF om direct de tabellen te bereiken, maar met functions/stored procedures/views kan je EF doen denken alsof het allemaal op één database server draait.

[ Voor 8% gewijzigd door Merethil op 05-12-2015 15:22 ]

[NickThissen]

Het is dezelfde server, maar een andere database.

Een stored proc of view is dus zoals ik al dacht niks meer dan een vooraf ingesteld query die lekker efficient alle auto's kan ophalen. Echter kan ik dan niet dit doen:

C#:

public class User
{
    public int Id {get; set;}

    public virtual int CarId {get; set;}
    public virtual Car Car {get; set;}
}

// Ergens anders
var user = Database.Users.Find(4);
MessageBox.Show(user.Car.CarName);

De koppeling van user.Car werkt dan natuurlijk niet, toch?

Dus zou ik in de User class iets moeten inbouwen dat hij vanuit CarId de bijbehorende Car haalt. Of dat nou de naam alleen is (string) of een object met wat meer properties is denk ik niet zo relevant, maar er moet ergens code zitten om de naam (of object) op te halen vanuit de eerder opgehaalde (cache) lijst met auto's.

Bijvoorbeeld

C#:

public class User
{
    public int Id {get; set;}

    public virtual int CarId {get; set;}

    private Car _car;
    [NotMapped]
    public Car Car
    {
        get
        {
            if (_car == null)
            {
                _car = OtherDatabaseCache.Instance.Cars[this.CarId];
            }
            return _car;
        }
    }
}

waarbij OtherDatabaseCache.Instance.Cars dus een dictionary oid is die ik bij het opstarten uit de andere database laadt. Of dat nou met een stored proc of gewoon een normale call is maakt denk ik toch niet veel uit?

Dit zal allemaal wel werken maar ik ben dan dus de harde koppeling tussen User-Car kwijt en moet handmatig de Car op Id gaan zoeken (en eigenlijk ook altijd nachecken dat het een geldige ID is etc).


Of snap ik nou nog steeds niet het nut van een stored proc / view?

[Merethil]

Dit klopt dan wel ongeveer. Het is wel gek dat EF niet overweg kan met een tweede database, bij Hibernate in Java is dat geen probleem en NHibernate ook niet voor .Net

Hoe dan ook: je kan dan inderdaad niet vanuit EF de koppelingen leggen, die zou je zelf moeten schrijven. Helaas wat meer werk, maar naar mijn mening beter dan van ORM wisselen (als je al EF overal gebruikt) of de tabel dupliceren...

Een stored procedure/view had alleen echt handig geweest als je het niet specifiek als een tabel met fk's wilt zien. Wij doen zelf het grootste deel van dat soort relaties trouwens ophalen door juist stored procedures in te zetten omdat dat 1: veel sneller is, 2: makkelijker te wijzigen is en 3: wij weten dat we niet snel van db-type zullen wijzigen.
ORM's zijn juist vooral handig voor simpele crud en onafhankelijk zijn van het type RDBMS.

[EfBe]

Merethil schreef op zaterdag 05 december 2015 @ 11:10:
[...]


Een ander ORM is een optie, maar als je een MySQL database wilt queryen, slechts om read-only access te hebben op een simpele tabel waarin niets meer dan key-value-pairs staan en daarom van het "gemak" van EF af te stappen... Tja. Ik zou gewoon een linked server aanmaken en een stored procedure bouwen die de boel ophaalt. Waarom zou je de CRUD van een ORM willen hebben als je alleen read-only op die tabel wilt?

Ik ben bekend met het ORM dat jij bouwt, en ik snap dat dit ORM een stuk beter is dan EF, maar gelijk aanraden iets anders te gebruiken dan iets wat ze misschien al door de hele codebase heen (buiten dit project om) gebruiken of wat gewoon het simpelst is vanwege kosten/baten vind ik lichtelijk overdreven.

Ik heb de mijne niet genoemd, je kunt ook nhibernate gebruiken als je wilt. Kosten lijken me onzin, er is al meer tijd verkloot in deze thread dan wat een andere ORM kost, zeker vandaag de dag met bv Linq (alhoewel NHibernate dan weer wat moeite heeft). Verder vind ik het toch wel triest dat er meteen conclusies worden getrokken alsof ik hier mijn spullen loop te verkopen. Misschien weet ik meer van het onderwerp dan veel mensen domweg omdat ik de laastste 13+ jaar fulltime bezig ben met ORMs en tooling daarvoor te bouwen?

Anyway: linked server werkt niet, want je moet dan nog steeds een catalog opgeven, je proc verhaal kan, in EF kun je een entity op een stored proc result mappen maar het is allemaal niet erg flexibel (niet de schuld van EF) omdat navigation naar related entities die op een proc zijn gemapped niet gaat, immers je kunt niet joinen met een proc.

TVFs zou kunnen, maar MySQL ondersteunt die niet.

Wat eventueel wel kan is views aanmaken in de gebruikte catalog die simpelweg een select * doen op de tables in de andere catalog, en dan entities mappen op die views en dan in het model relationships leggen tussen die entities. Dit is niet altijd mogelijk, EF wil nl. dat je PKs definieert op non-nullable fields, en de fields in de views moeten dus non-nullable zijn, iets wat niet alle databases kunnen aangeven in hun meta-data.

[ Voor 7% gewijzigd door EfBe op 06-12-2015 11:18 ]

[Merethil]

EfBe schreef op zondag 06 december 2015 @ 11:08:
[...]

Ik heb de mijne niet genoemd, je kunt ook nhibernate gebruiken als je wilt. Kosten lijken me onzin, er is al meer tijd verkloot in deze thread dan wat een andere ORM kost, zeker vandaag de dag met bv Linq (alhoewel NHibernate dan weer wat moeite heeft). Verder vind ik het toch wel triest dat er meteen conclusies worden getrokken alsof ik hier mijn spullen loop te verkopen. Misschien weet ik meer van het onderwerp dan veel mensen domweg omdat ik de laastste 13+ jaar fulltime bezig ben met ORMs en tooling daarvoor te bouwen?

Anyway: linked server werkt niet, want je moet dan nog steeds een catalog opgeven, je proc verhaal kan, in EF kun je een entity op een stored proc result mappen maar het is allemaal niet erg flexibel (niet de schuld van EF) omdat navigation naar related entities die op een proc zijn gemapped niet gaat, immers je kunt niet joinen met een proc.

TVFs zou kunnen, maar MySQL ondersteunt die niet.

Wat eventueel wel kan is views aanmaken in de gebruikte catalog die simpelweg een select * doen op de tables in de andere catalog, en dan entities mappen op die views en dan in het model relationships leggen tussen die entities. Dit is niet altijd mogelijk, EF wil nl. dat je PKs definieert op non-nullable fields, en de fields in de views moeten dus non-nullable zijn, iets wat niet alle databases kunnen aangeven in hun meta-data.

Excuus, zo bedoelde ik het niet. Ik bedoelde meer dat om een issue heen werken niet altijd hoeft te worden gedaan met een ander ORM gebruiken.

Gebruiken van NHibernate bijvoorbeeld kost ook geld: training in gebruik, vervangen van alle delen waar EF al gebruikt wordt (want wie wil er meer dan 1 ORM in zijn codebase hebben?), dus een oplossing met EF zelf is eerder gewenst lijkt me.

In alle gevallen: excuus, mijn opmerking sloeg eerder op "er zijn betere oplossingen, ja, maar dat is niet zomaar de oplossing voor TS' probleem".

[EfBe]

No worries