Tag Archives: entity framework

LINQ to Entities does not recognize the method ‘System.DateTime AddDays(Double)’ method, and this method cannot be translated into a store expression.

LINQ to Entities does not recognize the method ‘System.DateTime AddDays(Double)’ method, and this method cannot be translated into a store expression.

Some of the operations you can perform in code don’t translate (at least not cleanly) to SQL, you’ll need to move around certain operations so they translate better.

//Figure out the day you want in advance
var sevenDaysAgo = DateTime.Now.Date.AddDays(-7);
var results = users.Select(user => new {
     user.Key,
     user.Value,
     Count = users.Join(context.Attendances, 
                        user => user.Key,
                        attendance => attendance.EmployeeId,
                        (user, attendance) => attendance.LoginDate)
                  .Where(date => date > sevenDaysAgo)
                  .Select(date => date.Day)
                  .Distinct()
                  .Count()
});

foreach (var result in results)
{        
    _attendanceTable.Rows.Add(result.Key, result.Value, result.Count);
}

Do link tables need a meaningless primary key field?

I have found that many people set up the primary keys for join tables in a manner that I find undesirable. This leaves open the possibility of some sneaky bugs in their programs.

I’ll start by covering what I mean by a join table (as opposed to what I will call a data table).

Most basic data is stored in data tables. For example a “users” table is a data table, as is a “groups” table. Each row in a “users” table represents one user, as each row in a “groups” table would represent a group.

It could be, however, that each user could be in more than one group and each group could have more than one user as a member. In order to keep the database normalized, another table should represent these relationships (“users2groups”).

I wouldn’t classify this as a data table because each row in this table represents a relationship between two things, rather than a thing itself. So, the “users2groups” table is a join table.

I have frequently seen a structure like this for join tables:

users2groups:
user2group_id (primary key)
user_id
group_id

The thinking here, I imagine, is that we know that each table needs a primary key so we add one to the table. Herein lies the problem. The value of “user2group_id” represents a relationship, not data.

You won’t ever look up a row using the “user2group_id” because the row has no value in isolation. Instead, you will look up rows by the user_id or the group_id.

The real problem, however, is that this allows duplication. See this sample data:

image

Using a user2group_id primary key allows this duplication, which has no meaning.

At first this may seem like the same problem as two rows of matching data in a data table, but it isn’t. First of all, a duplicate user row means something – that two users have identical data. More than that, it is relatively easy to detect.

A duplicate row in a join table has no meaning, but it can cause problems that are difficult to debug.

The solution to this problem is simple, drop the extraneous “user2group_id” and make your primary key a compound primary key consisting of both “user_id” and “group_id”.

users2groups:
user_id (primary key)
group_id (primary key)

This will ensure that you cannot have one relationship represented by more than one row.

You should even be able to make this change without causing any problems for your existing application. Unless, of course, you already have a relationship duplicated in the table. 

Next time you make a join table, I suggest using a compound primary key. The structure is simpler and it could help to eliminate some bugs.

MVC 5, Entity Framework 6 and Many to Many Relationship : a step by step View Model approach

Introduction

There are a lot of articles about ASP.NET MVC and Model Binder. I didn’t find any real-world article about Model Binder of Many To Many relation. So I decided to write this post and share my experience on this topic. Although I will explain easily and step by step my approach, readers of this article should have as prerequisite the knowledge of basic concepts in ASP.NET MVC as Controller, View, Model, Model Binder and Scaffolding.

Background

In this post I will use ASP.NET MVC 5, Entity Framework 6.1.1 and Visual Studio 2012. To add MVC 5 support in VS2012 you have to check this link : ASP.NET and Web Tools 2013.1 for Visual Studio 2012. You can add Entity Framework 6.1.1 through NuGet. My approach should be not the best, but it works well.

Many to Many Entity Model generation

My sample comes from a real-world application, a Web Job Portal for Employers and Job Seekers. In my sample I will use Database first approach. With Entity Framework we can start our project also with First Model Approach designing the Model of our entities and after generating the database schema using Entity Framework tool. I will not add more because this is not the topic of the post. Let’s carry on. We have the following database diagram

JobPost contains the job announce posted from Employer. Moreover the Employer can add to a JobPostmany JobTag. In the database those two tables are related to each other via a link or junction table, JobPost_JobTag without payload. This table just contains the foreign keys used to link the two tables together into a many-to-many relationship. We create a new ASP.NET MVC5 web project named ManyToManyMVC5

