Entity Framework 6 Recipes Translation Series (4)-----Chapter II the foundation of Entity Data modeling from existing database creation model

I do not know that there is not much interest in EF, or I translate a problem (if so, I implore you to correct), through the previous few feedback, reading this series of people are not many. Do not this matter to finally become to eat not to please the matter on the trouble, nonsense on to here, straight to the subject.

2-2 creating a model from an existing database

　　Problem

There is an existing database that has tables, perhaps views, and foreign keys. You want to create a model from it.

　　Solution Solutions

Let's imagine that you have a database describing poets (poet) and their poems (Poem), and their relationships. As shown in 2-7.

　　

Figure 2-7 A simple database of poets and their poems

As can be seen, a poet may be the author of one or more poems, each poem can be classified according to its rhythm, rhyme is the basic pattern of verse. The view that joins tables together in the database is not displayed, which makes it easier to enumerate poets, poems, and rhymes.

Follow these steps to import tables, views, and relationships into a model:

1. Right click on your project, select Add (add)? New Item.

2. Select the ADO data model under the data template under the Visual C # Entry (ADO).

3. Select generate from database to create a model from an existing one, click Next.

4, you can choose an existing database connection, you can also choose to create a new database connection, if you choose New, you will choose the database server, authentication method (Windwos or SQL server) and a database. Once you've chosen it, you can click Test Connection (test connection) to see if the connection is available. After the test is finished, click Next.

The dialog box that pops up shows all the tables, views, and stored procedures for the database. Select the items that you want to include in the model. We select all the tables (Meter,poem,poet), the View (vwlibrary), and then tick on the "plural" form that determines the name of the generated object, including the foreign key column in the Model check box. We will discuss this further. Figure 2-8 shows the choices we make.

Figure 2-8 Selecting a table, a view containing a model, checking the single and plural form that determines the name of the generated object, including the foreign key column in the Model check box

When you click Finish, the wizard generates a model with three tables and a view. The wizard reads the FOREIGN KEY constraint from the database and deduces a one-to-many relationship between poet and poem (s), as well as a a-to-many relationship between meter and poem (s).

Figure 2-9 Conceptual model

Figure 2-9 shows a model that contains table poet,poem and meter, view vwlibrary.

Now you have a model that you can use in your code. Note that the Vwlibrary entity is vwlibrary based on the view in the data. In the vast majority of libraries, views are read-only, and insertions, deletions, and updates are not supported. The same is true in the Entity Framework, where the Entity Framework treats views as read-only. You can map stored procedures to resolve create, update, and delete actions for view-based entities. We will demonstrate this in the sixth chapter.

　　Principle

　　Let's take a look at the model that the Import Wizard created for us. Note that scalar attributes and navigation properties are already included in the entity. Scalar properties are mapped to columns in a database table, but the navigation properties come to the inter-table relationship in the database.

In a database diagram, a poem has a meter and a poet (author). This conforms to the navigation properties in meter and poet. If I have an instance of a poem entity, the navigation property poet will reference an instance of a poet entity, and the navigation property meter will reference an instance of the meter entity.

A poet may be the author of more than one poem, and its navigation property poems contains a collection of instances of a poem entity. This collection may be empty, which means that the poet has not yet created any poem. For meter entities, the navigation property poems is also a collection. The Navigation property contains a collection of poem entity instances that belong to the specified meter. SQL Server does not support creating relationships on views, which are reflected in the model as a vwlibrary entity without a directed property.

The Import wizard has a very clever representation of the name of the navigation property that contains the collection in the plural form. When you right-click on the entity to view his properties, you will see that the entity set name for each entity is also in the plural form. For example, the entity set name for the poem entity is poems. This automatically adds complex numbers to the benefit, tick the check box on the single plural form that determines the name of the generated object.

Tick the Include Foreign key column check box option in the model so that the foreign key is also included in the model. Although it seems that there is no need to have a foreign key attribute while owning a navigation property. In this context, we will demonstrate the benefits of having a direct access to foreign key properties in the following techniques.

Listing 2-2 shows how to create an instance of Poet,poem and meter entities in the model and save them to the data. It also demonstrates how to get poets and poems from the database by querying the model.

