Load DataModel from Multi Databases and Multi Providers

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";
                conn.Open();
                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

---

http://fromisraeltolebanon.info

Answer 1

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

Answer 2

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

  <system.data>
    <DbProviderFactories>
      <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=2.5.0.0, Culture=neutral, PublicKeyToken=3750abcc3150b00c" />
    </DbProviderFactories>
  </system.data>

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";
            purchasingDSinstance.Supplier.AddSupplierRow(row);

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

---

http://fromisraeltolebanon.info

Answer 3

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

---

http://fromisraeltolebanon.info

Answer 4

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

Answer 5

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

---

http://fromisraeltolebanon.info

Answer 6

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