Now we have to add Entity Framework 6.1.1 from NuGet. We are ready to create a model and import these tables and relationship in onr project.

Add a new model to the project by right-clicking your Models folder and selecting Add>New>ADO.NET Entity Data Model.

  1. Specify as name for item “JobPortal”
  2. Select the option Generate from Database
  3. Use the wizard to create your database connection or connect to an existing one. Let “JobPortalEntities” as the name for the connection string of the Web.Config file ( the option on the buttom )
  4. Select the 3 tables to add to the model : Employer, JobPortal, JobTag and JobPortal_JobTag. Check the option “Pluralize or Singularize generated object names” and let other settings as default.

Now you see the Entity Diagram of the generated model

Note that we have the one-to-many relationship between Employer and JobPost, and the many-to-many relationship between JobPost and JobTag. Note that the link table JobPost_JobTag is not represented as an entity in our mode. This is because our link table it has no payload ( that is it has no scalar property ). If we had a payload, we shouldn’t have the many to many relationship but we should have a fourth entity with one-to-many relationship to JobPost and one to JobTag. Let’s carry on. In the Models folder we have JobPortal.edmx file, that is the phisical representation for our model. We can expand it and expand JobPortal.tt. We see that the last contains our .cs files for the entitites we generated. Take a look to JobPost.cs and JobTag.cs

Collapse | Copy Code

    public partial class JobPost
    {
        public JobPost()
        {
            this.JobTags = new HashSet<JobTag>();
        }
    
        public int Id { get; set; }
        public string Title { get; set; }
        public int EmployerID { get; set; }
    
        public virtual Employer Employer { get; set; }
        public virtual ICollection<JobTag> JobTags { get; set; }
    }
    public partial class JobTag
    {
        public JobTag()
        {
            this.JobPosts = new HashSet<JobPost>();
        }
    
        public int Id { get; set; }
        public string Tag { get; set; }
    
        public virtual ICollection<JobPost> JobPosts { get; set; }
    } 

Note that the two entities have a ICollection property to each other.

ASP.NET MVC5 and Many to Many relationship

Surfing internet you will find many interesting articles and posts on Model View Controller ASP.NET pattern and how it is easy to create MVC projects. But in the real-world the things are different and we have to enhance our approach.

In ASP.NET MVC we can use Scaffolding to quickly generating a basic outline of our application that we can edit and customize. So we can create automatically controllers and strong-typed view to perform basic CRUDL operations on our entities. Unfortunately ASP.NET MVC scaffolding doesn’t handle many to many relationship. I think that the main reason is that there are too many kinds of many-to-many user interface creating/editing.

Another issue is concerning the automatic ASP.NET MVC Model Binding. As we know, model binding allows you to map HTTP request data with a model. Model binding makes it easy for you to work with form data because the request data (POST/GET) is automatically transferred into a data model you specify. ASP.NET MVC accomplishes this behind the scenes with the help of Default Binder. If we have a Many to Many relationship in the user interface we will have some kind of interface that allow user to perform multiple choice. So we need a complex type in our view to bind the selected items, as a Check Box Group or List, or a Multiple List Box. But let see the issues in action .

We will use Scaffolding to generate a basic controller with Entity Framework based CRUDL method and the related Views. Right click on Controlers foder and choise Add>Controller. In the Add Scaffold choise “MVC5 Controller with views, using Entity Framework”. Set the next form as the below picture and click on Add

Note: If you get an error message that says “There was an error getting the type…”, make sure that you built the Visual Studio project after you added the class. The scaffolding uses reflection to find the class.

MVC5 Scaffolding will generate in the Controllers folder a file JobPostController.cs and in the Views folder in the JobPost subfolder the views for the related CRUDL methods. Before run the application we have to modify the RouteConfig.cs located in App_Start setting as default controller JobPostController

Collapse | Copy Code

public class RouteConfig
{
    public static void RegisterRoutes(RouteCollection routes)
    {
        routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
 
        routes.MapRoute(
            name: "Default",
            url: "{controller}/{action}/{id}",
            defaults: new { controller = "JobPost", action = "Index", id = UrlParameter.Optional }
        );
    }
}

Now you can run the application and browse the pages. It is amazing …!! But .. we don’t have any track about Many to Many relationship !!!

View Model Approach

