Mvc5+ef6 Simple Example---the two-table federated Query of the original SQL Server database as an example

Tools: vs.net2013, EF6, MVC5, SQLServer2008

Reference Source:

Http://www.cnblogs.com/slark/p/mvc-5-get-started-create-project.html

Http://www.cnblogs.com/miro/p/4288184.html

Http://www.cnblogs.com/dotnetmvc/p/3732029.html

First, the preparatory work

To create a database on SQL Server: Element

Simulate two tables and insert data: sysuser (User table), Sysrole (role table)

CREATE TABLE [dbo]. [Sysuser] (
[ID] [int] IDENTITY (*) Not NULL,
[Name] [NCHAR] (ten) Not NULL,
[Rolenum] [NCHAR] (Ten) Not NULL
) on [PRIMARY]

CREATE TABLE [dbo]. [Sysrole] (
[ID] [int] IDENTITY (*) Not NULL,
[RoleName] [NCHAR] (ten) Not NULL,
[Rolenum] [NCHAR] (Ten) Not NULL
) on [PRIMARY]

Insert data:

Second, the new MVC Project generated solution folder, the understanding of MVC is still shallow, do not make too much explanation, please take a look at the heroes of the blog, you can benefit. To really understand the meaning of the framework, the advantages of layering, more hands-on, more feelings, more thinking. Through these days of learning, it is felt that the EF (entity FrameWork), like its name, is related to the model layer and can generate entity objects. And controll, view should not be directly linked, initially easy to mix with other layers in a piece, to distinguish between concepts and functions. (If understanding is not willing to accept criticism!) Install EF6: Second, use the EF code first to generate from the original database models on the Models folder right--add--New item data--ado.net Entity Data model such as cannot appear option, please download install entity Framework 6.1.0 Tools for Visual Studio & 2013:http://www.cnblogs.com/dotnetmvc/p/3644980.htmlSelect New Connection Click Change, select the two tables we need to generate the data model after the completion of the following three files are generated under the Models folder where ElementModel.cs is the database connection context, that is, the connection database, SysUser.cs and sysrole.c  S is the data entity model for the corresponding database table. C. Generate controller and view according to model

Right--add--Controller on the Controllers folder

Enter the controller name, select the model class, the database context (in the case of the Sysuser model), and then generate SysUserController.cs under the Controllers folder. The function of Sysusercontroller is to receive the action (action) of the view layer, and then to the corresponding model layer of the data interaction, and then return the results to view. Similarly, under the Views folder also generated the Sysuser folder, which corresponds to the five view page of the cshtml file. Right-index.cshtml--in the browser, display the following page: Four, the use of ViewModel display multi-table joint query just now we are using the controller to query the data from a table Sysuser to Sysuser (Model) Entity is returned to the index (view) display, and in practice we need to query the data in multiple tables and display them in the view. That's what we need. Viewmodel,viewmodel is an entity model that is closer to the view, that is, we build the entity model based on the needs of the data to be displayed in the view. The model is closer to the database entity. Here we implement a simple two-table joint query Small example: query out the Sysuser table of the user name and its corresponding role name. Right-click the solution-add-new folder (ViewModel), then right-ViewModel the folder--add--class to create a new Userrole class, and add the following entities:

Namespace Mvcdemo.viewmodels
{
public class Userrole
{
public string UserName {get; set;}
public string Userrole {get; set;}
}
}

Right-click the Controllers folder to add a control class that inherits from the Controller class

Add the following code (federated Query with LINQ to entity two tables):

Using System;

Using System.Collections.Generic;

Using System.Linq; Using System.Web;

Using SYSTEM.WEB.MVC; Using System.Data.Entity;

Using Mvcdemo.viewmodels;

Using Mvcdemo.models;

Namespace Mvcdemo.controllers

{

public class Userrolecontroller:controller

{

Elementmodel db = new Elementmodel ();

Public ActionResult Index ()

{

var userrolelist = from UU in db. SysUsers

Join UD in db. Sysroles on UU. Rolenum equals UD. Rolenum

where uu.id = = 1

Select New Userrole {userName = uu. Name,userrole = ud. RoleName}

Return View (userrolelist);

}

}

}

Right-click the Views folder, create new Userrole folder, right-userrole folder, add-MVC5 view page with layout index.cshtml, add code as follows:

@model ienumerable<mvcdemo.viewmodels.userrole>

@{

Layout = "~/views/shared/_layout.cshtml";

}

<table class= "Table" >

<tr>

<th>

@Html. Displaynamefor (Model=>model.username)

</th>

<th>

@Html. displaynamefor (model = model.userrole)

</th>

<th></th>

</tr>

@foreach (var item in Model)

{

<tr>

<td>

@Html. displayfor (ModelItem = item.username)

</td>

<td>

@Html. displayfor (ModelItem = item.userrole)

</td>

</tr>

}

</table>

Mvc5+ef6 Simple Example---the two-table federated Query of the original SQL Server database as an example