I'm considering the use of MVC & Entity Framework (Studio 11 Beta) for a project. I like it (a lot), and most of the documentation here is excellent, but still I'm having trouble finding good documentation about simple MVC & EF relations. Only after having
searched and tested for a long time I tend to stumble on the right information. For example, one is that EF assumes that the columns "ID" or "[Tablename]ID" is the primary key to a table. Also that table Navigation entries ONLY get created if related tables
have the same "[Tablename]ID" column(s).
One simple thing I'm having trouble whith is this one:
Parent Table
CountryID
ZipID
ZipName
ChildTable (needs CountryID & ZipID to exist in parent table)
CountryID
ZipID
ContactID
ContactName
I must be missing something because here I encounter a problem. The "-ID" columns in the parent table are auto-generated and hidden in the (controller)view. So are the "-ID" columns in the child controller view. How on earth can I create a related child
table entry - and at the same time utilize the automatic "-ID" column behaviour between MVC & EF?!
I realize that my subject may be a bit off, but the ID's seem to dictate how MVC/EF Navigation works, and I find the "ID"-use a bit terse. Not as well documented as most other parts of MVC/EF.
I guess there isn't a straighforward way to create a "Navigation" relationship between two tables - other than using ID columns?
And what if I just want to use the "ID" columns for meaningful input and data in a view, would it be possible to have the ID columns enabled and visible?
I realize that my subject may be a bit off, but the ID's seem to dictate how MVC/EF Navigation works, and I find the "ID"-use a bit terse. Not as well documented as most other parts of MVC/EF.
I guess there isn't a straighforward way to create a "Navigation" relationship between two tables - other than using ID columns?
And what if I just want to use the "ID" columns for meaningful input and data in a view, would it be possible to have the ID columns enabled and visible?
Regards,
Markus
I guess I'm confused at what you want...
The IDs are needed due to the fact it's a relational database. You don't need tge IDs in the models -- as long as one type contains the other you can load a parent object will all its children from the database.
So then if you want to map those to a user interface? Again, it's up to you from your object model how you want those displayed. You're not limited to only one "table" at a time.
Sound complex maybe, but I believe my needs are simple. I just don't get this ID concept - sounds like a dilemma. I'll give this one more try though, I'd appreciate your comments/suggestions. This is what I would like to accomplish:
- Create my (3rd normal form) relational database
- Use meaningful "ID" columns as primary keys to tables and for relationships
- Have MVC/EF add new controllers and views from the database to create, edit and delete tables - via ID columns
- Use generated Navigation properties from ID's to draw data from multiple tables and display in a single view
Ideally I would create tables/controllers/views like the following for example:
Continent table
ContinentID
Country table (ContinentID relationship to parent Continent table)
ContinentID (Create View: DropDownBox populated with ContinentID table values)
CountryID
Zip table (ContinentID & CountryID relationship to parent Country table)
ContinentID (Create View: DropDownBox populated with ContinentID table values)
CountryID (Create View: DropDownBox populated with CountryID table values matching selected ContinentID)
ZipID
I would use the ID columns for meaningful data entry and display, not identifiers. I would like to create a controller/view for the Zip table that has all the create, edit and delete functions.
But if I use ID columns, they get hidden from all views!
And if I dont use ID columns the Navigation properties to join multiple tables in a single view dont get generated.
"Meaningful" is when the primary index is composed of real values like names, ZIP codes, registration numbers etc., be it strings or numbers.
I'm not really taken by the idea of using a single "ID" column as a primary key to all tables. This usually needs to be an automatically incremented identifier - that can make moving data (import/export) a nightmare. But ID columns seem to be the only option
available for the model to be generated correctly by VS.
This really is not an issue any longer, I have resigned to the idea of using IDs :-)
Take a look at the following example, which demonstrates how to use Data Annotations provided by the Entity Framework. Most conventions (such as using ID as the key) are simply conventions to speed up routine tasks. Most of these conventions can be modified
through some manual changes.
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace Continents.Models
{
public class Continent
{
[Key]
[Required]
[MaxLength(255)]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[Display(Name="Continent")]
[Column("Name")]
public string ID { get; set; }
public virtual ICollection<Country> Countries { get; set; }
}
}
[Key] - This explicitly sets the property as the primary key. This is not required if you follow the naming conventions of classname.classnameID or classname.ID
[Required] - This is just a validation annotation. Your scaffolded forms will be validated both client-side and server-side based on validation annotations.
[MaxLength(255)] - This is another validation annotation.
[DatabaseGenerated(DatabaseGeneratedOption.None)] - This specifies that you do not want to auto-increment the field on insert.
[Display(Name="Continent")] - This tells the framework what you would like to display to the user for this fieldname when scaffolding a form. IE: @Html.DisplayNameFor(model => model.ContinentID) would display "Continent"
[Column("Name")] - This specifies what to name the database field. If this annotation is omitted, the Entity Framework will just use the property name as the field name
Marked as answer by markussveinn on Dec 21, 2012 10:59 PM
Thank you, mgasparel, for your input. I appreciate it.
I had actually been through those (excellent) examples you refer to, but I guess I was hoping for a more intelligent "model from database" genarator. One that could create the model based on relationships between tables, rather than simply by IDs.
markussveinn
Member
3 Points
12 Posts
Navigation - User Defined?
Apr 01, 2012 11:35 PM|LINK
Hi.
I'm considering the use of MVC & Entity Framework (Studio 11 Beta) for a project. I like it (a lot), and most of the documentation here is excellent, but still I'm having trouble finding good documentation about simple MVC & EF relations. Only after having searched and tested for a long time I tend to stumble on the right information. For example, one is that EF assumes that the columns "ID" or "[Tablename]ID" is the primary key to a table. Also that table Navigation entries ONLY get created if related tables have the same "[Tablename]ID" column(s).
One simple thing I'm having trouble whith is this one:
Parent Table
CountryID
ZipID
ZipName
ChildTable (needs CountryID & ZipID to exist in parent table)
CountryID
ZipID
ContactID
ContactName
I must be missing something because here I encounter a problem. The "-ID" columns in the parent table are auto-generated and hidden in the (controller)view. So are the "-ID" columns in the child controller view. How on earth can I create a related child table entry - and at the same time utilize the automatic "-ID" column behaviour between MVC & EF?!
Thank you,
Markus Sveinn Markusson
BrockAllen
All-Star
28084 Points
4997 Posts
MVP
Re: Navigation - User Defined?
Apr 01, 2012 11:43 PM|LINK
Either you can add them together at the same time:
var parent = new Parent();
parent.Children.Add(new Child());
db.Parents.Add(parent);
db.SaveChanges();
Or one at a time. The trick withi this is the ID gets populated into the entity after saving:
var parent = new Parent();
db.Parents.Add(parent);
db.SaveChanges();
var parentID = parent.ID;
var child = new Child();
child.ParentID = parentID;
db.Children.Add(child);
db.SaveChanges();
All pseudo-code :)
DevelopMentor | http://www.develop.com
thinktecture | http://www.thinktecture.com/
markussveinn
Member
3 Points
12 Posts
Re: Navigation - User Defined?
Apr 02, 2012 08:52 PM|LINK
Thank you very much, BrockAllen.
I realize that my subject may be a bit off, but the ID's seem to dictate how MVC/EF Navigation works, and I find the "ID"-use a bit terse. Not as well documented as most other parts of MVC/EF.
I guess there isn't a straighforward way to create a "Navigation" relationship between two tables - other than using ID columns?
And what if I just want to use the "ID" columns for meaningful input and data in a view, would it be possible to have the ID columns enabled and visible?
Regards,
Markus
BrockAllen
All-Star
28084 Points
4997 Posts
MVP
Re: Navigation - User Defined?
Apr 02, 2012 10:51 PM|LINK
I guess I'm confused at what you want...
The IDs are needed due to the fact it's a relational database. You don't need tge IDs in the models -- as long as one type contains the other you can load a parent object will all its children from the database.
So then if you want to map those to a user interface? Again, it's up to you from your object model how you want those displayed. You're not limited to only one "table" at a time.
DevelopMentor | http://www.develop.com
thinktecture | http://www.thinktecture.com/
markussveinn
Member
3 Points
12 Posts
Re: Navigation - User Defined?
Apr 04, 2012 12:05 AM|LINK
Thank you, BrockAllen.
Sound complex maybe, but I believe my needs are simple. I just don't get this ID concept - sounds like a dilemma. I'll give this one more try though, I'd appreciate your comments/suggestions. This is what I would like to accomplish:
- Create my (3rd normal form) relational database
- Use meaningful "ID" columns as primary keys to tables and for relationships
- Have MVC/EF add new controllers and views from the database to create, edit and delete tables - via ID columns
- Use generated Navigation properties from ID's to draw data from multiple tables and display in a single view
Ideally I would create tables/controllers/views like the following for example:
Continent table
ContinentID
Country table (ContinentID relationship to parent Continent table)
ContinentID (Create View: DropDownBox populated with ContinentID table values)
CountryID
Zip table (ContinentID & CountryID relationship to parent Country table)
ContinentID (Create View: DropDownBox populated with ContinentID table values)
CountryID (Create View: DropDownBox populated with CountryID table values matching selected ContinentID)
ZipID
I would use the ID columns for meaningful data entry and display, not identifiers. I would like to create a controller/view for the Zip table that has all the create, edit and delete functions.
But if I use ID columns, they get hidden from all views!
And if I dont use ID columns the Navigation properties to join multiple tables in a single view dont get generated.
Regards,
Markus
mgasparel
Member
298 Points
65 Posts
Re: Navigation - User Defined?
Dec 21, 2012 09:17 PM|LINK
When you say:
"Use meaningful "ID" columns as primary keys to tables and for relationships"
Do you mean that you want to use a string as the value for the ID, or that you want to rename the fields to something other than 'ID'?
markussveinn
Member
3 Points
12 Posts
Re: Navigation - User Defined?
Dec 21, 2012 09:37 PM|LINK
"Meaningful" is when the primary index is composed of real values like names, ZIP codes, registration numbers etc., be it strings or numbers.
I'm not really taken by the idea of using a single "ID" column as a primary key to all tables. This usually needs to be an automatically incremented identifier - that can make moving data (import/export) a nightmare. But ID columns seem to be the only option available for the model to be generated correctly by VS.
This really is not an issue any longer, I have resigned to the idea of using IDs :-)
Regards,
Markus
mgasparel
Member
298 Points
65 Posts
Re: Navigation - User Defined?
Dec 21, 2012 10:16 PM|LINK
Just for future reference, you can accomplish what you are asking...
I would recommend you take a look at this tutorial. I'm new to .NET, but this got me up and running pretty quickly.
http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/creating-a-more-complex-data-model-for-an-asp-net-mvc-application
Take a look at the following example, which demonstrates how to use Data Annotations provided by the Entity Framework. Most conventions (such as using ID as the key) are simply conventions to speed up routine tasks. Most of these conventions can be modified through some manual changes.
using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; namespace Continents.Models { public class Continent { [Key] [Required] [MaxLength(255)] [DatabaseGenerated(DatabaseGeneratedOption.None)] [Display(Name="Continent")] [Column("Name")] public string ID { get; set; } public virtual ICollection<Country> Countries { get; set; } } }[Key] - This explicitly sets the property as the primary key. This is not required if you follow the naming conventions of classname.classnameID or classname.ID
[Required] - This is just a validation annotation. Your scaffolded forms will be validated both client-side and server-side based on validation annotations.
[MaxLength(255)] - This is another validation annotation.
[DatabaseGenerated(DatabaseGeneratedOption.None)] - This specifies that you do not want to auto-increment the field on insert.
[Display(Name="Continent")] - This tells the framework what you would like to display to the user for this fieldname when scaffolding a form. IE: @Html.DisplayNameFor(model => model.ContinentID) would display "Continent"
[Column("Name")] - This specifies what to name the database field. If this annotation is omitted, the Entity Framework will just use the property name as the field name
markussveinn
Member
3 Points
12 Posts
Re: Navigation - User Defined?
Dec 21, 2012 10:58 PM|LINK
Thank you, mgasparel, for your input. I appreciate it.
I had actually been through those (excellent) examples you refer to, but I guess I was hoping for a more intelligent "model from database" genarator. One that could create the model based on relationships between tables, rather than simply by IDs.
Regards,
Markus