DataGrid for DB objects with multiples FK

Oct 5, 2012 at 10:16 PM

Hello,

First, thanks for creating this controls.

Environment:

  • Newbie with: MVC 4 + Razor and EntityFramework with DBContext

Scenario:

I have 1 table (DetailTask) which stores the Task associated with a Project with following columns:

  • 3 are simple: description, hours and cost
  • Others are fk to other tables like Tasks, Users, Project

I need to show in the DataGrid the values of the other tables also allow to insert and edit, for the last 2 I want to display DropDownList.

I'm having troubles to get the datagrid to work, I'm not quiete sure how to fill the List of items for the DataGrid, because the Tracker only accept all items from the same type but my items are an inner join of tables. description, hours, cost, taskTable, userTable and projectTable.

Thanks in advance for your help! 

Coordinator
Oct 6, 2012 at 6:29 PM

What you get from the joins you do is an object to. .Net creates an anonymous class with properties taken from all tables. You can the list of objects of this anonymous class to fill a list of tracker objects. However this is not adviced, if you need to get back on the controller the edited list, since in this case you dont know wich type to put in the receiving controller. It is better to use the select clause to fill a class you defined with the properties from different table:

var items (your query).Select(m => new MyClass{property1= m.propertyq,....}).ToList()

then you use the items obtained this way to populate a list of tracker objects.

 

Anyway if you wouldlike to use dropdowns for the connection with the other table I think yo dont need any join, since the value passed to the dropdown is just the external key that is contained already in the main table. Then you pass to each dropdown the list of valuename/key taken from the other two tables.

 

Give a look to all code examples containing the datagrid. For example the basic examples contained in BinariesWithSimpleExamples contains also dropdowns. 

Oct 7, 2012 at 2:27 PM

Hi Frank,

Thanks for your prompt answer.

I already could fix the problem with the joins, I'm using .Include .ToList() and now I'm not getting the error, but when I try to execute, I receive a new exception: "The ObjectContext instance has been disposed and can no longer be used for operations that require a connection." on the Html.DataGridFor method on the view.

I also looked the Simple Examples for using the DropDowns but for the moment I want to make the DataGrid work only with TextBoxes.

I'm using DBContext with Lazy Load enable instead of ObjectContext to access my DB.

The ViewModel GetPage method is:

public static List<Tracker<tblinvdetailsitem>> GetItemsPage(int pageDim, out int totalPages, ref List<KeyValuePair<LambdaExpression, OrderType>> order, int page = 1)
        {
            List<Tracker<tblinvdetailsitem>> result;
            if (order == null)
            {
                order = new List<KeyValuePair<LambdaExpression, OrderType>>();
            }
            if (order.Count == 0)//paging require ordering! Therefore we always need to add a default oredering
            {
                Expression<Func<tblinvdetailsitem, DateTime>> defaultOrder = m => m.indi_Created;

                order.Add(new KeyValuePair<LambdaExpression, OrderType>(defaultOrder, OrderType.Descending));
            }

            //temporally create a context object
            using (eInvoicingEntities dbContext = new eInvoicingEntities())
            {
                int rowCount = dbContext.tblinvdetailsitems.Count();
                
                if (rowCount == 0)
                {
                    totalPages = 0;
                    return new List<Tracker<tblinvdetailsitem>>();
                }
                totalPages = rowCount / pageDim;
                if (rowCount % pageDim > 0) totalPages++;
                if (page > totalPages) page = totalPages;
                if (page < 1) page = 1;
                int toSkip = (page - 1) * pageDim;

                //go to index and use that script here
                //get the item list
                var Items = dbContext.tblinvdetailsitems
                    .Include(t => t.tblcompany)
                    .Include(t => t.tblinvoice)
                    .Include(t => t.tblitem)
                    .Include(t => t.tbltax)
                    .Include(t => t.tbltax1)
                    .Include(t => t.tbluser)
                    .Include(t => t.tbluser1);

                //wrap the list objects with trackers
                result = Items.ToList().ApplyOrder(order).Select(viewItem =>
                    new Tracker<tblinvdetailsitem> { Value = viewItem, OldValue = viewItem, Changed = false }
                    ).Skip(toSkip).Take(pageDim).ToList();
            }
            return result;
        }//GetItemsPage

 

The Controller Index Action is:

public const int PageDim = 5;//in actual application this should be put in a config file
        public ActionResult Index()
        {
            int totalPages;
            List<KeyValuePair<LambdaExpression, OrderType>> order = null;
            
            InvDetailsItemViewModel result = new InvDetailsItemViewModel()
            {
                ItemsList = InvDetailsItemViewModel.GetItemsPage(PageDim, out totalPages, ref order),
                TotalPages = totalPages,
                CurrPage = 1,
                PrevPage = 1,
                ItemOrder = order
            };
            return View(result);
        }//index get

 

