How to insert a row using Entity Framework?

Question

Hi ,
I have done it by Linq to Sql and it works, but I dont know how to do it by Entity Framework.
I have three tables in DB. Table User (UserID, CarTypeID, RoadTypeID) , Table CarType(CarTypeId, typename) , Table RoadType(RoadTypeID, RoadType) , and 2 foreign key .

When I try to insert by
MyDbEntities mydb=new MyDbEntities();
User  us=new User();
User.CarTypeID =1 ;   // I cannot do this since there is not a field CarTypeID. compiled failed
User.CarType.CarTypeID=1;  // this compile ok ,  but it generate an error when runtime say no instance.
User.RoadType.RoadTypeID=1;
mydb.AddtoUser(us);
mydb.SaveChange();

How do I do it?   why the CarTypeID column is disapear?

Answer 1

LINQ to SQL is mostly a 1-1 mapping between classes and the database and as such it has foreign key properties on the entities (just like in the database) and these can just be modified directly.

 

In the case of the entity framework, the model has relationships as first class citizens and foreign keys are just artifacts in the database which those relationships are mapped to.  So, to accomplish the scenario you have above, you would need to do one of two things:

 

1) Retrieve the related car entity and set the entity reference onto the navigation property for the entity -- something like this:

 

MyDbEntities mydb=new MyDbEntities();
User  us=new User();

User.CarType = mydb.CarType.Where(ct => ct.CarTypeID == 1).First();
User.RoadType = mydb.RoadType.Where(rt => rt.RoadTypeID == 1).First();
mydb.AddtoUser(us);
mydb.SaveChange();

 

2) If, as in these cases, the relationship is such that you have a reference on your entity (rather than a collection), then you can set the EntityKey of the related entity on the reference which can be done without a query to the database.  That would look something like this:

 

MyDbEntities mydb=new MyDbEntities();
User  us=new User();

User.CarTypeReference.EntityKey = new EntityKey("MyDb.CarType", "CarTypeID", 1);
User.RoadTypeReference.EntityKey = new EntityKey("MyDb.RoadType", "RoadTypeID", 1);
mydb.AddtoUser(us);
mydb.SaveChange();

 

- Danny

Answer 2

Thanks,
after some compare , I think Linq To Sql is enough to me.

Answer 3

Hi Danny,

Thanks for your help, however, I'm still having problems.  Let me tell you my situation.

I'm working with a Windows Forms application which connects to a webservice I have made on the server, which returns serialized ADO.NET Entities.  Then in my forms application, I have SqlCompact as my disconnected database.  So basically I need to map and copy all the entities from the webservice into my SqlCompact schema, which basically is the same thing, just with fewer columns.

