Invalid column name when including child collection navigation property involving 2 joins to the same table

c# entity-framework entity-framework-6

Question

I have written a repository GetList function which works fine for most navigation properties. However it fails when I try and include a particular navigation property that is based on a collection of entities.

the following domain classes which involve a many to many relationship

[Table("Template")]
public class Template : LoggedEntity
{
    public string Description { get; set; }

    public virtual ICollection<TemplateTask> Tasks { get; set; }
}

[Table("TemplateTask")]
public class TemplateTask : LoggedEntity
{
    [Column("TemplateId")]
    public int TemplateId { get; set; }

    [ForeignKey("TemplateId")]
    public virtual Template Template { get; set; }

    public virtual ICollection<TemplateTaskDependancy>  Dependancies { get; set; }  

    public virtual ICollection<TemplateTaskDependancy> NeededTasks { get; set; }
}

[Table("TemplateTaskDependancy")]
public class TemplateTaskDependancy : LoggedEntity
{
    [Column]
    public int NeededTaskId { get; set; }

    [Column]
    public int TaskId { get; set; }

    [Required]
    [ForeignKey("TaskId")]
    public virtual TemplateTask Task { get; set; }

    [Required]
    [ForeignKey("NeededTaskId")]
    public virtual TemplateTask NeededTask { get; set; }
}

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

The following fails

public IList<TemplateTask> GetTaskData()
{
    using (var uow = new UnitOfWork<TemplateContext>())
    using (var repository = new TemplateTaskRepository(uow))
    {
        int templateId = 1;
        return repository.GetList(p => p.Template.Id == templateId,
                                  a => a.Template, 
                                  c => c.Dependancies);  // runtime error occurs if c.Dependancies is included
    }
}

where as the following works

public IList<TemplateTask> GetTaskData()
{
    using (var uow = new UnitOfWork<TemplateContext>())
    using (var repository = new TemplateTaskRepository(uow))
    {
        int templateId = 1;
        return repository.GetList(p => p.Template.Id == templateId,
        a => a.Template);   
    }
}

My repository classes are:

 // based on Julie Lerman's repository pattern
 public class TemplateTaskRepository : LoggedEntityRepositoryBase<TemplateTask>, ITemplateTaskRepository
 {
    public TemplateTaskRepository(IUnitOfWork uow) : base(uow)
    {
    }
}

public interface ITemplateTaskRepository : IEntityRepository<TemplateTask>
{
}

public class LoggedEntityRepositoryBase<T> : IEntityRepository<T> where T : LoggedEntity
{
    private readonly IContext context;
    private readonly bool hasUnitOfWork;

    public LoggedEntityRepositoryBase(IUnitOfWork uow)
    {
        hasUnitOfWork = true;
        context = uow.Context;
    }

    public LoggedEntityRepositoryBase(IContext pContext)
    {
        context = pContext;
    }

    public IContext IContext
    {
        get
        {
            return context;
        }
    }
    public virtual IList<T> GetList(Func<T, bool> where, params Expression<Func<T, object>>[] navigationProperties)
    {
        List<T> list;
        IQueryable<T> dbQuery = ((DbContext)context).Set<T>();

        //Apply eager loading
        foreach (var navigationProperty in navigationProperties)
        {
            dbQuery = dbQuery.Include(navigationProperty);
        }

        list = dbQuery.AsNoTracking().Where(where).ToList(); // error occurs here

        return list;
    }
 // etc
}

public interface IEntityRepository<T> : IDisposable where T : class
{
    IList<T> GetList(Func<T, bool> where, params Expression<Func<T, object>>[] navigationProperties);
    // etc
}

The error message is as follows

System.Data.Entity.Core.EntityCommandExecutionException was unhandled
  HResult=-2146232004
  Message=An error occurred while executing the command definition. See the inner exception for details.
  Source=EntityFramework
  StackTrace:
       at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
       at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
       at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
       at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
       at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
       at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
       at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
       at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at SyriusData.LoggedEntityRepositoryBase`1.GetList(Func`2 where, Expression`1[] navigationProperties) in e:\EShared\Dev2013SyriusTest\SyriusTest\SyriusData\LoggedEntityRepositoryBase.cs:line 87
       at SyriusTest.Form1.GetTaskData() in e:\EShared\Dev2013SyriusTest\SyriusTest\SyriusTest\Form1.cs:line 56
       at SyriusTest.Form1.button1_Click(Object sender, EventArgs e) in e:\EShared\Dev2013SyriusTest\SyriusTest\SyriusTest\Form1.cs:line 41
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at SyriusTest.Program.Main() in e:\EShared\Dev2013SyriusTest\SyriusTest\SyriusTest\Program.cs:line 19
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.Data.SqlClient.SqlException
       HResult=-2146232060
       Message=Invalid column name 'TemplateTask_Id'.
       Source=.Net SqlClient Data Provider
       ErrorCode=-2146232060
       Class=16
       LineNumber=29
       Number=207
       Procedure=""
       Server=Myserver\SQL2008R2
       State=1
       StackTrace:
            at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
            at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
            at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
            at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
            at System.Data.SqlClient.SqlDataReader.get_MetaData()
            at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
            at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
            at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
            at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
            at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

[Update] I am wondering if the error is due to not following EF's naming conventions. As seen here however I thought I had handled that in the TemplateTaskConfiguration class

1
0
5/23/2017 12:07:25 PM

Accepted Answer

The problem was that my TemplateContext class did not have TemplateTaskConfiguration set up.

I had done it in the main context that is used to create the database, however I forgot that if multiple contexts are implemented then they all need to implement the modelBuilder rules.

My TemplateContext class now reads

public class TemplateContext : ContextBase<TemplateContext>, ITemplateContext
{
    public DbSet<Template> Templates { get; set; }
    public DbSet<TemplateTask> TemplateTasks { get; set; }
    public DbSet<TemplateTaskDependancy> TemplateTaskDependancies { get; set; }


    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        modelBuilder.Configurations.Add(new TemplateTaskConfiguration());
    }
}

Although it now seems obvious, I spent the whole day trying to solve this issue!

0
9/26/2014 6:58:29 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow