Loading Entity Framework Navigation Properties with Stored Procedures

I don’t see a lot of posts about this but it is possible to achieve.

Classes

public class MyContext: DbContext
{
    public MyContext()
    {
        Configuration.LazyLoadingEnabled = true;
        Configuration.ProxyCreationEnabled = true;
    }

    public DbSet<Table1> T1 { get; set; }

    public DbSet<Table2> T2 { get; set; }
}

[Table("Table1")]
public class Table1
{
    public int Id { get; set; }

    public int Tabel2Id { get; set; }

    [ForeignKey("Tabel2Id ")]
    public virtual Table2 Table2 { get; set; }
}

[Table("Table2")]
public class Table2
{
    public int Id { get; set; }

    public string SomeValue { get; set; }
}

Example Stored Procedure

SELECT * FROM Table1 WHERE Id = @SearchId

Using a DbSet … this DOES NOT WORK if you are using a full DbContext class.

var searchIdParam = new SqlParameter("@SearchId", searchId);
var searchResults = _dbSet.SqlQuery("LoadMyTable1s @SearchId);

You’ll find that your navigation properties are in fact loaded. I’ve been using this since at least EF5 …

Share

Entity Framework Code First Migrations and Running SQL Scripts

I suspect I’m not alone in that even though I’m using Code First for my data access there are points where it just makes more sense to encapsulate a complex process in a stored procedure.  But getting a database created using Migrations and including the process of running all the required scripts into a database on initial creation isn’t the most obvious thing.  You also want it to be repeatable, consistent and you don’t want to forget any.

You may have other scripts that create triggers or functions in addition to stored procedures and I’ve written this into my Configuration class.  The initial problem is that you need to include in your scripts a check to make sure that the object you are about to create in your database doesn’t already exist or the creation process will error.  The exact way to do this depends on the type of object you are creating but an example is included here:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'usp_Create_Role')
SET NOEXEC ON
GO
CREATE PROCEDURE
...
GO

SET NOEXEC OFF
GO

The NOEXEC command will prevent the following statements from actually being executed until the NOEXEC is turned off.

The next part of this is to make use of the Seed method in the Configuration class and use of the #if DEBUG preprocessor directive. In my seed method I’ve included this:

#if DEBUG
RunExternalScripts(context);
#endif

The contents of this method is pretty simple and includes a process for enumerating the collection of scripts and then executing them, all pretty standard stuf really, my scripts are separated into directories by type (these are also all version controlled in SVN):

#if DEBUG

        /// <summary>
        /// Runs the external scripts.
        /// </summary>
        /// <param name="context">The context.</param>
        private void RunExternalScripts(MyDataDbContext context)
        {
            _subDirs = new[] { "data\schema\", "data\functions\", "data\storedprocedures\", "data\staticdata\" };
            _scriptRoot = "<ROOT PATH>";
            string currentScript = string.Empty;

            try
            {
                CompileSqlScripts();

                if (SqlScripts != null && SqlScripts.Count > 0)
                {
                    foreach (var sqlScript in SqlScripts)
                    {
                        currentScript = sqlScript;
                        ScriptExecutor.ExecuteFile(context.Database.Connection.ConnectionString, currentScript);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException(string.Format("The database could not be initialized. Error running {0}.", currentScript), ex);
            }
        }
#endif

So now any time I want to delete my local database and start from scratch for any reason all my scripts are run into the database. Because all my scripts also check for the objects existence before running I can also happily run update-database commands in the Package Manager console with any worries (the seed command is run after every update). But also because all of these methods are within #if DEBUG directive none of this code will be compiled into the release version of the assemblies.

Share

Enabling Entity Framework Code First Migrations ProviderIncompatibleException

Quite simple actually … make sure the connection string in your config file is named the same as your context class OR make sure you do something like this public MyContextName : base(“connectionstringname”).

It’s also worth checking the name of the connection string being used in the configuration file for the start up project if your data context is in a class library as this is the connection string that will be used.

Share

Entity Framework Code-First Stored Procedures & Foreign Key Entities

I was recently looking at some changes I’d made to a project I’m working on using the Entity Framework for data access. I realised that as I had moved to using a stored procedure for the data retrieval I had lost the immediate loading of some of he object related by foreign keys. If you want to load the related entities, don’t do this:

var searchResults = _context.Database.SqlQuery<MyObject>("GetMyObjects" @UserId", inputTerm);

Do this! 🙂

var searchResults = _dbSet.SqlQuery("GetMyObjects" @UserId", inputTerm);

Executing the stored procedure on the DbSet rather than the DbContext object will load the foreign key related entities.

Tip: If your entities still aren’t loading, check to make sure you have marked them as virtual in the entity class:

public virtual Organisation Organisation { get; set; }
Share