Can You Create SQL Views/Stored Procedure Using Entity Framework 4.1 Code First Approach

Can you create sql views / stored procedure using Entity Framework 4.1 Code first approach

EF code-first approach expects that there is no logic in the database. That means no stored procedures and no database views. Because of that code-first approach doesn't provide any mechanism to generate such constructs automatically for you. How could it do that if it means generating logic?

You must create them yourselves in custom database initializer by manual executing creation scripts. I don't think that this custom SQL constructs can be handled by SQL migrations.

How do I define a database view using Entity Framework 4 Code-First?

That's because you cannot define database view using code-first approach. Database view is database construct which uses SQL Query on top of existing tables / functions. You can't define such constructs using code first.

If you want view you must create it manually by executing CREATE VIEW SQL script for example in custom initializer - it will be similar like this answer. Just be aware that this will not help you if you want to map entity to a view. In such case you would probably have to first drop table created by EF and create view with the same name (I didn't try it but it could do the trick). Also be aware that not every view is udpatable so you will most probably get read only entity.

Update stored procedure in Code First DbMigration

Separate your stored procedures from your model creation by doing your stored procedure updates in the migration Seed() method which runs after all the migrations:

context.Database.ExecuteSqlCommand(sp_DoSomething);

Since this runs with every update-database, you will need to make the script idempotent by adding an existance check to the start of Create_sp_DoSomething:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_DoSomething')
BEGIN
    DROP PROCEDURE sp_DoSomething
END

Code-First Entity Framework w/ Stored Procedure returning results from complex Full-text Searches

Posting this as an answer rather than an edit to my question:

Taking some of the insight provided by @Drauka's (and google) here is what I did for my initial iteration.

1. Created the stored procedure to do the full text searching. It was really too complex to be done in EF even if supported (as one example some of my entities are related via business logic and I wanted to group them returning as a single result). The stored procedure maps to a DTO with the entity id's and a Rank.
2. I modified this blogger's snippet / code to make the call to the stored procedure, and populate my DTO: http://www.lucbos.net/2012/03/calling-stored-procedure-with-entity.html

3. I populate my results object with totals and paging information from the results of the stored procedure and then just load the entities for the current page of results:

   int[] projectIDs = new int[Settings.Default.ResultsPerPage];
   foreach (ProjectFTS_DTO dto in 
             RankedSearchResults
             .Skip(Settings.Default.ResultsPerPage * (pageNum - 1))
             .Take(Settings.Default.ResultsPerPage)) {
                projectIDs[index] = dto.ProjectID;
                index++;
           }
   
   IEnumerable<Project> projects = _repository.Projects
               .Where(o=>projectIDs.Contains(o.ProjectID));
   

Full Implementation:

As this question receives a lot of views I thought it may be worth while to post more details of my final solution for others help or possible improvement.

The complete solution looks like:

DatabaseExtensions class:

public static class DatabaseExtensions {
    public static IEnumerable<TResult> ExecuteStoredProcedure<TResult>(
             this Database database, 
             IStoredProcedure<TResult> procedure, 
             string spName) {
        var parameters = CreateSqlParametersFromProperties(procedure);
        var format = CreateSPCommand<TResult>(parameters, spName);
        return database.SqlQuery<TResult>(format, parameters.Cast<object>().ToArray());
    }

    private static List<SqlParameter> CreateSqlParametersFromProperties<TResult>
             (IStoredProcedure<TResult> procedure) {
        var procedureType = procedure.GetType();
        var propertiesOfProcedure = procedureType.GetProperties(BindingFlags.Public | BindingFlags.Instance);

        var parameters =
            propertiesOfProcedure.Select(propertyInfo => new SqlParameter(
                    string.Format("@{0}", 
                    (object) propertyInfo.Name), 
                    propertyInfo.GetValue(procedure, new object[] {})))
                .ToList();
        return parameters;
    }

    private static string CreateSPCommand<TResult>(List<SqlParameter> parameters, string spName)
    {
        var name = typeof(TResult).Name;
        string queryString = string.Format("{0}", spName);
        parameters.ForEach(x => queryString = string.Format("{0} {1},", queryString, x.ParameterName));

        return queryString.TrimEnd(',');
    }

    public interface IStoredProcedure<TResult> {
    }
}

Class to hold stored proc inputs:

class AdvancedFTS : 
         DatabaseExtensions.IStoredProcedure<AdvancedFTSDTO> {
    public string SearchText { get; set; }
    public int MinRank { get; set; }
    public bool IncludeTitle { get; set; }
    public bool IncludeDescription { get; set; }
    public int StartYear { get; set; }
    public int EndYear { get; set; }
    public string FilterTags { get; set; }
}

Results object:

public class ResultsFTSDTO {
    public int ID { get; set; }
    public decimal weightRank { get; set; }
}

Finally calling the stored procedure:

public List<ResultsFTSDTO> getAdvancedFTSResults(
            string searchText, int minRank,
            bool IncludeTitle,
            bool IncludeDescription,
            int StartYear,
            int EndYear,
            string FilterTags) {

        AdvancedFTS sp = new AdvancedFTS() {
            SearchText = searchText,
            MinRank = minRank,
            IncludeTitle=IncludeTitle,
            IncludeDescription=IncludeDescription,
            StartYear=StartYear,
            EndYear = EndYear,
            FilterTags=FilterTags
        };
        IEnumerable<ResultsFTSDTO> resultSet = _context.Database.ExecuteStoredProcedure(sp, "ResultsAdvancedFTS");
        return resultSet.ToList();

    }

---

Related Topics