The auto-generated class from the table is:

public partial class tblinvdetailsitem
    {
        public System.Guid pk_Indi_InvDetailItemId { get; set; }
        public string indi_InvDetailItemDescription { get; set; }
        public decimal indi_InvDetailItemUnitCost { get; set; }
        public decimal indi_InvDetailItemQuantity { get; set; }
        public decimal indi_InvDetailItemLineTotal { get; set; }
        public System.DateTime indi_Created { get; set; }
        public Nullable<System.DateTime> indi_Modified { get; set; }
        public System.Guid fk_IndiInvoiceId_InvInvoiceId { get; set; }
        public System.Guid fk_IndiCreatorId_UsrUserId { get; set; }
        public System.Guid fk_IndiItemId_ItmItemId { get; set; }
        public Nullable<System.Guid> fk_IndiModifierId_UsrUserId { get; set; }
        public System.Guid fk_IndiCompanyId_CoCompanyId { get; set; }
        public Nullable<System.Guid> fk_Indi_InvDetailItemLineTax1 { get; set; }
        public Nullable<System.Guid> fk_Indi_InvDetailItemLineTax2 { get; set; }
    
        public virtual tblcompany tblcompany { get; set; }
        public virtual tblinvoice tblinvoice { get; set; }
        public virtual tblitem tblitem { get; set; }
        public virtual tbltax tbltax { get; set; }
        public virtual tbltax tbltax1 { get; set; }
        public virtual tbluser tbluser { get; set; }
        public virtual tbluser tbluser1 { get; set; }
    }

 

And the view where I get the error is:

