How to add Foreign Key Column or Drop Down List in Grid?

[Jonathan]

I'm trying to add a foreign key column in my grid. I have a backups table that has a pc_id column. I also have a pc table with pc_id and pc_name columns. Right now, my grid is displaying the pc_id because that is the value that is stored in the backups table. However, I want the grid to display the pc_name.

Index.cshtml:

@{
    ViewBag.Title = "Backup Dashboard";
}

@model IEnumerable<MayetDashboard.Models.backup>

<h1>Backup</h1>

@(Html.Kendo().Grid(Model)
    .Name("Grid")
    .Columns(columns =>
    {
        columns.Bound(c => c.backup_id).Title("Backup ID");
        columns.Bound(c => c.pc_id).Title("PC ID");
        //columns.ForeignKey(c => c.pc_id, (System.Collections.IEnumerable)ViewData["pc"], "pc_id", "pc_name").Title("PC ID");
        columns.Bound(c => c.log_file).Title("Log File").Hidden(true);
        columns.Bound(c => c.backup_date).Title("Backup Date");
        columns.Bound(c => c.backup_job).Title("Backup Job");
        columns.Bound(c => c.backup_result).Title("Backup Result");
        columns.Bound(c => c.backup_text).Title("Backup Text").Hidden(true);
        columns.Bound(c => c.stamp_date).Title("Stamp Date");
        columns.Command(command => { command.Destroy(); });
    })
    .Pageable()
    .Filterable(filter =>
    {
        filter.Extra(false);
        filter.Operators(op =>
        {
            op.ForString(str =>
            {
                str.Clear().Contains("Contains");
            });
        });
    })
    .DataSource(dataSource => dataSource
    .Server()
    .Model(model =>
    {
        model.Id(p => p.backup_id);
        model.Field(p => p.pc_id).DefaultValue(1);
    })
    .Read(read => read.Action("Index", "Backup"))
    .Destroy("backups_Destroy", "Backup")
    )
)

 

BackupController.cs:

using System;
using System.Linq;
using System.Data;
using System.Collections.Generic;
using System.Web.Mvc;
using System.Web.Routing;
using Kendo.Mvc.Extensions;
using MayetDashboard.Models;
using System.Data.Entity;
using System.IO;
using Telerik.Windows.Zip;
using System.IO.Compression;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using Kendo.Mvc.UI;

namespace MayetDashboard.Controllers
{
    public class BackupService : IDisposable
    {
        private mayet_dashboardEntities1 db;

        public BackupService(mayet_dashboardEntities1 db)
        {
            this.db = db;
        }

        public IEnumerable<backup> Read()
        {
            return db.backups;
        }

        public void Destroy(backup backup)
        {
            var entity = new backup
            {
                backup_id = backup.backup_id,
                pc_id = backup.pc_id,
                log_file = backup.log_file,
                backup_date = backup.backup_date,
                backup_job = backup.backup_job,
                backup_result = backup.backup_result,
                backup_text = backup.backup_text,
                stamp_date = backup.stamp_date,
            };

            db.backups.Attach(entity);
            db.backups.Remove(entity);
            db.SaveChanges();
        }

        public void Dispose()
        {
            db.Dispose();
        }
    }

    public class BackupController : Controller
    {
        private BackupService backupService;

        public BackupController()
        {
            backupService = new BackupService(new mayet_dashboardEntities1());
        }

        protected override void Dispose(bool disposing)
        {
            backupService.Dispose();

            base.Dispose(disposing);
        }

