Nome colonna non valido quando si include la proprietà di navigazione raccolta figlio che coinvolge 2 join nella stessa tabella

Question

Nome colonna non valido quando si include la proprietà di navigazione raccolta figlio che coinvolge 2 join nella stessa tabella

c# entity-framework entity-framework-6

Domanda

Ho scritto una funzione GetList repository che funziona bene per la maggior parte delle proprietà di navigazione. Tuttavia fallisce quando provo e includo una particolare proprietà di navigazione che si basa su una collezione di entità.

le seguenti classi di dominio che implicano una relazione molte a molte

[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; }
}

Il seguente non riesce

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
    }
}

dove funziona come segue

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);   
    }
}

Le mie classi di repository sono:

 // 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
}

Il messaggio di errore è il seguente

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)

[Aggiornamento] Mi chiedo se l'errore non sia dovuto alle convenzioni di denominazione di EF. Come visto qui, tuttavia, pensavo di averlo gestito nella classe TemplateTaskConfiguration

Kirsten Greed

Vedi di più su Stack Overflow

Kirsten Greed · Accepted Answer · 9/26/2014 6:58:29 AM

Il problema era che la mia classe TemplateContext non aveva impostato TemplateTaskConfiguration.

L'ho fatto nel contesto principale che viene utilizzato per creare il database, tuttavia ho dimenticato che se sono implementati più contesti, tutti devono implementare le regole di modelBuilder.

La mia classe TemplateContext ora legge

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());
    }
}

Anche se ora sembra ovvio, ho passato l'intera giornata a cercare di risolvere questo problema!

Nome colonna non valido quando si include la proprietà di navigazione raccolta figlio che coinvolge 2 join nella stessa tabella

Domanda

Fastest Entity Framework Extensions

Risposta accettata

Performance

Expression Evaluator

Related