Telerik Forums / Kendo UI for jQuery
This is a migrated thread and some comments may be shown as answers.

Client Side Binding with Foreign Key Columns

10 Answers 726 Views
Grid
This is a migrated thread and some comments may be shown as answers.
andrew
Top achievements
Rank 1
andrew asked on 24 Jul 2012, 06:16 AM
Hi,

I am trying to find out how to do client side binding on foreign keys, all the examples I have come across are for server side binding and also I have seen no reference to how to deal with different model scenarios or what your controller actions should look like.

I have been stuck about a week now and basically I have a grid that is showing permissions that references Users, Payrolls and Functions. The idea is that the grid will display the User.NetLogin, Payroll.Title and Function.Title on the grid and in the dropdowns. This is working fine, however the way I passed the data to my model as you will see below I don't think is correct and the main issue is the code controlling the create, edit, delete and save (as this uses batch edit) features. Currently only read works, all the data manipulation fails and this is what I need to get working.

The permissions model has 3 foreign keys and these are refenced in the way the MVC Student Enrolement tutorial described the enrolement model. So on the permissions table the ID of the 3 reference tables are held as well as virtual object:

e.g. public int UserID {get;set}
       public virtual User User {get;set;}

On the 3 reference tables a reciprical link is held to the permissions table using:

public virtual ICollection<Permission> Permissions

I don't quite understand why this structure is used perhaps someone could exaplain? But this structure was from a tutorial on the MVC site and it worked perfectly what I have and Index view and 4 views for CRUD actions. The examples I have seen for foreign keys so far only show models with the public User User {get;set;} in them.

I am using entity framework so my permissions model is like

public class User
{
    public int UserID { get; set; }
    public string NetLogin { get; set; }
    [Required(ErrorMessage = "Employee number is required.")]
    [Display(Name = "Employee Number")]
    [MaxLength(6)]
    public string EmpNumber { get; set; }
    [Required(ErrorMessage = "First name is required.")]
    [Display(Name = "First Name")]
    [MaxLength(50)]
    public string Forename { get; set; }
    [Required(ErrorMessage = "Last name is required.")]
    [Display(Name = "Last Name")]
    [MaxLength(50)]
    public string Surname { get; set; }
    public bool Active { get; set; }
    public virtual ICollection<Permission> Permissions { get; set; }
}
 
public class Payroll
{
    public int PayrollID { get; set; }
    [Required(ErrorMessage = "Payroll title is required.")]
    [Display(Name = "Payroll")]
    [MaxLength(20)]
    public string Title { get; set; }
    [Required(ErrorMessage = "C21 description is required.")]
    [Display(Name = "C21 Name")]
    [MaxLength(30)]
    public string C21Name { get; set; }
    [Required(ErrorMessage = "Server name is required.")]
    [Display(Name = "Server Name")]
    [MaxLength(20)]
    public string Server { get; set; }
    [Required(ErrorMessage = "Port is required.")]
    [Display(Name = "Port")]
    [MaxLength(5)]
    public string Port { get; set; }
    [Required(ErrorMessage = "Connection string is required.")]
    [Display(Name = "Connection String")]
    [MaxLength(200)]
    public string Connection { get; set; }
    public virtual ICollection<Permission> Permissions { get; set; }
}
 
public class Function
{
    public int FunctionID { get; set; }
    [Required(ErrorMessage = "Function description is required.")]
    [Display(Name = "Function")]
    [MaxLength(20)]
    public string Title { get; set; }
    public virtual ICollection<Permission> Permissions { get; set; }
}
 
public class Permission
{
    public int PermissionID { get; set; }
    [Required(ErrorMessage = "You must select a payroll.")]
    public int PayrollID { get; set; }
    [Required(ErrorMessage = "You must select a function.")]
    public int FunctionID { get; set; }
    [Required(ErrorMessage = "You must select a user.")]
    public int UserID { get; set; }
    [Range(typeof(double), "1", "5")]
    [Required(ErrorMessage = "You must enter an access level.")]
    public double? Level { get; set; }
    public virtual Payroll Payroll { get; set; }
    public virtual Function Function { get; set; }
    public virtual User User { get; set; }
}


I have managed to get the read and custom dropdown editors working, but I can't find any code examples for the controller or the view using foreign keys like this and entity framework with kendo grid.

Currently the add action creates a javascript runtime error of 'NetLogin' is undefined. (NetLogin is a child entity of the User Object)