        public ActionResult Index()
        {
            return View(backupService.Read());
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult Backups_Destroy(backup backup)
        {
            RouteValueDictionary routeValues;

            backupService.Destroy(backup);

            routeValues = this.GridRouteValues();

            return RedirectToAction("Index", routeValues);
        }

        public void Update()
        {
            //GET ALL ZIPPED UP LOG FILES
            string[] arrayLogs = Directory.GetFiles(@"\\192.168.222.245\Backups\logs", "*.zip", System.IO.SearchOption.AllDirectories);
            var countLogs = arrayLogs.Length;
            string tempFolder = @"C:\temp\";
            for (int i = 0; i < countLogs; i++)
            {
                string logItem = arrayLogs[i];
                var logDirectory = new FileInfo(logItem).DirectoryName;
                //ZipFile.ExtractToDirectory(logItem, @"C:\temp\");
                using (System.IO.Compression.ZipArchive archive = ZipFile.OpenRead(logItem))
                {
                    foreach (System.IO.Compression.ZipArchiveEntry entry in archive.Entries)
                    {
                        if (entry.FullName.EndsWith(".txt", StringComparison.OrdinalIgnoreCase))
                        {
                            entry.ExtractToFile(Path.Combine(tempFolder, entry.FullName), true);
                        }
                    }
                }

                //BACKUP_TEXT COLUMN
                string backupText = System.IO.File.ReadAllText(tempFolder + "LogFile.txt");
                string resultBackupText = Regex.Replace(backupText, @"^\s+$[\r\n]*", string.Empty, RegexOptions.Multiline);

                //PC_ID COLUMN
                int startPosPcId = resultBackupText.LastIndexOf("Computer/Account: ") + "Computer/Account: ".Length;
                int lengthPcId = resultBackupText.IndexOf(" /") - startPosPcId;
                string subPcId = resultBackupText.Substring(startPosPcId, lengthPcId);
                int finalSubPcId = 0;
                if (subPcId == "REA015-PC") finalSubPcId = 1;
                else if (subPcId == "REA010-PC") finalSubPcId = 2;
                else if (subPcId == "DESKTOP-V4KPLUF") finalSubPcId = 3;
                else if (subPcId == "REA014-PC") finalSubPcId = 4;
                else if (subPcId == "REA005-PC") finalSubPcId = 5;
                else if (subPcId == "REA016-PC") finalSubPcId = 6;
                else if (subPcId == "SCOTT-PC") finalSubPcId = 7;
                else if (subPcId == "DESKTOP-Q6FFN6U") finalSubPcId = 8;
                else if (subPcId == "REA009-PC") finalSubPcId = 9;
                else if (subPcId == "ALI-PC") finalSubPcId = 10;
                else if (subPcId == "REA006-PC") finalSubPcId = 11;
                else if (subPcId == "DESKTOP-0B5GIC0") finalSubPcId = 12;
                else if (subPcId == "REA018-PC") finalSubPcId = 13;
                else if (subPcId == "EDREAINC-PC") finalSubPcId = 14;
                else if (subPcId == "REA013-PC") finalSubPcId = 15;
                else if (subPcId == "REA011-PC") finalSubPcId = 16;
                else if (subPcId == "JEA-PC") finalSubPcId = 17;
                else if (subPcId == "DESKTOP-UHJ55OH") finalSubPcId = 18;
                else if (subPcId == "REA019-PC") finalSubPcId = 19;

                //BACKUP_DATE COLUMN
                int startPosBackupDate = resultBackupText.IndexOf("Backup end: ") + "Backup end: ".Length;
                int lengthBackupDate = resultBackupText.IndexOf("Time taken: ") - startPosBackupDate;
                string subBackupDate = resultBackupText.Substring(startPosBackupDate, lengthBackupDate);
                string resultSubBackupDate = subBackupDate.TrimEnd('\n').TrimEnd('\r');
                DateTime finalResultSubBackupDate = DateTime.Parse(resultSubBackupDate);

                //BACKUP_JOB COLUMN
                int startPosBackupJob = resultBackupText.IndexOf("Iperius Backup Report") + "Iperius Backup Report".Length;
                int lengthBackupJob = resultBackupText.IndexOf("Backup start: ") - startPosBackupJob;
                string subBackupJob = resultBackupText.Substring(startPosBackupJob, lengthBackupJob);
                string resultSubBackupJob = subBackupJob.TrimStart('\r').TrimStart('\n').TrimEnd('\n').TrimEnd('\r');

                //BACKUP_RESULT COLUMN
                int startPosBackupResult = resultBackupText.IndexOf("Backup result: ") + "Backup result: ".Length;
                int lengthBackupResult = resultBackupText.LastIndexOf("Backup start: ") - startPosBackupResult;
                string subBackupResult = resultBackupText.Substring(startPosBackupResult, lengthBackupResult);
                string resultSubBackupResult = subBackupResult.TrimEnd('\n').TrimEnd('\r');

                //INSERT INTO BACKUP TABLE
                using (var context = new mayet_dashboardEntities1())
                {
                    var table = new backup
                    {
                        pc_id = finalSubPcId,
                        log_file = logItem,
                        backup_date = finalResultSubBackupDate,
                        backup_job = resultSubBackupJob,
                        backup_result = resultSubBackupResult,
                        backup_text = resultBackupText,
                        stamp_date = DateTime.Now
                    };
                    context.backups.Add(table);
                    context.SaveChanges();
                }

                //MOVE LOG FILE FROM LOGS TO LOGS_PROCESSED FOLDER
                string fileName = Path.GetFileNameWithoutExtension(logItem);
                string fileExtension = Path.GetExtension(logItem);
                Regex rgx = new Regex("[^a-zA-Z0-9 -]");
                string fileBackupDate = rgx.Replace(resultSubBackupDate, "");
                string fileRename = @"\\192.168.222.245\Backups\logs_processed\\" + fileName + "_" + subPcId + "_" + fileBackupDate + fileExtension;
                System.IO.File.Move(logItem, fileRename);
            }
        }
    }
}

 

pc.cs:

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace MayetDashboard.Models
{
    using System;
    using System.Collections.Generic;

    public partial class pc
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public pc()
        {
            this.backups = new HashSet<backup>();
            this.events = new HashSet<@event>();
        }

        public int pc_id { get; set; }
        public string pc_name { get; set; }
        public Nullable<int> user_id { get; set; }
        public string system { get; set; }
        public string service_tag { get; set; }
        public Nullable<System.DateTime> purchase_date { get; set; }
        public string backup_code { get; set; }
        public string systeminfo_dump { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<backup> backups { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<@event> events { get; set; }
        public virtual user user { get; set; }
    }
}

 

backup.cs:

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace MayetDashboard.Models
{
    using System;
    using System.Collections.Generic;

    public partial class pc
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public pc()
        {
            this.backups = new HashSet<backup>();
            this.events = new HashSet<@event>();
        }

        public int pc_id { get; set; }
        public string pc_name { get; set; }
        public Nullable<int> user_id { get; set; }
        public string system { get; set; }
        public string service_tag { get; set; }
        public Nullable<System.DateTime> purchase_date { get; set; }
        public string backup_code { get; set; }
        public string systeminfo_dump { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<backup> backups { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<@event> events { get; set; }
        public virtual user user { get; set; }
    }
}

 

Thank you!

[Jonathan]

Sorry, my backup.cs file looks like this:

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace MayetDashboard.Models
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    public partial class backup
    {
        public int backup_id { get; set; }
        public Nullable<int> pc_id { get; set; }
        public string log_file { get; set; }
        public Nullable<System.DateTime> backup_date { get; set; }
        public string backup_job { get; set; }
        public string backup_result { get; set; }
        public string backup_text { get; set; }
        public Nullable<System.DateTime> stamp_date { get; set; }

        public virtual pc pc { get; set; }
    }
}

 

Thank you!

[Viktor Tachev]

Hello Jonathan,

In order to display a field from another table I would suggest defining a ForeignKey column for the Grid. I noticed that there is one commented out in the Grid definition. However, the ViewData collection that contains the data for the column was not populated in the Controller. Please check out the example below that illustrates how a ForeignKey column can be used in the Grid:

• https://demos.telerik.com/aspnet-mvc/grid/foreignkeycolumn

Regards,
Viktor Tachev
Progress Telerik

[Jonathan]

Hi Viktor,

I get this error when I try to run the application:

System.NotSupportedException: 'The entity or complex type 'mayet_dashboardModel.pc' cannot be constructed in a LINQ to Entities query.'

I added the following to my BackupController.cs:

public ActionResult Index()
{
   PopulatePcs();
   return View();
   //return View(backupService.Read());
}

private void PopulatePcs()
{
   var dataContext = new mayet_dashboardEntities1();
   var pcs = dataContext.pcs
      .Select(c => new pc
      {
         pc_id = c.pc_id,
         pc_name = c.pc_name
      })
      .OrderBy(e => e.pc_name);

   ViewData["pcs"] = pcs;
}

My Index.cshtml looks like this:

@{
    ViewBag.Title = "Backup Dashboard";
}

@model IEnumerable<MayetDashboard.Models.backup>

<h1>Backup</h1>

@(Html.Kendo().Grid(Model)
    .Name("Grid")
    .Columns(columns =>
    {
        columns.Bound(c => c.backup_id).Title("Backup ID");
        //columns.Bound(c => c.pc_id).Title("PC ID");
        //columns.Bound(c => c.pc_id).EditorTemplateName("GridForeignKey").Title("PC ID");
        columns.ForeignKey(c => c.pc_id, (System.Collections.IEnumerable)ViewData["pcs"], "pc_id", "pc_name").Title("PC ID");
        columns.Bound(c => c.log_file).Title("Log File").Hidden(true);
        columns.Bound(c => c.backup_date).Title("Backup Date");
        columns.Bound(c => c.backup_job).Title("Backup Job");
        columns.Bound(c => c.backup_result).Title("Backup Result");
        columns.Bound(c => c.backup_text).Title("Backup Text").Hidden(true);
        columns.Bound(c => c.stamp_date).Title("Stamp Date");
        columns.Command(command => { command.Destroy(); });
    })
    .Pageable()
    .Filterable(filter =>
    {
        filter.Extra(false);
        filter.Operators(op =>
        {
            op.ForString(str =>
            {
                str.Clear().Contains("Contains");
            });
        });
    })
    .DataSource(dataSource => dataSource
    .Server()
    .Model(model =>
    {
        model.Id(p => p.backup_id);
        //model.Field(p => p.pc_id).DefaultValue(1);
    })
    .Read(read => read.Action("Index", "Backup"))
    .Destroy("backups_Destroy", "Backup")
    )
)

Thank you!

[Konstantin Dikov]

Hello Jonathan,

You could take a look at the following forum thread, where the error that you are receiving is discussed:

• https://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query

Best Regards,
Konstantin Dikov
Progress Telerik