Load DataModel from Multi Databases and Multi Providers RRS feed

  • Question

  • hi
    i have a data model like that (Suppliers, Orders, OrderDetails, Items) my problem is the Suppliers and items data comes from different database (firebird not ms sqlserver) and the data on those tables will be readonly, in previous dataset Model i could load them by writing a moethod to fill those tables, when i try to create an entity connection to firebird it give me exception when i try read from the database it gives me exception which say that Ssdl for sqlClient is different than the firbird client
    using (EntityConnection conn = new EntityConnection(fbcb.ToString()))
                    MyModelEntities3 poe = new MyModelEntities3 (conn);
                    ObjectQuery<Supplier> q = poe.CreateQuery<Supplier>(
                       "select c.Cus_id AS SupplierID, c.cmp_name AS SupplierName from Supplier c where c.cus_type <> 0");
                    return q.Execute(MergeOption.AppendOnly);

    also i had tried to use the old way but it didn't work either still the supplier data comes from my sqlServer database and nothing loaded from the following method

    internal void LoadSuppliers(MyDataModel3 poe)
                FbConnectionStringBuilder fbcb = new FbConnectionStringBuilder();
                //fbcb.ServerType = FbServerType.Embedded;
                fbcb.ServerType = FbServerType.Embedded;
                fbcb.UserID = "sysdba";
                fbcb.Password = "masterkey";
                fbcb.DataSource = "LocalHost";
                fbcb.Database= @"D:\DataCenter\Omar\Data\OrigionalDB.GDB";
                using (FbConnection conn = new FbConnection(fbcb.ToString()))
                    FbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "select c.Cus_id AS SupplierID, c.cmp_name AS SupplierName from Customers c where c.cus_type <> 0";
                    using (FbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
                        while (dr.Read())
                            poe.AddToSuppliers(Supplier.CreateSupplier(dr.GetInt32(0), dr.GetString(1)));

    so how can we do this in EntitiesDataModel

    note: i created ItemTable and SupplierTable in my SQLServer database to easly generate Mymodel from it

    thanx in advance
    Sunday, May 3, 2009 3:00 AM

All replies

  • Hi *,

    if I understand your question correctly, you'll need two SSDL (and maybe MSL) files. Each for every database. The SSDL is store related, and even if the structure is same you have to use the proper one. There may be different types and mainly there's in metadata saved info about the provider (and DB version (token)).
    Jiri {x2} Cincura
    Sunday, May 3, 2009 5:50 PM
  • Hi,
    Thank you Jiri for response, actualy i'm very new to Entity framework, so forgive my ignorance

    first - because i use Embeded Firebird version and Local FirebirdClient (not installed on the GAC) i add the factory to my App.Config as following

          <add name="FirebirdClient Data Provider" invariant="FirebirdSql.Data.FirebirdClient" description="FirebirdClient - ADO.NET 2.0 Data Provider" type="FirebirdSql.Data.FirebirdClient.FirebirdClientFactory, FirebirdSql.Data.FirebirdClient, Version=, Culture=neutral, PublicKeyToken=3750abcc3150b00c" />

    this step were my first problem but your blog helped me out to solve that because at begining i got the assembly you referencing doesn't has correct factory or something like that i don't  remember the message, f course with little change becasuse i use the Client.Dll locally not from the GAC i didn't do that in the machine.Config,
     i didn't get a change to thank you for that, so Thank you first

    Second - where is that SSDL reside i searched my solution and even my solution folder i didn't found anything with that extension or name except in the connection string, they put that myModel.SSDL; ...etc

    third - i think i miss something here about entity framework, i get lost when i try to relate ADo.net dataset to entity framework to find a start point to understand things, in dataset (Dataset is something and DataAccess is other thing) i mean i can Generate a dataset but i can access data by hard coding a TableAdapter and ignore the auto generated one, but i think this is not the case here

    for example in dataset case i can do something like this

                PurchasingrDataSet.SupplierRow row = purchasingDSinstance.Supplier.NewSupplierRow();
                row.SupplierID = 1;
                row.SupplierName = "any Name";

    so how can i do things like that
    i mean how to create object and to add it to my datamodel because when i tray this it didn't work in myfrist post code
    mymodel.AddToSuppliers(Supplier.CreateSupplier(dr.GetInt32(0), dr.GetString(1)));

    every documention i've read just talking about how easy to query "select" or at most talking about editing foreach (object in myqueryResult) change so and so  at ent SaveChanges()

    sorry i have lots of questions in my head about this framework, so if anyone kindly forward to me a good source to learn from i'll be thankfull

    thanks in advance
    Monday, May 4, 2009 3:37 AM
  • Hi,

    i found this SSDL file its embeded in the Mymode.edmx i've  created other version from it, it ended up like i had to override the three fiels SSDl CSDl ml but still not working, actualy all what  i need is to fill my two of my ObjectsSet not all of them i had started with just one ObjectSet in my firebird of myModel, i get runtime error which say there are proeprties in the object which are not mabed actualy those properties are navigation properties which i don't need, so i think i have to write the mapping xml to it even if i don't need them.

    i think entity Framework doesn't separate the object model from the database mapping, i mean each object must be coubled with database mapping so if i want to load my model from 2 or 3 databases i have to remapping every thing even if i don't want them << this is not good

    so the question which i didn't get answer for it is how to add to my objectset by using code??

    thanks in advance
    Wednesday, May 6, 2009 5:21 PM
  • Hi *,

    of course you need to map all items from CSDL into new SSDL. Simply, that's it.

    What do you mean by ObjectSet? You mean EntitySet?
    Jiri {x2} Cincura
    Wednesday, May 6, 2009 6:19 PM
  • hi,
    thanks Jiri for your response, as i said i have many tables in my model i just want to load one table from other database, so to rewrite the entire ssdl is crazy job to do i would prefer to use dataset for that its much handy i don't say its best but controlable,

    i would prefer if there is a way to hard code independent sql command and to load my EntitySet from it, but the problem is when i do i find nothing in my EntitySet like this

    MyModel.AddToSuppliers(Supplier.CreateSupplier(1, "someone Name"));
    the suppliers EntitySet.Suppliers.Count still 0

    Thanks in advance
    Thursday, May 7, 2009 6:00 PM
  • Hi *,

    but the EF doesn't know you need just one table. That's why it's done like it is. You can hardcode SQL into your model, using either DefiningQuery or QueryVie. For you probably the DefiningQuery suits the needs.

    Did you call SaveChanges? If not, then it's not saved into database and context (I hope you mean this, cause with something called EntitySet it doesn't make sense) .Suppliers.Count will be 0 for sure.
    Jiri {x2} Cincura
    Thursday, May 7, 2009 6:07 PM