code Listing 2-2. Inserting and querying in a model

 1 using (var context = new Ef6recipescontext ()) {2 var poet = new Poet {FirstName = "John", LastName = "Milton"}; 3 var poem = new Poem {Title = "Paradise Lost"}; 4 var meter = new Meter {metername = "iambic pentameter"}; 5 poem. Meter = Meter; 6 poem. poet = poet; 7 context. Poems.add (poem); 8 poem = new Poem {Title = "Paradise regained"}; 9 poem. Meter = meter;10 Poem. Poet = poet;11 context.  Poems.add (poem); poet = new Poet {FirstName = "Lewis", LastName = "Carroll"};13 poem =  New Poem {Title = "The Hunting of the Shark"};14 meter = new Meter {metername = "anapestic tetrameter" };15 poem. Meter = meter;16 Poem. Poet = poet;17 context. Poems.add (poem): poet = new Poet {FirstName = "Lord", LastName = "Byron "};19 poem = new Poem {Title =" Don Juan "};20 poem. Meter = meter;21 Poem. Poet = poet;22 context. Poems.add (poem);  SaveChanges ();}25 using (var context = new Ef6recipescontext ()) {$ var poets = Context. Poets;27 foreach (var poet in poets) {Console.WriteLine ("{0} {1}", poet. FirstName, poet. LastName); foreach (Var poem in poet. Poems) {Console.WriteLine ("\t{0} ({1})", poem. Title, poem.             Meter.metername); 31}32}33}34 35//Use view vwLibrary36                 using (var context = new Ef6recipescontext ()) {PNS var items = context.vwlibraries;38 foreach (var item in items) {Console.WriteLine ("{0} {1}", item. FirstName, item. LastName); Console.writEline ("\t{0} ({1})", item. Title, item. Metername); 41}42}

In code listing 2-2, the first block of code, we created an instance of the Poet,poem and meter entity types, the poet John Milton, his poem "Paradise Lost" and the Rhythm of the poem, which belonged to the five-step Caron-iambic Pentameter (a rhyme of poetry). Once we create an instance of the poem entity type Peom and set the meter property of the poem to point to the meter instance, the poet attribute points to the poet instance. Using the same method, we create other entities and relationships. Once created, we can call the SaveChanges () method to generate and execute the appropriate SQL statement to insert data into the underlying database.

The output of code listing 2-2 is sent:

Lord Byron
Don Juan (anapestic tetrameter)
Lewis Carroll
The hunting of the Shark (anapestic tetrameter)
John Milton
Paradise regained (iambic pentameter)
Paradise Lost (iambic pentameter)
Lewis Carroll
The hunting of the Shark (anapestic tetrameter)
Lord Byron
Don Juan (anapestic tetrameter)
John Milton
Paradise regained (iambic pentameter)
John Milton
Paradise Lost (iambic pentameter)

In the code, we start by creating instance poet,poem and the prosodic meter of John Milton's first poem. Once we have created it, we can set the poem navigation property meter to meter instance, and the poet navigation property is the poet instance. All the settings for the instance poem are complete, and the Add () method is called to be added to the context. The next work will be done by the Entity Framework, including adding poem to the poet instance's navigation properties poems collection, adding poem to meter instances of the navigation properties poems collection. Use the same steps to complete the remaining tasks. To reduce the code, we reused the variables and instances.

Once all instances have been created and the navigation properties are initialized, we have completed the creation of an object graph. The Entity Framework keeps all modifications to the creation of the object graph, which is implemented in the context of the database. The variable context contains an instance of a database context (its type is DbContext), which is the object we use to track modifications made to the object graph, and calls its SaveChanges () method to send all modifications to the database.

Then you can query the model to verify the data that we saved in the database. We used a new context object and queried using LINQ to Entities. We could have reused the previous context object, but we knew that it had an object graph in memory, and that the next query would not be retrieved directly from memory through the database (this would not be possible for verification purposes).

Using LINQ to Entities, we queried all the poets (poets) and then printed out the names of each poet and the details of each poet's poem. The code is very simple, but it is implemented using two nested loops.

The last block of code uses the Vwlibrary entity, which is based on the Vwlibrary view. The Vwlibrary view joins tables together to flatten them to provide a clearer perspective. When we query each poet's information through the Vwlibraties entity set, only one loop is needed. The output is slightly different because we repeat the poet's name in each poem.

The last note in this example is that we did not insert poests,poems and meters through the view vwlibrary. Because in the vast majority of databases, views are read-only. In the Entity Framework, we also cannot be able to insert (or update, delete) entities from a view entity. Of course, we will show you how to overcome this difficulty in the later part of this book!

Entity Framework 6 Recipes Translation Series (4)-----Chapter II creation of a model from an existing database based on Entity Data Modeling (RPM)