The save action for any delete and edit (add add) action litterally does nothing, I have traced it with firebug and it creates no errors and no actions and my knowledge of jquery is minimal to non so I couldn't trace it back.

Here is my controller:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using PayPlate.Models;
using PayPlate.DAL;
using PagedList;
 
namespace PayPlate.Controllers
{
    public class PermissionController : Controller
    {
        UnitOfWork unitOfWork = new UnitOfWork();
 
        public ActionResult Index()
        {
            return View();
        }
 
        /// <summary>
        /// Creates new products by inserting the data posted by the Kendo Grid in the database.
        /// </summary>
        /// <param name="products">The products created by the user.</param>
        /// <returns>The inserted products so the Kendo Grid is aware of the database generated ProductID</returns>
        [HttpPost]
        public ActionResult Create(IEnumerable<Permission> perms)
        {
            var result = new List<Permission>();
 
            //Iterate all created products which are posted by the Kendo Grid
            foreach (var permission in perms)
            {
                var perm = new Permission
                {
                    UserID = permission.UserID,
                    PayrollID = permission.PayrollID,
                    FunctionID = permission.FunctionID,
                    Level = permission.Level                 
                };
 
                // store the product in the result
                result.Add(perm);
 
                // Add the entity
                unitOfWork.PermissionRepository.Insert(perm);
            }
 
            // Insert all created products to the database
            unitOfWork.Save();
 
            // Return the inserted products - the Kendo Grid needs their ProductID which is generated by SQL server during insertion
            return Json(result.Select(r => new { PermissionID = r.PermissionID, UserID = r.UserID, PayrollID = r.PayrollID, FunctionID = r.FunctionID, Level = r.Level, NetLogin = r.User.NetLogin, PayrollTitle = r.Payroll.Title, FunctionTitle = r.Function.Title }));
             
            /*
            return Json(result.Select(p => new Permission
            {
                PermissionID = p.PermissionID,
                UserID = p.UserID,
                PayrollID = p.PayrollID,
                FunctionID = p.FunctionID,
                Level = p.Level 
            })
            .ToList());
             */
        }
 
        /// <summary>
        /// Reads the available products to provide data for the Kendo Grid
        /// </summary>
        /// <returns>All available products as JSON</returns>
        [HttpPost]
        public ActionResult Read()
        {
 
            var perms2 = unitOfWork.PermissionRepository.Get()
                .Select(r => new { PermissionID = r.PermissionID,
                    UserID = r.UserID, PayrollID = r.PayrollID,
                    FunctionID = r.FunctionID, Level = r.Level,
                    NetLogin = r.User.NetLogin,
                    PayrollTitle = r.Payroll.Title,
                    FunctionTitle = r.Function.Title });
             
            var perms1 = unitOfWork.PermissionRepository.Get()
                // Use a view model to avoid serializing internal Entity Framework properties as JSON
                .Select(p => new Permission
                {
                    PermissionID = p.PermissionID,
                    UserID = p.UserID,
                    PayrollID = p.PayrollID,
                    FunctionID = p.FunctionID,
                    Level = p.Level
                }).ToList();
            
 
            return Json(perms2);
        }
 
        /// <summary>
        /// Updates existing products by updating the database with the data posted by the Kendo Grid.
        /// </summary>
        /// <param name="products">The products updated by the user</param>
        [HttpPost]
        public ActionResult Update(IEnumerable<Permission> perms)
        {
            //Iterate all created products which are posted by the Kendo Grid
            foreach (var permission in perms)
            {
                // Attach the entity
                unitOfWork.PermissionRepository.Update(permission);
            }
 
            // Save all updated products to the database
            unitOfWork.Save();
 
            //Return emtpy result
            return Json(null);
        }
 
        /// <summary>
        /// Destroys existing products by deleting them from the database.
        /// </summary>
        /// <param name="products">The products deleted by the user</param>
        [HttpPost]
        public ActionResult Destroy(IEnumerable<Permission> perms)
        {
            //Iterate all destroyed products which are posted by the Kendo Grid
            foreach (var permission in perms)
            {
                // Delete the entity
                unitOfWork.PermissionRepository.Delete(permission);
            }
 
            // Delete the products from the database
            unitOfWork.Save();
 
            //Return emtpy result
            return Json(null);
        }
    }
}