To overcome these issues, my approach is based on ViewModel entity. View Model represents data you want to have displayed in your view. Easily is a class that represents data model used in a specific view. In our case our View Model contains the data of the JobPost, the data of all available JobTag and the data of the selected JobTag.

We start to modify the View Index ( it is associated to the Action index of the JobPostController ) and let it to show the list of the associated JobTag to a JobPost in a Listbox. So we add a row to the table in the index.cshtml.

Collapse | Copy Code

// This row to the header table
 <th>
    @Html.DisplayNameFor(model => model.JobTags)
 </th>
// This row to the body table
 <td>
    @Html.ListBox("Id", new SelectList(item.JobTags,"Id","Tag"))
 </td>

Now we can run the apps … in the index you will see a ListBox with all Tags for the post. It was easy.

Next step is to modify the edit view. When user edit a job post, we desire to show a ListBox with the list of available the job tags where the selected are the ones associated to the job post. User can change the selection or other data and submit back to the controller to persist the data,

The first problem is to pass to the view the list of all available job tags. The second problem is to mark as selected the associated ones. Now comes in the Model View !!

In your solution create a folder named ViewModel and add to it a class file “JobViewMode.cs” with the code below

Collapse | Copy Code

public class JobPostViewModel
{
    public JobPost JobPost { get; set; }
    public IEnumerable<SelectListItem> AllJobTags { get; set; }
 
    private List<int> _selectedJobTags;
    public List<int> SelectedJobTags
    {
        get
        {
           if (_selectedJobTags == null)
           {
              _selectedJobTags = JobPost.JobTags.Select(m => m.Id).ToList();
           }
           return _selectedJobTags;
        }
        set { _selectedJobTags = value; }
    }
}

Our view model contains a property that store a JobPost, a property that store the JobTag associated to the stored JobPost as a List<int> of JobTag’s Id, and finally a property that store all available JobTag as a IEnumerable fo SelectListItem ( to bind to a ListBox )

We modify the Action edit associated to the Get as below where we introduce the JobPostViewModel instead of JobPost

Collapse | Copy Code

 // GET: /JobPost/Edit/5
public ActionResult Edit(int? id)
{
     if (id == null)
     {
       return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
     }

     var jobPostViewModel = new JobPostViewModel            {
            JobPost = _db.JobPosts.Include(i => i.JobTags).First(i => i.Id == id),
         };

     if (jobPostViewModel.JobPost == null)
        return HttpNotFound();

     var allJobTagsList = _db.JobTags.ToList();       
     jobPostViewModel.AllJobTags = allJobTagsList.Select(o => new SelectListItem
     {
                Text = o.Tag,
                Value = o.Id.ToString()
     });

     ViewBag.EmployerID =
             new SelectList(db.Employers, "Id", "FullName", jobpostViewModel.JobPost.EmployerID);
  
     return View(jobpostViewModel);
} 

Note : As you modified because you have not already changed the view type model you will get an error from the retur View field. Ignore it. It will off after you will modify the related view

In the modified action method we use the JobPostView entity. We load the propertiy JobPost with the selected job post including the eager loading of JobTags entity, and the property AllJobTags with a ListIntem builded form the JobTags and return to the View the ViewModel instead of the Model. Now we can modify the View. We change the ModelBinder to ViewModelJobPost and all the property. We add the ListBox binding.

Collapse | Copy Code

@model ManyToManyMVC5.ViewModels.JobPostViewModel
@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>JobPost</h4>
        <hr />
        @Html.ValidationSummary(true)
        @Html.HiddenFor(model => model.JobPost.Id)

        <div class="form-group">
            @Html.LabelFor(model => model.JobPost.Title, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.JobPost.Title)
                @Html.ValidationMessageFor(model => model.JobPost.Title)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.JobPost.EmployerID, "EmployerID", new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DropDownListFor(m => m.JobPost.EmployerID,
                        (SelectList)ViewBag.EmployerID,
                        Model.JobPost.Employer.Id);
                @Html.ValidationMessageFor(model => model.JobPost.EmployerID)
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model=>model.AllJobTags,"JobTag",new {@class="control-label col-md-2"})
            <div class="col-md-10">
                @Html.ListBoxFor(m => m.SelectedJobTags, Model.AllJobTags)
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

<script src="~/Scripts/jquery-2.1.1.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

We can select more than one JobTag by multiselecting, as in the below

After saved, below what we can see in out Index page