<div>
        <div>
            @Html.DataGridFor(model => model.ItemsList, ItemContainerType.tr,
            _S.H<tblinvdetailsitem>(
            @<text>
                <td class="editor-field">
                    @item.ValidationMessageFor(model => model.tblitem.itm_ItemName, "*")
                    @item.TextBoxFor(model => model.tblitem.itm_ItemName)
                </td>
                <td class="editor-field">
                    @item.ValidationMessageFor(model => model.indi_InvDetailItemDescription, "*")
                    @item.TextBoxFor(model => model.indi_InvDetailItemDescription)
                </td>
                <td class="editor-field">
                    @item.ValidationMessageFor(model => model.indi_InvDetailItemUnitCost, "*")
                    @item.TextBoxFor(model => model.indi_InvDetailItemUnitCost)
                </td>
                <td class="editor-field">
                    @item.ValidationMessageFor(model => model.indi_InvDetailItemQuantity, "*")
                    @item.TextBoxFor(model => model.indi_InvDetailItemQuantity)
                </td>
                <td class="editor-field">
                    @item.ValidationMessageFor(model => model.fk_Indi_InvDetailItemLineTax1)
                    @item.TextBoxFor(model => model.tbltax.tax_TaxName)
                </td>
                <td class="editor-field">
                    @item.ValidationMessageFor(model => model.fk_Indi_InvDetailItemLineTax2)
                    @item.TextBoxFor(model => model.tbltax1.tax_TaxName)
                </td>
                <td class="editor-field">
                    @item.ValidationMessageFor(model => model.indi_InvDetailItemLineTotal)
                    @item.DisplayField(model => model.indi_InvDetailItemLineTotal)
                </td>
                <td class="ToDoTool" colspan="2">
                    @item.ImgDataButton(DataButtonType.Cancel, "../../Content/undo.jpg", null)
                    @item.HiddenFor(model => model.pk_Indi_InvDetailItemId)
                    @item.HiddenFor(model => model.indi_Created)
                    @item.HiddenFor(model => model.fk_IndiInvoiceId_InvInvoiceId)
                    @item.HiddenFor(model => model.fk_IndiCreatorId_UsrUserId)
                    @item.HiddenFor(model => model.fk_IndiModifierId_UsrUserId)
                    @item.HiddenFor(model => model.fk_IndiCompanyId_CoCompanyId)
                </td>
            </text>
            ),
             _S.H<tblinvdetailsitem>(
            @<text>
                <td class="display-label">
                    @item.ValidationMessageFor(model => model.tblitem.itm_ItemName, "*")
                    @item.DisplayField(model => model.tblitem.itm_ItemName)
                </td>
                <td class="display-label">
                    @item.ValidationMessageFor(model => model.indi_InvDetailItemDescription, "*")
                    @item.DisplayField(model => model.indi_InvDetailItemDescription)
                </td>
                <td class="display-label">
                    @item.ValidationMessageFor(model => model.indi_InvDetailItemUnitCost, "*")
                    @item.DisplayField(model => model.indi_InvDetailItemUnitCost)
                </td>
                <td class="display-label">
                    @item.ValidationMessageFor(model => model.indi_InvDetailItemQuantity, "*")
                    @item.DisplayField(model => model.indi_InvDetailItemQuantity)
                </td>
                <td class="display-label">
                    @item.ValidationMessageFor(model => model.tbltax.tax_TaxName)
                    @item.DisplayField(model => model.tbltax.tax_TaxName)
                </td>
                <td class="display-label">
                    @item.ValidationMessageFor(model => model.tbltax1.tax_TaxName)
                    @item.DisplayField(model => model.tbltax1.tax_TaxName)
                </td>
                <td class="display-label">
                    @item.ValidationMessageFor(model => model.indi_InvDetailItemLineTotal)
                    @item.DisplayField(model => model.indi_InvDetailItemLineTotal)
                </td>
                <td class="">
                    @item.DetailLink(Ajax, "Editar Articulo", DetailType.Edit, "Edit", "Items",
                        new { id = item.ViewData.Model.fk_IndiItemId_ItmItemId }, null)
                    @item.ImgDataButton(DataButtonType.Edit, "../../Images/treeDots-128.png", null)
                </td>
                <td class="">
                    @item.ImgDataButton(DataButtonType.Delete, "../../Images/delete-128.png", null)
                    @item.HiddenFor(model => model.pk_Indi_InvDetailItemId)
                    @item.HiddenFor(model => model.indi_Created)
                    @item.HiddenFor(model => model.fk_IndiInvoiceId_InvInvoiceId)
                    @item.HiddenFor(model => model.fk_IndiCreatorId_UsrUserId)
                    @item.HiddenFor(model => model.fk_IndiModifierId_UsrUserId)
                    @item.HiddenFor(model => model.fk_IndiCompanyId_CoCompanyId)
                </td>
            </text>
            ),
            _S.H<tblinvdetailsitem>(
              @<table class="">
                  <tr>
                      <td class="ToDoHeader"><strong>@*@item.SortButtonFor(model => model.tblitem.itm_ItemName, sortButtonStyle: SortButtonStyle.Button)*@</strong></td>
                      <td class="ToDoHeader"><strong>@*@item.SortButtonFor(model => model.indi_InvDetailItemDescription, sortButtonStyle: SortButtonStyle.Button)*@</strong></td>
                      <td class="ToDoHeader"><strong>@item.ColumnNameFor(model => model.indi_InvDetailItemUnitCost)</strong></td>
                      <td class="ToDoHeader"><strong></strong></td>
                      <td class="ToDoHeader"><strong></strong></td>
                      <td class="ToDoHeader"><strong></strong></td>
                      <td class="ToDoHeader"><strong></strong></td>
                      <td class="ToDoHeader"><strong></strong></td>
                      <td class="ToDoHeader"><strong></strong></td>
                  </tr>
                  @item.ViewData["Content"]
              </table>
             ),
             _S.H<tblinvdetailsitem>(
               @<td colspan="5" class="ToDo">@item.ImgDataButton(DataButtonType.Insert, "../../Images/new-128.png", null)</td>
             ))
        </div>

        <div class="ToDoPager">
            @{ var pager = Html.PagerFor(m => m.CurrPage, m => m.PrevPage, m => m.TotalPages);}
            @pager.PageButton("<<", PageButtonType.First, PageButtonStyle.Link)
            @pager.PageButton("<", PageButtonType.Previous, PageButtonStyle.Link)
            @pager.PageChoice(5)
            @pager.PageButton(">", PageButtonType.Next, PageButtonStyle.Link)
            @pager.PageButton(">>", PageButtonType.Last, PageButtonStyle.Link)
            @pager.PageButton("Go To", PageButtonType.GoTo, PageButtonStyle.Button)
            @pager.GoToText(new { style = "width:50px;" })
        </div>

        <input type="submit" value="Save" />
        
        @Html.HiddenFor(m => m.TotalPages)
    </div>

Thanks in advance for your help.

Oct 9, 2012 at 12:00 AM

Hi Frank,

I follow your advice about defining a new class (like ToDoView) with the properties I need, now I got the DataGrid to work.

Thanks for all your help and for the controls.

I'm looking forward to define a theme for my DataGrids like you use in the examples.

BR

Coordinator
Oct 9, 2012 at 4:28 AM

Lazy loading is risky on the web, because the loading maybe triggered after the context has been disposed, since in the web request are shortlived. As a rule of thumb, load all that you need before passing a data structure to a View. This means calling ToList to any IQueryable, and loading all connected children you need.

 

IQueryables can be used up to the controller, but just alittle before being passed to the View they must be converted in usual data structures. The only exception are the Api Controllers methods decorated with the [Queryable] attribute because the Queryable attribute do itself the job of converting the IQueryable after it applies the query containedd in the query string