Using the view in the database in code first

If you want to use the view in the database in code first

(For whatever reason), there are 2 ways to do so.

First, use database.sqlquery<t> ("query statement"), such as:

var query = db. Database.sqlquery<replystatusviewmodel> ("select * from Dbo.vreplystatus")

Then make a variety of queries based on the Vreplystatus view:

var qqo = query. Where (p = p.prdord.contains ("Yuan")); var count = Qqo. Count (); // will go wrong! -->nullreferenceexception--> because there are null or null values in the Prdord field. 

The following can be adopted as follows:

var P.prdord! = null && p.prdord.contains (" yuan "));

The resulting statement is:

SELECT *  from Dbo.vreplystatus. Where = != NULL AndAlsoP.prdord. Contains ("Yuan")))

However, when using dynamic LINQ, it seems impossible to put p. Prodord! = NULL Plus, the statement generated by Dynamic LINQ is:

SELECT *  from Dbo.vreplystatus. Where = = P.planner. Contains ("Yuan")). Union (SELECT* from Dbo.vreplystatus. Where = = P.prdord. Contains ("Yuan")). Union (SELECT...

This is a fuzzy query statement that finds data in all fields that contain yuan. But the!=null condition is less, so that at the time of the count var count = Qqo. Count (); it will go wrong.

Second, add the view model in context

Now using the second method, add the Replystatusviewmodel class to the context class, and then join the entity when the model is created (pretend the view is a table). After you have prompted the data migration, do not update-database! Comment out the contents of the migration file by emptying the contents of the public override void up () and public override void () to make an empty migration and finally update the database.

The steps are as follows:

1. Create a Class (Replystatusviewmodel) that corresponds to field one by one in this class and view.

 Public class Replystatusviewmodel{ Public intID {Get;Set; }  Public BOOLDel {Get;Set; }  Public decimal? Qty {Get;Set; }  Public stringReplystatus {Get;Set; }}

2. Change the context class

 Public classMycontext:dbcontext
{     PublicMycontext ():Base("name=defaultconnection") { }     Publicdbset<Replystatusviewmodel>Replystatusviewmodel{Get;Set; }//views in the mapping database     protected Override voidOnmodelcreating (Dbmodelbuilder modelBuilder) {
Vreplystatus is the name of the View modelbuilder.entity<replystatusviewmodel> (). ToTable ("vreplystatus");     }}

3. Add Data Migration

Pm> add-migration Test

Automatically displays a class file that changes the context to create a table:

  Public Partial classtest:dbmigration { Public Override voidUp () { createtable("dbo. Replystatusviewmodels", C=New{ID= C.int (Nullable:false, Identity:true), Del= C.boolean (Nullable:false), Qty= C.decimal (Precision: -, Scale:2), Replystatus=c.string (),}) . PrimaryKey (t=t.id); }                 Public Override voidDown () { droptable("dbo. Replystatusviewmodels"); }    }}

The content here is not what we need, if there is no view in the database, you can create a view in the UP:

 Public Override voidUp () {SQL (@"CREATE VIEW [dbo]. [Vreplystatus] As SELECT a.id, ..."); }         Public Override voidDown () {SQL (@"IF EXISTS
(SELECT * from sys.views WHERE object_id = object_id (N ' dbo.vreplystatus ')) DROP VIEW Dbo.vreplystatus"); }

If a view is already in the database, clear the contents:

 Public Override void Up () {         }        publiclyoverridevoid down () {}              

Adding a data migration can add a parameter: Ignorechanges, so that up and down are automatically blank.

Last Run

Pm> Update-database

The controller can use the view like any other entity class!

 Public stringGetmasterdata (){  using(vardb =NewPurchaseplancontext ()) {IQueryable<Replystatusviewmodel> Query9 =db.     Replystatusviewmodel; Query9= fromMinchQuery9whereM.prdord.contains ("Yuan")              Selectm; varCount9 =Query9.      Count (); ...    } }

Http://stackoverflow.com/questions/13593845/how-to-create-a-view-using-ef-code-first-poco

Http://stackoverflow.com/questions/20862807/mapping-database-views-to-ef-5-0-code-first-w-migrations?rq=1

Http://msdn.microsoft.com/zh-cn/magazine/dn519921.aspx

Here is a CreateView method, unfortunately I did not find!

   Public Partial classaddview:dbmigration { Public Override voidUp () { This. CreateView ("dbo. Casinoswithover100slotmachines",                      @"SELECT * FROM Casino.casinos WHERE ID in (SELECT casinoid as ID  From Casino.slotmachines GROUP by Casinoid have COUNT (casinoid) >=100)"); }     Public Override voidDown () { This. Removeview ("dbo. Casinoswithover100slotmachines"); }  }

--end--