Here is my  razor view:
@section CustomHeader {
    @* kendo.common.min.css contains common CSS rules used by all Kendo themes *@
    @* kendo.silver.min.css contains the "Blue Opal" Kendo theme *@
}
<h2>Permissions</h2>
<h3>Page Size: <div id="comboBox"></div></h3>
<script>
    $("#comboBox").kendoComboBox({
        dataTextField: "text",
        dataValueField: "value",
        dataSource: [
            { text: 10 },
            { text: 25 },
            { text: 50 },
            { text: 100 },
            { text: 500 }
        ],
        change: function (e) {
            var grid = $("#grid").data("kendoGrid");
            grid.dataSource.pageSize(this.value());
        }
    });
</script>
@* The DIV where the Kendo grid will be initialized *@
<div id="grid"></div>
<script>
 
$(document).ready(function () {
    var dataSource = new kendo.data.DataSource({
    transport: {
        create: {
            url: "@Url.Action("Create", "Permission")", //specify the URL which should create new records. This is the Create method of the HomeController.
            type: "POST" //use HTTP POST request as the default GET is not allowed for ASMX
        },
        read: {
            url: "@Url.Action("Read", "Permission")", //specify the URL which should return the records. This is the Read method of the HomeController.
            type: "POST", //use HTTP POST request as by default GET is not allowed by ASP.NET MVC
        },
        update: {
            url:"@Url.Action("Update", "Permission")", //specify the URL which should update the records. This is the Update method of the HomeController.
            type: "POST" //use HTTP POST request as by default GET is not allowed by ASP.NET MVC
        },
        destroy: {
            url: "@Url.Action("Destroy", "Permission")", //specify the URL which should destroy the records. This is the Destroy method of the HomeController.
            type: "POST" //use HTTP POST request as by default GET is not allowed by ASP.NET MVC
        },
        parameterMap: function(data, operation) {
            if (operation != "read") {
                // post the products so the ASP.NET DefaultModelBinder will understand them:
 
                var result = {};
 
                for (var i = 0; i < data.models.length; i++) {
                    var perm = data.models[i];
 
                    for (var member in perm) {
                        result["perms[" + i + "]." + member] = perm[member];
                    }
                }
 
                return result;
            }
        },
        batch: true, // enable batch editing - changes will be saved when the user clicks the "Save changes" button
        pageSize: 20,
        schema: {
            model: { // define the model of the data source. Required for validation and property types.
                id: "PermissionID",
                fields: {
                    PermissionID: { editable: false, nullable: true },
                    NetLogin: "User",
                    PayrollTitle: "Payroll",
                    FunctionTitle: "Function",
                    Level: { type: "number", validation: { required: true, min: 1} }
                }
            }
        }
    }});                     
 
    $("#grid").kendoGrid({
        dataSource: dataSource,
        pageable: true,
        height: 425,
        sortable: true,
        filterable: true,
        groupable: true,
        resizable: true,
        reorderable: true,
        toolbar: ["create", "save", "cancel"], // specify toolbar commands
        columns: [
            { field: "NetLogin", width: "70px", editor: userDropDownEditor },
            { field: "PayrollTitle", width: "70px", editor: payrollDropDownEditor },
            { field: "FunctionTitle", width: "70px", editor: functionDropDownEditor },
            { field: "Level", width: "70px" },
            { command: "destroy", title: "Delete", width: "60px", groupable: false, filterable: false }],
        editable: true // enable editing
    });
 
});
 
function userDropDownEditor(container, options) {
    $('<input data-text-field="NetLogin" data-value-field="NetLogin" data-bind="value:' + options.field + '"/>')
        .appendTo(container)
        .kendoDropDownList({
            autoBind: false,
            dataSource: {
                transport: {
                    read: {
                        url: "@Url.Action("Read", "User")", //specify the URL which should return the records. This is the Read method of the HomeController.
                        type: "POST" //use HTTP POST request as by default GET is not allowed by ASP.NET MVC
                    }
                }
            }
        });
}
 
function payrollDropDownEditor(container, options) {
    $('<input data-text-field="Title" data-value-field="Title" data-bind="value:' + options.field + '"/>')
        .appendTo(container)
        .kendoDropDownList({
            autoBind: false,
            dataSource: {
                transport: {
                    read: {
                        url: "@Url.Action("Read", "Payroll")", //specify the URL which should return the records. This is the Read method of the HomeController.
                        type: "POST" //use HTTP POST request as by default GET is not allowed by ASP.NET MVC
                    }
                }
            }
        });
}
 
