CODEWITHSUNDEEP
asp.netentity-frameworkstored-proceduresentity-framework-migrations
NHK
NHK

Reputation: 107

how to Create Stored Procedure with database migration in Asp.Net

I am using migration to create my database model. I already created all tables by overriding OnModelCreating as bellow. 

protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder){
modelBuilder.Entity<AccessLevel>().Map(c =>
{
    .ToTable("AccessLevel", "HelpSystem");
    c.Properties(p => new
        {
            p.Id,
            p.Name,
            p.Description
        });
}).HasKey(x => x.Id);
modelBuilder.Entity<AccessLevel>().Property(x => x.Id).HasColumnAnnotation("DatabaseGenerated", DatabaseGeneratedOption.Identity);}

Now I want to create Stored Procedure to my database like this. How can I do it?

This is the Sql Script of my Stored Procedure 

CREATE PROCEDURE [HelpSystem].[sp_GetLanguageContentByObject] @InfoObjectId AS BIGINT AS
SELECT  io.Id InfoObjectId ,
io.Name InfoObject ,
cc.Id ComChannelId ,
cc.Name ComChannel ,
cc2.Id ContentCategoryId ,
cc2.Name ContentCategory ,
c.Id ContentId ,
c.Name ContentName ,
c.Description ,
c.AuthorPartyId ,
c.DefaultLanguageId ,
c3.CultureCode DefaultLanguage ,
lc.Id LanguageContentId ,
lc.LanguageId ,
c2.CultureCode Language ,
lc.Title ,
lc.Content FROM    HelpSystem.InfoPointInfoObject ipio
    JOIN HelpSystem.InfoObject io ON io.Id = ipio.InfoObjectId
    JOIN HelpSystem.InfoPointComChannel ipcc ON ipcc.InfoPointId = ipio.InfoPointId
    JOIN HelpSystem.ComChannel cc ON cc.Id = ipcc.ComChannelId
    JOIN HelpSystem.InfoPointContent ipc ON ipc.InfoPointId = ipcc.InfoPointId
    JOIN HelpSystem.Content c ON c.Id = ipc.ContentId
    JOIN HelpSystem.ContentCategory cc2 ON cc2.Id = c.ContentCategoryId
    JOIN HelpSystem.LanguageContent lc ON lc.ContentId = ipc.ContentId
    JOIN Localization.Culture c2 ON lc.LanguageId = c2.Id
    JOIN Localization.Culture c3 ON c.DefaultLanguageId = c3.Id WHERE ipio.InfoObjectId = @InfoObjectId AND (ipio.ExpireDateTime IS NULL OR ipio.ExpireDateTime > GETUTCDATE()) AND (ipcc.ExpiredDateTime IS NULL OR ipcc.ExpiredDateTime > GETUTCDATE()) AND (ipc.ExpiredDateTime IS NULL OR ipc.ExpiredDateTime > GETUTCDATE())

Upvotes: 1

Views: 1224

Answers (1)

Fiyaz Hasan
Fiyaz Hasan

Reputation: 838

You can write the stored procedure code directly on an instance of your dbcontext using the ExecuteSqlCommand API like this:

_dataContext.Database.ExecuteSqlCommand("stored procedure sql");

You can also use the CreateStoredProcedure function to create stored procedure directly from the DbMigration file. CreateStoredProcedure API

There is no extension method on the modelBuilder that will let you create stored procedures.

Upvotes: 2

Related Questions