Let take a look on the mechanism that it’s behind. From the Edit page, when we save a POST command is triggered as below

Note the the query string use the “JobPost.<FieldName>” and that we have multiple SelectedJobTag.

This is important to understand the mechanismo of the MVC autobinder. When we click on Save, the Action

           public ActionResult Edit(JobPostViewModel jobpostView)

from the controller JobStatus is called. MVC5, becuase its ModelBinder, will automatically map the value on the query string to the related property of the class JobPostViewModel injected in the Action. We could override this mechanism for a more complex ViewModel object using the [Bind] decorator with the Action. But this is could be a subject for a new article. I hope I gave you a first idea about what is behind the scene with binding in ASP.NET MVC.

Conclusion

ASP.NET MVC5 with its Scaffolding mechanism too often has huge limitation in real world application. In this article I tryed to explain you how to use ASP.NET MVC in real world with a simple example about the implementation of a Many to Many relationship.

The example is really a basic one, but I hope that you got the idea behind the Binder and the ViewModel, so you are ready to go alone to the next level starting to modify the “create” feature in the enclosed sample project.

People could think to use other mechanisms like PropertyBag/ViewBag instead of ViewModel for example to pass other data to the View. You will lose the AutoBind mechanism and  it also is definetly not correct from the point of view of S.O.L.I.D. principles, TDD approach and Design Object Oriented in general.

original article:http://www.codeproject.com/Articles/702890/MVC-Entity-Framework-and-Many-to-Many-Relation

Configure Many-to-Many relationship using Code First Approach

Configure Many-to-Many relationship using Code First Approach:

Here, we will see how to configure Many-to-Many relationship between the Student and Course entity classes. Student can join multiple courses and multiple students can join one course.

Visit Entity Relationship section to understand how EF manages one-to-one, one-to-many and many-to-many relationships between the entities.

Configure Many-to-Many relationship using DataAnnotation:

Student class should have a collection navigation property for Course, and Course should have a collection navigation property for student, which will create a Many-to-Many relationship between student and course as shown below:

     
    public class Student
    {
        public Student() { }

        public int StudentId { get; set; }
        [Required]
        public string StudentName { get; set; }

        public int StdandardId { get; set; }
        
        public virtual ICollection<Course> Courses { get; set; }
    }
        
    public class Course
    {
        public Course()
        {
            this.Students = new HashSet<Student>();
        }

        public int CourseId { get; set; }
        public string CourseName { get; set; }

        public virtual ICollection<Student> Students { get; set; }
    }
        

The code shown above will create the following database, where Code-First will create a third joining table, CourseStudent which will consist PK of both the tables ie. StudentId & CourseId:

one-to-one relationship in code first

Configure Many-to-Many relationship using Fluent API:

You can use the Fluent API to configure a Many-to-Many relationship between Student and Course as following:

   
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

       modelBuilder.Entity<Student>()
                   .HasMany<Course>(s => s.Courses)
                   .WithMany(c => c.Students)
                   .Map(cs =>
                            {
                                cs.MapLeftKey("StudentRefId");
                                cs.MapRightKey("CourseRefId");
                                cs.ToTable("StudentCourse");
                            });

    }
        

As you can see in the above example, .HasMany<Course>(s => s.Courses).WithMany(c => c.Students)says that Student and Course has many to many relationship with Students navigation property in Course class and Courses navigation property in Student class.

Map method takes Action type delegate, so we can pass lambda expression wherein we will specify FK property name of Student (we start with Student entity, so it will be left table) and FK of Course table. ToTable will create StudentCourse table.

This will create a new joining table StudentCourse with two Primary Keys which will also be Foreign Keys as shown below:

one-to-one relationship in code first

Entity Framework: “Store update, insert, or delete statement affected an unexpected number of rows (0).”

I ran into this and it was caused by the entity’s ID (key) field not being set. Thus when the context went to save the data, it could not find an ID = 0. Be sure to place a break point in your update statement and verify that the entity’s ID has been set.
This issue is caused by forgetting to include the hidden ID input in the .cshtml edit page.

One to one optional relationship using Entity Framework Code First

Here’s an example from the Programming E.F. Code First book

modelBuilder.Entity<PersonPhoto>()
.HasRequired(p => p.PhotoOf)
.WithOptional(p => p.Photo);
The PersonPhoto entity has a navigation property called PhotoOf (that points to a Person type). the Person type has a navigation property called Photo that points to the PersonPhoto type.

