Entity Framework Data Insert Question

Question

0

 

I am using Entity Framework 4 and have a question regarding relationships.  I will will ask my question through an example I am currently working on to help further my understanding of the Entity Framework. 

 

My conceptual model contains two entities: MP3 and Genre.  The MP3 entity contains all of the information about a particular mp3 derived from the metadata (title, artist, album, etc).  Each mp3 file also has an associated genre in the metadata.  Since the list valid genres is somewhat large with most of the values repeated multiple times, I've decided to create a separate entity (Genre) that will hold the string name of each genre.  I then use an association to tie an integer field (genre_ID) in the MP3 entity to the string name of the genre in the Genre entity (the genre_ID field in the MP3 entity will be the same number as the ID field in the Genre entity).  I believe this is called data normalization in the database world.

 

My question is as follows.  I can easily read all of the metadata for all of the mp3's in a particular folder into a datatable.  Now I want to add the data to a database via the Entity Framework.  In words, I need to create a new MP3 entry for each mp3 file and then check to see if the genre of the current mp3 is already in the list.  If it is, I need to obtain its ID from the Genre entity.  If not, I need to add a new entry to the Genre entity and obtain its ID.  I can then use this ID when I create my new MP3 entry (genre_ID).

 

I know when I needed to perform this similar operation using SQL, I had to use a series of JOINs and other SQL statements.  Is there any easier way for me to insert data using the Entity Framework?

 

Thank you for your help!

Answer 1

Hi oufan22,

Welcome to MSDN forums.

According to your description, I am not clear about the relationship between the MP3 and Genre table. but you said::the genre_ID field in the MP3 entity will be the same number as the ID field in the Genre entity my understanding is the genre_ID is a foriegn key. so The Genre is main table, The MP3 table must have a gener_ID when the new record was inserted. If I misunderstand you, please feel free to follow up.

Have a nice day.

---

Alan Chen[MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Alan,

I am trying to compress the amount of data that will be stored in the database by minimizing the storing of long, repetitive strings.  To do this, I created a separate table GENRE, that will have two columns: ID (an auto-generated number) and Genre (a text string).  Instead of storing the genre for each mp3 record in the MP3 table, I will store an integer (genre_ID) and then use that integer to point to an ID in the GENRE table to get the genre text string.

In SQL, this is very complicated (at least for me) to do and requires the use of multiple JOINs.  Is there an easy way to accomplish this behavior using Entity Framework 4?

I haven't really coded anything yet, I'm still trying to research the best way to accomplish this before I get too far.  If this wasn't explained well enough, let me know and I will be more thorough.

Thank you for your help,

Josh

Answer 3

Hello Josh,

 

Thanks for your reply.

According to your description, I think if I don't misunderstand you, a single genre_ID in MP3 table only have one genre text string in GENRE, and that genre_ID is a foriegn key. So you have to model this as two separate entities with a 1-1 relationship between them.  If you generated a model from the database, in the designer / wizard right click the entity MP3, choose "Add", and choose "Association..."

Then, in the "Add association" window, you can fill in the Association name, choose the target entity, and choose 1 to 1 or 1 to many, and navigation.

Then click "OK". I think the next you could done it by yourself.

 

I hope it can help,

Have a nice day,

 

---

Jackie Sun [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

I understand the relationship between the two tables.  What I don't understand is how the insertion of new data is handled by the entity framework.  For example, how do I create a new MP3 record that has a new Genre?  In my old code (prior to entity framework), I performed the following steps:

 

1. Check to see if the Genre of the new MP3 record exists in the GENRE table.
2. a) If it exists, return the ID.
3. b) If it doesn't exist, create a new Genre record and return its ID.
4. Save the MP3 record with the genre_ID returned.

In some of the articles I've read on entity framework, it almost seems as if I get some of this behavior for free in Entity Framework.  How does the creation of a new MP3 with a new Genre need to be handled in Entity Framework?

 

As an aside, inserting new data is one topic that I can't seem to find much information on.  I've purchased books (Programming Entity Framework by Julie Lerman) and scoured the net, but I can't seem to find what I'm looking to learn.  If anyone can point to some resources on this, I would be greatly appreciative.

 

Thanks again!

Answer 5

Hello oufan222,

 

Thanks for your feed back.

Here is an example for 1-1 relationship using Entity Framework.

How to insert a row using Entity Framework?

I hope it can help you. If you have any questions please feel free to let us know.

 

Have a nice day,

 

---

Jackie Sun [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 6

var employee = (from e in context.Employees
  where e.Id == empid
  select e)
  .DefaultIfEmpty(new Employee()
  {Id = Convert.ToInt32(dr.Field<string>("EMPL_ID")),
  LastName = dr.Field<string>("LAST_NAME"),
  FirstName = dr.Field<string>("FIRST_NAME"),
  MiddleName = dr.Field<string>("MIDDLE_NAME"),
  PreferredName = dr.Field<string>("PREFERRED_NAME"),
  UserId = dr.Field<string>("USER_ID")
  }
  )
  .First();

context.Employees.AddObject(employee);

I am trying to be smart about how I insert new employees.  I thought I could use the DefaultIfEmpty() method to return a new employee if the query does not return any.  I am having two problems

1) If I leave the .First() method in the code, I get the following run-time error:

{"Unable to create a constant value of type 'BusinessDataDB.Employee'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."}

2) If I remove the .First(), the code executes, but the employee variable is null.

Thanks in advance for the help!

Answer 7

Hello oufan222,

 

Thanks for your question.

If you have any time could you please post a new thread for this question? So that I can follow it up in time until your question has been solved.

Another, I will appreciate if you mark some useful replies as answer, because the thread has been lasted for a long time.

Thank you for your understanding and support.

 

Have a nice day,

---

Jackie Sun [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 8

Sorry for not marking the replies useful and a new thread has been created.