function functionDropDownEditor(container, options) {
    $('<input data-text-field="Title" data-value-field="Title" data-bind="value:' + options.field + '"/>')
        .appendTo(container)
        .kendoDropDownList({
            autoBind: false,
            dataSource: {
                transport: {
                    read: {
                        url: "@Url.Action("Read", "Function")", //specify the URL which should return the records. This is the Read method of the HomeController.
                        type: "POST" //use HTTP POST request as by default GET is not allowed by ASP.NET MVC
                    }
                }
            }
        });
}
 /
</script>

Any help would be really appreciated. I will make a quick example project when I get to work to assist.

Many thanks,

Andy

10 Answers, 1 is accepted

Sort by
0
andrew
Top achievements
Rank 1
answered on 24 Jul 2012, 09:35 AM
Please find attached a sample project. If you go to the Admin tab there are link to the 4 views for users, payrolls, functions and permisions.

The Grid in question is the permissions grid.

There is also another problem on the users form where I wanted to make a Master and Details grid showing Users, then you select a user and it displays the payrolls that user has permissions for in the details grid. Then another level of you can select a payroll and see what function the user can perform and at what level within the selected payroll for the selected user.

This thread primarily addresses the foreign key column issue.

I hope you can help.

Andy

EDIT: File is attached lower down
0
im
Top achievements
Rank 2
answered on 24 Jul 2012, 03:09 PM
Personally I wont go to outside sites to download files. Nor should that be included in the post. If you are using Visual Studio, you can Clean the project (thus deleting the build, temp files, program DB, debug files.. and so on), to limit it to just the source files, that can be re-built by the user. This should make the project allot smaller than 18mb. (I havent seen any project with even hundreds of thousands of lines of code, zipped to 18mb).
0
andrew
Top achievements
Rank 1
answered on 24 Jul 2012, 03:57 PM
Example1.zip
Hi,

Here is a stripped down version, it took a bit of playing around as I've never done this before but her you go...

Andy
0
im
Top achievements
Rank 2
answered on 24 Jul 2012, 04:16 PM
Much better, what version of VS are you using, my VS2010 wont open it, however 2012 will, but I get errors when trying to build it due to dbContext...

Im trying to build, and then re-produce what your seeing, so I can debug, and see what your process looks like to help with a solution.

D
0
im
Top achievements
Rank 2
answered on 24 Jul 2012, 04:22 PM
If you get me your direct email, we can take this off line, and not mess up the thread in the forum, in case others might help as well, and no need for all the back and fourth. I have some time today, I can try and help. D
0
andrew
Top achievements
Rank 1
answered on 24 Jul 2012, 04:22 PM
Hi Derek,

I am using VS2010 Pro, it seems fine on mine I tried it on another machine and it opened too. The only mistake is in Example1/Example1 there is a folder called Example1 that can be deleted completely but it still opens regardless.

Any suggestions as to what I can do to make it available for you to look at?

Andy
0
im
Top achievements
Rank 2
answered on 24 Jul 2012, 05:02 PM
I did get it to work on this machine, the reason was this install didnt have MVC 4 RC on it, as it is a production development machine. After trying it on a testing development box with all the Beta and RC candidates on it, it opened fine, then just had to install the entityframework, and it seems I have a working (possibly) example.

0
im
Top achievements
Rank 2
answered on 24 Jul 2012, 05:19 PM
OK I got it working just fine now, Ive used it in Firefox, as well as IE. there was an issue though in IE, where there was an extra comma (,) that was tripping up IE when displaying the grid. See the Index.cshtml file under Permission. Line 89 I think under the read: statement... it should have NO comma after the  - type: "POST"

Mine reads now- 
read: { url: "@Url.Action("Read", "Permission")", //specify the URL which should return the records. This is the Read method of the HomeController.
type: "POST" //use HTTP POST request as by default GET is not allowed by ASP.NET MVC
},

The only thing I find now is when adding a new record, then an error gets generated. Have you not implemented that part, or is that the crux of you problem?

 


0
andrew
Top achievements
Rank 1
answered on 24 Jul 2012, 05:27 PM
Hi Derek,

Thats where I am up to, the comma was left behind where I tried to do something with the "Data:" function with the read action.

I need editing, adding and deleting to work. I have been playing around with the server side setup and have got a little further, but still erroring on save. Currently my server side testing is only as far as editing and this fails in the repository dbset save action.