In the two related classes, you use each types KEY, not foreign keys. I.E. you won’t use the LoyaltyUserDetailId or PIIUserId properties. Instead, the relationship depends on the Id fields of both types.

If you are using the fluent API as above, you do not need to also specify LoyaltyUser.Id as a FK, EF will figure it out.

So without having your code to test myself (I hate doing this from my head)…I would translate this into your code as

public class PIIUser
{
    public int Id { get; set; }   
    public LoyaltyUserDetail LoyaltyUserDetail { get; set; }
}

public class LoyaltyUserDetail
{
    public int Id { get; set; }
    public double? AvailablePoints { get; set; }   
    public PIIUser PIIUser { get; set; }
}

modelBuilder.Entity<LoyaltyUserDetail>()
.HasRequired(lu => lu.PIIUser )
.WithOptional(pi => pi.LoyaltyUserDetail );
That’s saying LoyaltyUserDetails PIIUser property is required and PIIUser’s LoyaltyUserDetail property is optional.

You could start from the other end:

modelBuilder.Entity<PIIUser >()
.HasOptional(pi => pi.LoyaltyUserDetail );
.WithRequired(lu => lu.PIIUser);
which now says PIIUser’s LoyaltyUserDetail property is optional and LoyaltyUser’s PIIUser property is required.

You always have to use the pattern HAS/WITH.

other example:
One solution would be;

public class User
{
    [Key]
    public string Username { get; set; }

    public virtual Contact Contact { get; set; }
}

public class Contact
{
    [Key]
    public int ID { get; set; }
    public string Name { get; set; }

    public virtual User User { get; set; }
}

        modelBuilder.Entity<User>()
            .HasOptional<Contact>(u => u.Contact)
            .WithOptionalDependent(c => c.User).Map(p => p.MapKey(“ContactID”));

one to one (or one to zero/one) relationships are one of the most confusing relationships to configure in code first so you are not alone..:)

Configuring Relationships with the Fluent API- entity framework

Configuring Relationships with the Fluent API

Note: This page provides information about setting up relationships in your Code First model using the fluent API. For general information about relationships in EF and how to access and manipulate data using relationships, see Relationships & Navigation Properties.

When working with Code First, you define your model by defining your domain CLR classes. By default, the Entity Framework uses the Code First conventions to map your classes to the database schema. If you use the Code First naming conventions, in most cases you can rely on Code First to set up relationships between your tables based on the foreign keys and navigation properties that you define on the classes. If you do not follow the conventions when defining your classes, or if you want to change the way the conventions work, you can use the fluent API or data annotations to configure your classes so Code First can map the relationships between your tables.

Contents

Introduction
Configuring a Required-to-Optional Relationship (One-to-Zero-or-One)
Configuring a Relationship Where Both Ends Are Required (One-to-One)
Configuring a Many-to-Many Relationship
Configuring a Relationship with One Navigation Property
Enabling Cascade Delete
Configuring a Composite Foreign Key
Renaming a Foreign Key That Is Not Defined in the Model
Configuring a Foreign Key Name That Does Not Follow the Code First Convention
Model Used in Samples

Introduction

When configuring a relationship with the fluent API, you start with the EntityTypeConfiguration instance and then use the HasRequired, HasOptional, or HasMany method to specify the type of relationship this entity participates in. The HasRequired and HasOptional methods take a lambda expression that represents a reference navigation property. The HasMany method takes a lambda expression that represents a collection navigation property. You can then configure an inverse navigation property by using the WithRequired, WithOptional, and WithMany methods. These methods have overloads that do not take arguments and can be used to specify cardinality with unidirectional navigations.

You can then configure foreign key properties by using the HasForeignKey method. This method takes a lambda expression that represents the property to be used as the foreign key.

Configuring a Required-to-Optional Relationship (One-to–Zero-or-One)

The following example configures a one-to-zero-or-one relationship. The OfficeAssignment has the InstructorID property that is a primary key and a foreign key, because the name of the property does not follow the convention the HasKey method is used to configure the primary key.

// Configure the primary key for the OfficeAssignment
modelBuilder.Entity()
.HasKey(t => t.InstructorID);

// Map one-to-zero or one relationship
modelBuilder.Entity()
.HasRequired(t => t.Instructor)
.WithOptional(t => t.OfficeAssignment);

