locked
Return a Join to a view RRS feed

  • Question

  • User-164452226 posted

    Good day tech gurus. l want to return a join to a view but l have tried with no success at all. Here is what l have.

    1) Classes/SQL tables l intend to join

        public class SchemeBook
        {
            [Key]
            public Int64 Id { get; set; }
            public string StudentID { get; set; }
            public string SchemeID { get; set; }
            public string Subject { get; set; }
            public string Grade { get; set; }
            public int SchoolYear { get; set; }
            public int SchoolTerm { get; set; }
            public Int64 Userid { get; set; }
        }   
    
    
        public class SchemeWeek
        {
            public Nullable <Int64> Id { get; set; }
            [Key]
            public Int64 WeekID { get; set; }
            public string WeekEnding { get; set; }
            public Int64 Userid { get; set; }
        }

    2) Class to Join the two classes/Tables

        public class JoinedViews
        {
            public SchemeBook SchemeBk { get; set; }
            public SchemeWeek SchemeWk { get; set; }
        }

    3) Entity Framework- connecting to SQL DB

      public class EFSchemeBookRepository : ISchemeBookRepository
        {
            private EFDbContext context = new EFDbContext();
            public IEnumerable<SchemeBook> SchemeBooks
            {
                get { return context.SchemeBooks; }
            }
           //Methods to save and delete from db
    
        }
    
        public class EFSchemeWeekRepository : ISchemeWeekRepository
        {
            private EFDbContext context = new EFDbContext();
            public IEnumerable<SchemeWeek> SchemeWeeks
            {
                get { return context.SchemeWeeks; }
            }
            //Methods to save and delete from db
        }

    4) Controller ViewResult Method

            public ViewResult GetSchemeBookWeeks()
            {
                
                EFSchemeBookRepository SBrepository = new EFSchemeBookRepository();
                EFSchemeWeekRepository SWrepository = new EFSchemeWeekRepository();
    
                var SchemeWks = from s in SBrepository.SchemeBooks
                                join w in SWrepository.SchemeWeeks
                                on s.Id equals w.Id into sw
                                select new
                                {
    
                                    schemeBook = s,
                                    schemeWeek = sw
                                };
                return View(SchemeWks);
            }

    5) My View

    @model IEnumerable<CollegeSys.Domain.Entities.JoinedViews>
    @{
        ViewBag.Title = "GetSchemeBookWeeks";
        Layout = "~/Views/Shared/_SchemeBookNav.cshtml";
    }
    <table class="table table-striped">
        <tr>
            <td>Student ID</td>
            <td>Scheme ID</td>
            <td>WeekID</td>
            <td>Weekending</td>
        </tr>
        @foreach (var item in Model)
        {
            <tr>
                <td>@item.SchemeBk.StudentID</td>
                <td>@item.SchemeBk.Id</td>
                <td>@item.SchemeWk.WeekID</td>
                <td>@item.SchemeWk.WeekEnding</td>
            </tr>
        }
    </table>

    6) Error


    Server Error in '/' Application.
    
    The model item passed into the dictionary is of type 'System.Linq.Enumerable+<GroupJoinIterator>d__41`4[CollegeSys.Domain.Entities.SchemeBook,CollegeSys.Domain.Entities.SchemeWeek,System.Nullable`1[System.Int64],<>f__AnonymousType1`2[CollegeSys.Domain.Entities.SchemeBook,System.Collections.Generic.IEnumerable`1[CollegeSys.Domain.Entities.SchemeWeek]]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[CollegeSys.Domain.Entities.JoinedViews]'. 
      Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
    
     Exception Details: System.InvalidOperationException: The model item passed into the dictionary is of type 'System.Linq.Enumerable+<GroupJoinIterator>d__41`4[CollegeSys.Domain.Entities.SchemeBook,CollegeSys.Domain.Entities.SchemeWeek,System.Nullable`1[System.Int64],<>f__AnonymousType1`2[CollegeSys.Domain.Entities.SchemeBook,System.Collections.Generic.IEnumerable`1[CollegeSys.Domain.Entities.SchemeWeek]]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[CollegeSys.Domain.Entities.JoinedViews]'.
    
    Source Error: 
    
    
     An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  
    
    Stack Trace: 
    
    
    
    [InvalidOperationException: The model item passed into the dictionary is of type 'System.Linq.Enumerable+<GroupJoinIterator>d__41`4[CollegeSys.Domain.Entities.SchemeBook,CollegeSys.Domain.Entities.SchemeWeek,System.Nullable`1[System.Int64],<>f__AnonymousType1`2[CollegeSys.Domain.Entities.SchemeBook,System.Collections.Generic.IEnumerable`1[CollegeSys.Domain.Entities.SchemeWeek]]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[CollegeSys.Domain.Entities.JoinedViews]'.]
       System.Web.Mvc.ViewDataDictionary`1.SetModel(Object value) +175
       System.Web.Mvc.ViewDataDictionary..ctor(ViewDataDictionary dictionary) +107
       System.Web.Mvc.WebViewPage`1.SetViewData(ViewDataDictionary viewData) +49
       System.Web.Mvc.RazorView.RenderView(ViewContext viewContext, TextWriter writer, Object instance) +99
       System.Web.Mvc.BuildManagerCompiledView.Render(ViewContext viewContext, TextWriter writer) +107
       System.Web.Mvc.ViewResultBase.ExecuteResult(ControllerContext context) +291
       System.Web.Mvc.ControllerActionInvoker.InvokeActionResult(ControllerContext controllerContext, ActionResult actionResult) +13
       System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList`1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult) +56
       System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList`1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult) +420
       System.Web.Mvc.ControllerActionInvoker.InvokeActionResultWithFilters(ControllerContext controllerContext, IList`1 filters, ActionResult actionResult) +52
       System.Web.Mvc.Async.<>c__DisplayClass2b.<BeginInvokeAction>b__1c() +173
       System.Web.Mvc.Async.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult) +100
       System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
       System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
       System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +27
       System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) +13
       System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +29
       System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
       System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +36
       System.Web.Mvc.Controller.<BeginExecute>b__15(IAsyncResult asyncResult, Controller controller) +12
       System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +22
       System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
       System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +26
       System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
       System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__5(IAsyncResult asyncResult, ProcessRequestState innerState) +21
       System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +29
       System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
       System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +28
       System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
       System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9873789
       System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +48
       System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +159
     
    

    Please help me resolve this error, or suggest a better way of achieving this. l would also want to return this view for a specific userid. Thanks in advance

    Wednesday, October 10, 2018 7:26 AM

Answers

  • User-369506445 posted

    in the controller, your query <g class="gr_ gr_6 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="6" data-gr-id="6">return</g> an anonymous Type  and in the <g class="gr_ gr_7 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="7" data-gr-id="7">View</g> you set <g class="gr_ gr_4 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="4" data-gr-id="4">a </g><g class="gr_ gr_4 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Grammar multiReplace" id="4" data-gr-id="4">IEnumerable</g><JoinedViews>

    you have to define you type in your query

    var SchemeWks = from s in SBrepository.SchemeBooks()
                                join w in SWrepository.SchemeWeeks()
                                on s.Id equals w.Id into sw
                                select new JoinedViews // this line
                                {
    
                                    SchemeBk = s,
                                    SchemeWk = sw
                                };

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 10, 2018 12:54 PM
  • User-893317190 posted

    Hi tmatiwure,

    I use my model to make a test and it works well . It seems the error is caused be your database , are you sure that you have WeekId and WeekEnding column in your database?

    My model

    public partial class Category
        {
            public int CategoryID { get; set; }
    
            [Required]
            [StringLength(15)]
            public string CategoryName { get; set; }
    
            [Column(TypeName = "ntext")]
            public string Description { get; set; }
    
            
        }
    
     public partial class Product
        {
            public int ProductID { get; set; }
    
            [Required]
            [StringLength(40)]
            public string ProductName { get; set; }
    
    
            public int? CategoryID { get; set; }
            
        }

    My dbContext.

     public partial class EntityDb1 : DbContext
        {
            public EntityDb1()
                : base("name=EntityDb1")
            {
            }
    
            public virtual DbSet<Product> Products { get; set; }
            public virtual DbSet<Category> Categories { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Product>()
                    .Property(e => e.UnitPrice)
                    .HasPrecision(19, 4);
            }
        }

    My joinView.

     public class JoinView
        {
    
            public Product product { get; set; }
            public Category category { get; set; }
        }

    My controller.

     public ActionResult Join()
            {
                EntityDb1 db1 = new EntityDb1();
                EntityDb1 db2 = new EntityDb1();
                IEnumerable<Product> products = db1.Products;
                IEnumerable<Category> categories = db2.Categories;
    
                var result = from pro in products
                             join ca in categories   
                             on pro.CategoryID equals ca.CategoryID    
    
                             select
                             new JoinView
                             {
                                 product = pro,
                                 category=ca
                             };
               
                
                return View(result);
            }

    My view.

    @model IEnumerable<MVCLearning.Models.JoinView>
    @{
        ViewBag.Title = "Join";
    }
    
    <h2>Join</h2>
    
    
    <table class="table table-striped">
        <tr>
            <td>ProductID </td>
            <td>ProductName</td>
            <td>CategoryID</td>
            <td>CategoryName</td>
        </tr>
        @foreach (var item in Model)
        {
            <tr>
                <td>@item.product.ProductID</td>
                <td>@item.product.ProductName</td>
                <td>@item.category.CategoryID</td>
                <td>@item.category.CategoryName</td>
            </tr>
        }
    </table>
      

    And the result.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 11, 2018 5:48 AM

All replies

  • User-369506445 posted

    hi

     Compiler Error Message: CS0234: The type or namespace name 'JoinedViews' does not exist in the namespace 'CollegeSys.Domain.Entities' (are you missing an assembly reference?)

    the above error related to below <g class="gr_ gr_22 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="22" data-gr-id="22">line</g>

    @model IEnumerable<CollegeSys.Domain.Entities.JoinedViews>

    the JoinedViews entity is not in <g class="gr_ gr_61 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="61" data-gr-id="61">CollegeSys</g>.Domain.Entities namespace

    Wednesday, October 10, 2018 7:58 AM
  • User-164452226 posted

    I just don't get it. Now it's giving the following error

    The model item passed into the dictionary is of type 'System.Linq.Enumerable+<GroupJoinIterator>d__41`4[CollegeSys.Domain.Entities.SchemeBook,CollegeSys.Domain.Entities.SchemeWeek,System.Nullable`1[System.Int64],<>f__AnonymousType1`2[CollegeSys.Domain.Entities.SchemeBook,System.Collections.Generic.IEnumerable`1[CollegeSys.Domain.Entities.SchemeWeek]]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[CollegeSys.Domain.Entities.JoinedViews]'

    Wednesday, October 10, 2018 12:33 PM
  • User-369506445 posted

    in the controller, your query <g class="gr_ gr_6 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="6" data-gr-id="6">return</g> an anonymous Type  and in the <g class="gr_ gr_7 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="7" data-gr-id="7">View</g> you set <g class="gr_ gr_4 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="4" data-gr-id="4">a </g><g class="gr_ gr_4 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Grammar multiReplace" id="4" data-gr-id="4">IEnumerable</g><JoinedViews>

    you have to define you type in your query

    var SchemeWks = from s in SBrepository.SchemeBooks()
                                join w in SWrepository.SchemeWeeks()
                                on s.Id equals w.Id into sw
                                select new JoinedViews // this line
                                {
    
                                    SchemeBk = s,
                                    SchemeWk = sw
                                };

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 10, 2018 12:54 PM
  • User-369506445 posted

    I think you want to do this

    you want to join  your tables and show them in your view 

    first, change your JoinedViews class to below

    change

    public class JoinedViews
        {
            public SchemeBook SchemeBk { get; set; }
            public SchemeWeek SchemeWk { get; set; }
        }

    to

    public class JoinedViews : SchemeBook
        {
            
            public Int64 WeekID { get; set; }
            public string WeekEnding { get; set; }
            public Int64 Userid { get; set; }
    
    
        }

    now in your controller

    public ActionResult Index()
            {
                EFSchemeBookRepository SBrepository = new EFSchemeBookRepository();
                EFSchemeWeekRepository SWrepository = new EFSchemeWeekRepository();
    
                 
                var SchemeWks = from s in SBrepository.SchemeBooks()
                                join w in SWrepository.SchemeWeeks()
                                on s.Id equals w.Id  
                                select new JoinedViews// this line
                                {
    
                                    Id=s.Id,
                                    StudentID="",
                                    SchemeID=s.SchemeID,
                                    Subject=s.Subject,
                                    Grade=s.Grade,
                                    SchoolYear=s.SchoolYear,
                                    SchoolTerm=s.SchoolTerm,
    
                                    WeekID =w.WeekID,
                                    WeekEnding=w.WeekEnding
    
    
                                };
                
                return View(SchemeWks);
                
            }

    now in your view

    @model IEnumerable<CollegeSys.Domain.Entities.JoinedViews>
    
    @{
        ViewBag.Title = "GetSchemeBookWeeks";
        Layout = "~/Views/Shared/_SchemeBookNav.cshtml";
    }
    <table class="table table-striped">
        <tr>
            <td>Student ID</td>
            <td>Scheme ID</td>
            <td>WeekID</td>
            <td>Weekending</td>
        </tr>
        @foreach (var item in Model)
        {
            <tr>
                <td>@item.StudentID</td>
                <td>@item.Id</td>
                <td>@item.WeekID</td>
                <td>@item.WeekEnding</td>
            </tr>
        }
    </table>

    I hope it can be  helpful

    Wednesday, October 10, 2018 1:13 PM
  • User-164452226 posted

    I think you want to do this

    you want to join  your tables and show them in your view 

    first, change your JoinedViews class to below

    change

    public class JoinedViews
        {
            public SchemeBook SchemeBk { get; set; }
            public SchemeWeek SchemeWk { get; set; }
        }

    to

    public class JoinedViews : SchemeBook
        {
            
            public Int64 WeekID { get; set; }
            public string WeekEnding { get; set; }
            public Int64 Userid { get; set; }
    
    
        }

    now in your controller

    public ActionResult Index()
            {
                EFSchemeBookRepository SBrepository = new EFSchemeBookRepository();
                EFSchemeWeekRepository SWrepository = new EFSchemeWeekRepository();
    
                 
                var SchemeWks = from s in SBrepository.SchemeBooks()
                                join w in SWrepository.SchemeWeeks()
                                on s.Id equals w.Id  
                                select new JoinedViews// this line
                                {
    
                                    Id=s.Id,
                                    StudentID="",
                                    SchemeID=s.SchemeID,
                                    Subject=s.Subject,
                                    Grade=s.Grade,
                                    SchoolYear=s.SchoolYear,
                                    SchoolTerm=s.SchoolTerm,
    
                                    WeekID =w.WeekID,
                                    WeekEnding=w.WeekEnding
    
    
                                };
                
                return View(SchemeWks);
                
            }

    now in your view

    @model IEnumerable<CollegeSys.Domain.Entities.JoinedViews>
    
    @{
        ViewBag.Title = "GetSchemeBookWeeks";
        Layout = "~/Views/Shared/_SchemeBookNav.cshtml";
    }
    <table class="table table-striped">
        <tr>
            <td>Student ID</td>
            <td>Scheme ID</td>
            <td>WeekID</td>
            <td>Weekending</td>
        </tr>
        @foreach (var item in Model)
        {
            <tr>
                <td>@item.StudentID</td>
                <td>@item.Id</td>
                <td>@item.WeekID</td>
                <td>@item.WeekEnding</td>
            </tr>
        }
    </table>

    I hope it can be  helpful

    I did just as you instructed, now it gives the following error

    Server Error in '/' Application.
    
    Invalid column name 'Discriminator'.
    Invalid column name 'Discriminator'.
    Invalid column name 'Discriminator'.
    Invalid column name 'WeekID'.
    Invalid column name 'WeekEnding'. 
      Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
    
     Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'Discriminator'.
    Invalid column name 'Discriminator'.
    Invalid column name 'Discriminator'.
    Invalid column name 'WeekID'.
    Invalid column name 'WeekEnding'.
    
    Source Error: 
    
    
    
    Line 14: 
    Line 15:     </tr>
    Line 16:     @foreach (var item in Model)
    Line 17:     {
    Line 18:     <tr>
      
    
     Source File:  C:\Users\TMATIWURE\source\repos\CollegeSys\CollegeSys.WebUI\Views\LessonPlan\GetSchemeBookWeeks.cshtml    Line:  16 
    
    Stack Trace: 
    
    
    
    [SqlException (0x80131904): Invalid column name 'Discriminator'.
    Invalid column name 'Discriminator'.
    Invalid column name 'Discriminator'.
    Invalid column name 'WeekID'.
    Invalid column name 'WeekEnding'.]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +2555722
       System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5958412
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +285
       System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4169
       System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +58
       System.Data.SqlClient.SqlDataReader.get_MetaData() +89
       System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) +430
       System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +2598
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +1483
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +64
       System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +240
       System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41
       System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +12
       System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c) +9
       System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) +72
       System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) +356
       System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior) +166
       System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +12
       System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +37
    
    [EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
       System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +112
       System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute(ObjectContext context, ObjectParameterCollection parameterValues) +744
       System.Data.Entity.Core.Objects.<>c__DisplayClass7.<GetResults>b__6() +97
       System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction(Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) +288
       System.Data.Entity.Core.Objects.<>c__DisplayClass7.<GetResults>b__5() +154
       System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func`1 operation) +189
       System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +279
       System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0() +11
       System.Data.Entity.Internal.LazyEnumerator`1.MoveNext() +45
       System.Linq.<JoinIterator>d__38`4.MoveNext() +178
       ASP._Page_Views_LessonPlan_GetSchemeBookWeeks_cshtml.Execute() in C:\Users\TMATIWURE\source\repos\CollegeSys\CollegeSys.WebUI\Views\LessonPlan\GetSchemeBookWeeks.cshtml:16
       System.Web.WebPages.WebPageBase.ExecutePageHierarchy() +198
       System.Web.Mvc.WebViewPage.ExecutePageHierarchy() +105
       System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage) +78
       System.Web.Mvc.RazorView.RenderView(ViewContext viewContext, TextWriter writer, Object instance) +235
       System.Web.Mvc.BuildManagerCompiledView.Render(ViewContext viewContext, TextWriter writer) +107
       System.Web.Mvc.ViewResultBase.ExecuteResult(ControllerContext context) +291
       System.Web.Mvc.ControllerActionInvoker.InvokeActionResult(ControllerContext controllerContext, ActionResult actionResult) +13
       System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList`1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult) +56
       System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList`1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult) +420
       System.Web.Mvc.ControllerActionInvoker.InvokeActionResultWithFilters(ControllerContext controllerContext, IList`1 filters, ActionResult actionResult) +52
       System.Web.Mvc.Async.<>c__DisplayClass2b.<BeginInvokeAction>b__1c() +173
       System.Web.Mvc.Async.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult) +100
       System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
       System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
       System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +27
       System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) +13
       System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +29
       System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
       System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +36
       System.Web.Mvc.Controller.<BeginExecute>b__15(IAsyncResult asyncResult, Controller controller) +12
       System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +22
       System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
       System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +26
       System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
       System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__5(IAsyncResult asyncResult, ProcessRequestState innerState) +21
       System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +29
       System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
       System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +28
       System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
       System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9873789
       System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +48
       System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +159
    
      
    
    
    Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.7.3160.0 

    Wednesday, October 10, 2018 1:41 PM
  • User-369506445 posted

    The error is about your fields, my code shows you how to do it, but you need change it based on your classes and fields

    For example you need add or remove your fields that you define them in your classes

    select new JoinedViews// this line
                                {
    
                                    Id=s.Id,
                                    StudentID="",
                                    SchemeID=s.SchemeID,
                                    Subject=s.Subject,
                                    Grade=s.Grade,
                                    SchoolYear=s.SchoolYear,
                                    SchoolTerm=s.SchoolTerm,
    
                                    WeekID =w.WeekID,
                                    WeekEnding=w.WeekEnding
    
    
                                };
                

    Please change them and if you still have problem , please put here yor complete code here such as classes and quries

    Wednesday, October 10, 2018 3:26 PM
  • User-893317190 posted

    Hi tmatiwure,

    I use my model to make a test and it works well . It seems the error is caused be your database , are you sure that you have WeekId and WeekEnding column in your database?

    My model

    public partial class Category
        {
            public int CategoryID { get; set; }
    
            [Required]
            [StringLength(15)]
            public string CategoryName { get; set; }
    
            [Column(TypeName = "ntext")]
            public string Description { get; set; }
    
            
        }
    
     public partial class Product
        {
            public int ProductID { get; set; }
    
            [Required]
            [StringLength(40)]
            public string ProductName { get; set; }
    
    
            public int? CategoryID { get; set; }
            
        }

    My dbContext.

     public partial class EntityDb1 : DbContext
        {
            public EntityDb1()
                : base("name=EntityDb1")
            {
            }
    
            public virtual DbSet<Product> Products { get; set; }
            public virtual DbSet<Category> Categories { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Product>()
                    .Property(e => e.UnitPrice)
                    .HasPrecision(19, 4);
            }
        }

    My joinView.

     public class JoinView
        {
    
            public Product product { get; set; }
            public Category category { get; set; }
        }

    My controller.

     public ActionResult Join()
            {
                EntityDb1 db1 = new EntityDb1();
                EntityDb1 db2 = new EntityDb1();
                IEnumerable<Product> products = db1.Products;
                IEnumerable<Category> categories = db2.Categories;
    
                var result = from pro in products
                             join ca in categories   
                             on pro.CategoryID equals ca.CategoryID    
    
                             select
                             new JoinView
                             {
                                 product = pro,
                                 category=ca
                             };
               
                
                return View(result);
            }

    My view.

    @model IEnumerable<MVCLearning.Models.JoinView>
    @{
        ViewBag.Title = "Join";
    }
    
    <h2>Join</h2>
    
    
    <table class="table table-striped">
        <tr>
            <td>ProductID </td>
            <td>ProductName</td>
            <td>CategoryID</td>
            <td>CategoryName</td>
        </tr>
        @foreach (var item in Model)
        {
            <tr>
                <td>@item.product.ProductID</td>
                <td>@item.product.ProductName</td>
                <td>@item.category.CategoryID</td>
                <td>@item.category.CategoryName</td>
            </tr>
        }
    </table>
      

    And the result.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 11, 2018 5:48 AM
  • User-164452226 posted

    l had to work with this. Thank you so much

    Thursday, October 11, 2018 6:29 AM
  • User-164452226 posted

    Hi tmatiwure,

    I use my model to make a test and it works well . It seems the error is caused be your database , are you sure that you have WeekId and WeekEnding column in your database?

    My model

    public partial class Category
        {
            public int CategoryID { get; set; }
    
            [Required]
            [StringLength(15)]
            public string CategoryName { get; set; }
    
            [Column(TypeName = "ntext")]
            public string Description { get; set; }
    
            
        }
    
     public partial class Product
        {
            public int ProductID { get; set; }
    
            [Required]
            [StringLength(40)]
            public string ProductName { get; set; }
    
    
            public int? CategoryID { get; set; }
            
        }

    My dbContext.

     public partial class EntityDb1 : DbContext
        {
            public EntityDb1()
                : base("name=EntityDb1")
            {
            }
    
            public virtual DbSet<Product> Products { get; set; }
            public virtual DbSet<Category> Categories { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Product>()
                    .Property(e => e.UnitPrice)
                    .HasPrecision(19, 4);
            }
        }

    My joinView.

     public class JoinView
        {
    
            public Product product { get; set; }
            public Category category { get; set; }
        }

    My controller.

     public ActionResult Join()
            {
                EntityDb1 db1 = new EntityDb1();
                EntityDb1 db2 = new EntityDb1();
                IEnumerable<Product> products = db1.Products;
                IEnumerable<Category> categories = db2.Categories;
    
                var result = from pro in products
                             join ca in categories   
                             on pro.CategoryID equals ca.CategoryID    
    
                             select
                             new JoinView
                             {
                                 product = pro,
                                 category=ca
                             };
               
                
                return View(result);
            }

    My view.

    @model IEnumerable<MVCLearning.Models.JoinView>
    @{
        ViewBag.Title = "Join";
    }
    
    <h2>Join</h2>
    
    
    <table class="table table-striped">
        <tr>
            <td>ProductID </td>
            <td>ProductName</td>
            <td>CategoryID</td>
            <td>CategoryName</td>
        </tr>
        @foreach (var item in Model)
        {
            <tr>
                <td>@item.product.ProductID</td>
                <td>@item.product.ProductName</td>
                <td>@item.category.CategoryID</td>
                <td>@item.category.CategoryName</td>
            </tr>
        }
    </table>
      

    And the result.

    Best regards,

    Ackerly Xu

    This was a great eye opener for me. l simply hat to combine this with the above suggestions and it yielded results

    Thursday, October 11, 2018 6:30 AM