My editor dropdown templates are working on the server side attempt and the grid loads, but I can't edit much the same as the client side example I gave you.

Andy
0
Andreas
Top achievements
Rank 1
answered on 11 Feb 2015, 01:02 PM
Is this resolved somehow? @Derek can you please share with other
Dave
Top achievements
Rank 1
commented on 14 Dec 2022, 03:10 PM

I found the solution for myself in the UserController.cs. This is what I need for the Tag Help transport, shown lower down this post. I was looking for how to populate the combo box and the Example1.zip file farther up was what I needed to get me across the line!

        [HttpPost]
        public ActionResult Read()
        {
            var _users = unitOfWork.UserRepository.Get()
                // Use a view model to avoid serializing internal Entity Framework properties as JSON
                .Select(p => new User
                {
                    UserID = p.UserID,
                    NetLogin = p.NetLogin,
                    EmpNumber = p.EmpNumber,
                    Forename = p.Forename,
                    Surname = p.Surname,
                    Active = p.Active
                })
                .ToList();

            return Json(_users);
        }

This is the Tag Help Kendo UI Grid.


    <kendo-grid name="Budgets" navigatable="true">
            <datasource type="DataSourceTagHelperType.Ajax"
                        server-operation="false"
                        batch="true"
                        on-error="error_handler">
                <schema data="Data">
                    <model id="Id">
                        <fields>
                            <field name="Id" type="number" editable="false"></field>
                            <field name="ProjectId" type="number" editable="false"></field>
                            <field name="AccountCodeId" type="number"></field>
                            <field name="ClientAccountCode" type="string"></field>
                            <field name="WorkDescription" type="string" editable="false"></field>
                            <field name="BudgetDescription" type="string"></field>
                            <field name="BudgetAmount" type="number"></field>
                        </fields>
                    </model>
                </schema>
                <transport>
                    <read type="GET" url="@Url.Action("EditingInline_Read", "Budgets", new{id = Model.Id, sortByCA = Model.SortByClientAccountCode, sortByBD = Model.SortByBudgetDescription })" />
                    <update url="@Url.Action("EditingInline_Update", "Budgets")" />
                    <create url="@Url.Action("EditingInline_Create", "Budgets")" />
                    <destroy url="@Url.Action("EditingInline_Delete", "Budgets")" />
                </transport>
            </datasource>
            <columns>
                <foreign-key-column field="AccountCodeId" title="Account Codes" width="150"
                                    value-field="Id"
                                    text-field="AccountCode">
                    <datasource>
                        <transport>
                            <read url="@Url.Action("AccountCodes", "Budgets")"/>
                        </transport>
                    </datasource>
                </foreign-key-column>
                <column field="ClientAccountCode" title="Client AC" />
                <column field="WorkDescription" title="Work Description" />
                <column field="BudgetDescription" title="Budget Description" />
                <column field="BudgetAmount" title="Budget Amount" format="{0:C}" />
                <column template="@rowTemplate" title="Actions" width="100" />
            </columns>
            <toolbar>
                <toolbar-button name="create"></toolbar-button>
                <toolbar-button name="save"></toolbar-button>
            </toolbar>
            <editable mode="incell" />
            <pageable enabled="false" />
            <sortable enabled="false" />
            <scrollable enabled="true" />
        </kendo-grid>

Dave
Top achievements
Rank 1
commented on 14 Dec 2022, 03:12 PM

BTW, my actual Controller was Budgets and the Action is AccountCodes. This is the code I wrote and now it populated the combo box.

        public ActionResult AccountCodes()
        {
            var accountCodes = _context.TblAccountCodes
                .Select(b => new TblAccountCode()
                {
                    Id = b.Id,
                    AccountCode = b.AccountCode,
                    Description = b.Description
                })
                .OrderBy(e => e.AccountCode).ToList();

            return Json(accountCodes);
        }

Neli
Telerik team
commented on 19 Dec 2022, 07:44 AM

Hi Dave,

Thank you very much for sharing your solution with the community. We always appreciate sharing information about solutions and different implementations. I am sure it will be helpful to the other users in the Forum.

Regards,

Neli

Tags
Grid
Asked by
andrew
Top achievements
Rank 1
Answers by
andrew
Top achievements
Rank 1
im
Top achievements
Rank 2
Andreas
Top achievements
Rank 1
Share this question
or