Configuring a Relationship Where Both Ends Are Required (One-to-One)

In most cases the Entity Framework can infer which type is the dependent and which is the principal in a relationship. However, when both ends of the relationship are required or both sides are optional the Entity Framework cannot identify the dependent and principal. When both ends of the relationship are required, use WithRequiredPrincipal or WithRequiredDependent after the HasRequired method. When both ends of the relationship are optional, use WithOptionalPrincipal or WithOptionalDependent after the HasOptional method.

// Configure the primary key for the OfficeAssignment
modelBuilder.Entity()
.HasKey(t => t.InstructorID);

modelBuilder.Entity()
.HasRequired(t => t.OfficeAssignment)
.WithRequiredPrincipal(t => t.Instructor);

Configuring a Many-to-Many Relationship

The following code configures a many-to-many relationship between the Course and Instructor types. In the following example, the default Code First conventions are used to create a join table. As a result the CourseInstructor table is created with Course_CourseID and Instructor_InstructorID columns.

modelBuilder.Entity()
.HasMany(t => t.Instructors)
.WithMany(t => t.Courses)

If you want to specify the join table name and the names of the columns in the table you need to do additional configuration by using the Map method. The following code generates the CourseInstructor table with CourseID and InstructorID columns.

modelBuilder.Entity()
.HasMany(t => t.Instructors)
.WithMany(t => t.Courses)
.Map(m =>
{
m.ToTable(“CourseInstructor”);
m.MapLeftKey(“CourseID”);
m.MapRightKey(“InstructorID”);
});

Configuring a Relationship with One Navigation Property

A one-directional (also called unidirectional) relationship is when a navigation property is defined on only one of the relationship ends and not on both. By convention, Code First always interprets a unidirectional relationship as one-to-many. For example, if you want a one-to-one relationship between Instructor and OfficeAssignment, where you have a navigation property on only the Instructor type, you need to use the fluent API to configure this relationship.

// Configure the primary Key for the OfficeAssignment
modelBuilder.Entity()
.HasKey(t => t.InstructorID);

modelBuilder.Entity()
.HasRequired(t => t.OfficeAssignment)
.WithRequiredPrincipal();

Enabling Cascade Delete

You can configure cascade delete on a relationship by using the WillCascadeOnDelete method. If a foreign key on the dependent entity is not nullable, then Code First sets cascade delete on the relationship. If a foreign key on the dependent entity is nullable, Code First does not set cascade delete on the relationship, and when the principal is deleted the foreign key will be set to null.

You can remove these cascade delete conventions by using:

modelBuilder.Conventions.Remove()
modelBuilder.Conventions.Remove()

The following code configures the relationship to be required and then disables cascade delete.

modelBuilder.Entity()
.HasRequired(t => t.Department)
.WithMany(t => t.Courses)
.HasForeignKey(d => d.DepartmentID)
.WillCascadeOnDelete(false);

Configuring a Composite Foreign Key

If the primary key on the Department type consisted of DepartmentID and Name properties, you would configure the primary key for the Department and the foreign key on the Course types as follows:

// Composite primary key
modelBuilder.Entity()
.HasKey(d => new { d.DepartmentID, d.Name });

// Composite foreign key
modelBuilder.Entity()
.HasRequired(c => c.Department)
.WithMany(d => d.Courses)
.HasForeignKey(d => new { d.DepartmentID, d.DepartmentName });

Renaming a Foreign Key That Is Not Defined in the Model

If you choose not to define a foreign key on the CLR type, but want to specify what name it should have in the database, do the following:

modelBuilder.Entity()
.HasRequired(c => c.Department)
.WithMany(t => t.Courses)
.Map(m => m.MapKey(“ChangedDepartmentID”));

Configuring a Foreign Key Name That Does Not Follow the Code First Convention

If the foreign key property on the Course class was called SomeDepartmentID instead of DepartmentID, you would need to do the following to specify that you want SomeDepartmentID to be the foreign key:

modelBuilder.Entity()
.HasRequired(c => c.Department)
.WithMany(d => d.Courses)
.HasForeignKey(c => c.SomeDepartmentID);

Model Used in Samples

The following Code First model is used for the samples on this page.

using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
// add a reference to System.ComponentModel.DataAnnotations DLL
using System.ComponentModel.DataAnnotations;
using System.Collections.Generic;
using System;