So here is an example (i've simplified it a little for this example)...


//Copying city entities from the server and inserting them into the SqlCompact database(cache).

Cache.DataEntities context = new Cache.DataEntities();

foreach(Server.Cities serverCity in GetAllServerCities())
{

Cache.Cities cachedCity = new Cache.Cities();

cachedCity.ID = cachedCity.ID;

cachedCity.CityName = serverCity.CityName;

//It drives me crazy that I have to type in these parameters manually.
//I thought the whole idea of O/R mapping was to absract you away from the database.
//There should at least be a property on the generated Entity object which I can call to get this information.
//What happens if the column name changes or something, I won't get any complie time error!

cachedCity.CountryReference.EntityKey = new EntityKey("DataEntities.Cities", "CountryID", serverCity.Country.ID);

//Why can't I just say this?
//cachedCity.Country.ID = serverCity.Country.ID;

         

context.AddToCities(cachedCity); //The error is thrown here.

}

context.SaveChanges(); 

Anyway, even when I run this, I get this error - "System.InvalidOperationException was unhandled
  Message="The relationship cannot be defined because the EntitySet name 'DataEntities.Cities' is not valid for the role 'Country' in association set name 'DataEntities.CitiesAndCountriesRelationship'."
  Source="System.Data.Entity"
  StackTrace:
       at System.Data.Objects.DataClasses.RelatedEnd.CheckRelationEntitySet(EntitySet set)
       at System.Data.Objects.DataClasses.RelatedEnd.AttachContext(ObjectContext context, EntitySet entitySet, MergeOption mergeOption)
       at System.Data.Objects.DataClasses.RelationshipManager.AttachContext(ObjectContext context, EntitySet entitySet, MergeOption mergeOption)
       at System.Data.Objects.ObjectContext.AddSingleObject(EntitySet entitySet, Object entity, String argumentName)
       at System.Data.Objects.ObjectContext.AddObject(String entitySetName, Object entity)
       at DisconnectedDatabase.DataEntities.AddToCities(Cities cities) ......." etc.

Thanks for your help,
Jules

Answer 4

Based on what you have shown, I think the issue is that you are specifying the wrong entityset name.  The code is supposed to create a key for the Country that you are relating to the city, so I'm assuming the entityset name would be "DataEntities.Countries" or something like that.

 

- Danny

Answer 5

Hi Danny,

Thanks for responding so quickly, it's wonderful to be able to talk to people actually working on the team about this stuff.  However, I was hoping you would also comment on some of my other comments; such as the frailty of not being able to use something better like a property on the entity, instead of a manually typed string which you can't do any compile time checking on?  Maybe there already is a way I don't know about?  If not, what do you think is the likelihood of getting something like that into a future version of the framework?  :-)

Anyway, you were right about the qualifiedEntitySetName, changing that to DataEntities.Country instead of DataEntities.Cities and the column name to the ID of the Country table allowed me to insert about 5 records, and then it died on me again.  Also, for anyone else reading the code from the previous thread, I wrote "cachedCity.ID = cachedCity.ID;", but it's suppose to be "cachedCity.ID = serverCity.ID;", just a copy paste error.

So like I was saying, it threw an "UpdateException", which referred me to the inner exception "System.Data.SqlServerCe.SqlCeException".  "A foreign key value cannot be inserted because a corresponding primary key value does not exist. [ Foreign key constraint name = CitiesAndCountriesRelationship ]"

So, I looked at the progression of data going into the SqlServerCe database and I noticed it failed when a city was inserted from a country that was already represented by another city from the same country.  Like this:
Country: Japan, City: Kawahigashi
Country: Italy, City: Fontechiari
Country: France, City: Saint-genis-d'hiersac
Country: Spain, City: Benijófar
Country: Netherlands, City: Elzen
Country: France, City: Arradon

The database is empty before I run this procedure; there are no countries or cities inserted yet (I've double checked).
However, then I changed the order in which the cities were inserted, so I could maybe understand the problem better...
On the first attempt to insert the first city, I get the same error as above.  (The city that was trying to be inserted was none of the previous cities listed.)

So, then I tried inserting all the Countries first, and then inserting all the cities... that seems to have worked :-)

But it's worrying me that I may run into a situation where I need to set the foreign key ID on an entity where the key on the primary table does not exist (yet).  I suppose you could imagine a circular dependency where table A links to table B and Table B links to Table C and Table C links to Table A.  Then this sorta workaround that I've done so far, wouldn't work.

Help?

Thanks,
Jules

Answer 6

In general the strategy of the entity framework is to have you work with entities and relationships rather than foreign key values (including EntityKey objects).  That said, there are definitely some cases where you need to interact with the entity keys as listed above.  Thre result is that the EF doesn't support any mechanism for directly exposing foreign key properties, but other folks have added properties to the partial classes to simplify setting values.  Another thing you could consider doing would be to add strongly typed entitykey factory methods to the context or something like that.  One approach would be to use T4 which can be a very convenient template-based way to generate code.

 

As to the exceptions you've been fighting around ordering of operations of the database, normally the expectation is that the entity framework would automatically do the ordering correctly for you regardless of the order of operations you make against the context.  When it is unable to get the ordering right, usually that means that there are foreign keys which logically exist in the model but aren't actually in the database as constraints.  When that happens, the system can't figure out the correct ordering.

 

Does that make sense?

 

- Danny

 

Answer 7

Hi Daniel,

Thanks so much for your previous help.

However, I still find my self railing up against some issues.  I hope the topic of this question is close enough to the original topic, if not I apologize.

Basically, I'd like to know what the general strategy is for disconnected and/or serialized entities.  I was thinking about asking this question in an abstract way, but I think it'd be clearer if I just gave my example.

I have a web server, which connects to a database which I have modeled/mapped using the Entity Framework.  I expose certain methods which use the EF modeled types I have as parameters or return types through a WCF service, which therefore brings the basic model to the caller of the WCF service.  (I hope I haven't mixed up my terminology too much this far.)

Some of the EF objects I have are like "Members".  The members object has a lot of basic properties, but a few of the properties are relationships to other tables.  So, this is where my problem starts.  If I want to create a new Members object so that I can send it back to the server (through the WCF service) to be inserted as a new member, all the relationship properties (such as MemberCountry and it's corresponding MemberCountryReference) are null.  Which means the little trick/work around you suggested to me before (where you showed me how to assign the EntityKey as a means to set the foreign ID value without querying the database again) doesn't work, because I can't call properties/methods on a null property, obviously.  Am I expected to create an entirely new MemberCountryReference object (for example)?

So, instead I have created a method ("AddNewMember") in the WCF service, which takes a "Members" object and several Guids, representing IDs of the relationships on the members object I'm unable to represent in the Members object (I use Guids as my ID value types).  I then use those Guids to query for the relationships I need to represent on my object, to piece it together again, before I do the insert - because some of these relationships/keys are not allowed to be null in the database. This seems to work ok... but it feels a bit messy and makes me feel like I'm doing something wrong or stupid, not to mention makes standardizing or abstracting my classes a bit more difficult which depressed me enough to write to you again for help.

So, I apologize if I've just being dumb here... I have used a couple other O/R libraries before and I've always been able to make it through these kinds of problems with out it getting too messy.  I just assume, I must be missing something here.

Any help or insight you can give me would be great! :)

Thanks again,
Jules

Answer 8

Hi! Daniel ! I was looking for this solution thanks for posting
It would be better if you change the entity set name to  "MyDbEntities.RoadType"


Thanks and Regards
Murtaza Tahir Ali

---

Thanks And Regards Murtaza Tahir Ali

Answer 9

Hi,

I am looking for architecture like as follows:

Database --> DataSet --> DataContext(Linq)/ ORM Entity --> GUI Application

I want to fetch data from database and keep into DataSet so if database id disconnect my application doesn't affect.

But DataSet is not supporting Object-Relational-Mapping (ORM) Model. I am interested in ORM because I want to take the advantages of LINQ.

and finally on My GUI i want to access Entity Classes.

Please Explain this by taking example like

1. College (Database Table)
     College_id   (Primary Key)
     College_name

2. Student (Database Table)
     Student_id  (Primary Key)
     Student_name
     College_id  (Foreign Key)
     Student_mark

Design a GUI in One Drop Down List which contains College List and on College Selection it displays Student List (Student Id,name,mark) in GridView.

Thanks

Answer 10

Hi Jules,

Did you ever get an answer to your question about passing Id along with member entity ?

Re

Answer 11

Danny,

User is a table name, mydb.User if so, Why I get error: The type or namespace name 'User' could not be found (are you missing a using..)

please give me help.

Thanks

Dex98