public class SchoolEntities : DbContext
{
public DbSet Courses { get; set; }
public DbSet Departments { get; set; }
public DbSet Instructors { get; set; }
public DbSet OfficeAssignments { get; set; }

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// Configure Code First to ignore PluralizingTableName convention
// If you keep this convention then the generated tables will have pluralized names.
modelBuilder.Conventions.Remove();
}
}

public class Department
{
public Department()
{
this.Courses = new HashSet();
}
// Primary key
public int DepartmentID { get; set; }
public string Name { get; set; }
public decimal Budget { get; set; }
public System.DateTime StartDate { get; set; }
public int? Administrator { get; set; }

// Navigation property
public virtual ICollection Courses { get; private set; }
}

public class Course
{
public Course()
{
this.Instructors = new HashSet();
}
// Primary key
public int CourseID { get; set; }

public string Title { get; set; }
public int Credits { get; set; }

// Foreign key
public int DepartmentID { get; set; }

// Navigation properties
public virtual Department Department { get; set; }
public virtual ICollection Instructors { get; private set; }
}

public partial class OnlineCourse : Course
{
public string URL { get; set; }
}

public partial class OnsiteCourse : Course
{
public OnsiteCourse()
{
Details = new Details();
}

public Details Details { get; set; }
}

public class Details
{
public System.DateTime Time { get; set; }
public string Location { get; set; }
public string Days { get; set; }
}

public class Instructor
{
public Instructor()
{
this.Courses = new List();
}

// Primary key
public int InstructorID { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
public System.DateTime HireDate { get; set; }

// Navigation properties
public virtual ICollection Courses { get; private set; }
}

public class OfficeAssignment
{
// Specifying InstructorID as a primary
[Key()]
public Int32 InstructorID { get; set; }

public string Location { get; set; }

// When the Entity Framework sees Timestamp attribute
// it configures ConcurrencyCheck and DatabaseGeneratedPattern=Computed.
[Timestamp]
public Byte[] Timestamp { get; set; }

// Navigation property
public virtual Instructor Instructor { get; set; }
}
http://msdn.microsoft.com/en-gb/data/jj591620.aspx

Entity Framework Gets Code First Model Generation Through Reverse Engineering

  • Microsoft has released a minor version update of its popular Object Relational Mapping (ORM) tool with numerous bug fixes and new features, including the ability to reverse engineer an existing database to create a Code First model.

    Entity Framework (EF) 6.1 is the first “.x” update since Microsoft open sourced the tool moving to version 6 and moved it to CodePlex. It features tooling consolidation that “extends the ADO.NET Entity Data Model wizard to support creating Code First models, including reverse engineering from an existing database,” Microsoft’s Rowan Miller explained in an ADO.NET Blog post.

    Miller said this new functionality was available in beta quality in the EF Power Tools package, used by the engineering team to preview features under consideration for inclusion in a main release.

    Developers can now right-click on a C# project to get a context menu item to “Reverse Engineer Code First,” according to Data Developer Center documentation for the EF Power Tools. This command generates a derived DbContext class, “plain-old CLR object” (POCO) classes and Code First mapping classes from an existing database.

    Another context menu item is available to “Customize Reverse Engineer Templates,” which lets developers customize code generation via reverse engineer T4 templates added to projects.

    According to project meeting notes that Microsoft developers now put online, the Code First models that can now be created with the wizard will lack certain features, such as the ability to split tables and entities, and support for complex types and enums, among others.

    Microsoft has posted a Code First to an Existing Database video and walk-through where you can learn more about this new feature.

    EF6.1 also includes a new IndexAttribute functionality that lets developers specifically identify database table indexes with an [Index] attribute attached to properties in Code First models.

    Other enhancements to Entity Framework in version 6.1 listed by Miller include aCommitFailureHandlerpublic mapping API, performance improvements and many others.

    Microsoft engineers such as Diego Vega and Pawel Kadluczka have been busy this week helping developers who reported problems with the new version update in the comments section of Miller’s blog.

    For more support, Microsoft recommends developers post questions on the StackOverflow site, where six questions had been posted as of today.

    For future releases, the EF team’s roadmap indicates it’s considering “EF Everywhere,” described as “a lighter-weight version of EF that can be used across a wide variety of platforms (including Windows Store and Windows Phone).”

    original article:http://visualstudiomagazine.com/blogs/data-driver/2014/03/entity-frameworkupdate.aspx