How to show table with foreigh key?

  1. #1

    How to show table with foreigh key?

    Hello! I have some problem with GridPanel.
    Column Brigade in table Request is a foreigh key. How to show
    m.Brigade.BrigadeName
    in a grid? And also i want to change a value in a combobox.

    I try to set
    .ColumnFor(Model, m => m.Brigade)
    - this is shown nothing
    .ColumnFor(Model, m => m.Brigade.BrigadeID)
    - this is shown id value
    .ColumnFor(Model, m => m.Brigade.BrigadeName)
    - this is shown nothing
    RequestsGrid.cshtml
    @using GeoSystem.Db.ViewModel;
    
    @model IEnumerable<GeoSystem.Models.Request>
    
    @(Html.X().Store()
                .ID("BrigadeStore")
                .Model(Html.X().Model()
                    .Fields(
                        new ModelField("id", ModelFieldType.Int) { Mapping = "BrigadeID" },
                        new ModelField("name", ModelFieldType.String) { Mapping = "BrigadeName" }
                    )
                )
                .Proxy(Html.X().AjaxProxy()
                    .Url(Url.Action("GetBrigades"))
                    .Reader(Html.X().JsonReader().RootProperty("data"))
                )
    )
    
    @(Html.X().GridPanel()
                .ID("GridPanel1")
                .Store(
                    Html.X().StoreForModel().ID("Store1")
                )
                .Icon(Icon.Table)
                .Frame(true)
                .Title("Бригады")
                .Height(430)
                .Width(500)
                .StyleSpec("margin-top: 10px;")
                .ColumnModel(
                    Html.X().ColumnFor(Model, m => m.RequestID)
                        .ToBuilder<Column.Builder>()
                        .Width(40)
                        .Renderer("return record.phantom ? '' : value;"),
    
                    Html.X().ColumnFor(Model, m => m.RequestName)
                        .ToBuilder<Column.Builder>()
                        .Flex(1)
                        .Editor(
                            Html.X().TextField().AllowBlank(false)
                        ),
    
                    Html.X().ColumnFor(Model, m => m.Start)
                        .ToBuilder<Column.Builder>()
                        .Flex(1)
                        .Editor(
                            Html.X().TextField().AllowBlank(false)
                        ),
    
                    Html.X().ColumnFor(Model, m => m.Brigade.BrigadeID)
                        .ToBuilder<Column.Builder>()
                        .Flex(1)
                        .Editor(
                            Html.X().ComboBox()
                            .QueryMode(DataLoadMode.Remote)
                            .TriggerAction(TriggerAction.All)
                            .StoreID("BrigadeStore")
                            .ValueField("id")
                            .DisplayField("name")
                        )
                    )
    )
    Controller

            [ChildActionOnly]
            public ActionResult ShowAllRequests()
            {
                IEnumerable<Request> list = requestRepository.GetAll();
                return PartialView("RequestsGrid", list);
            }
    
            public ActionResult GetBrigades()
            {
                return this.Store(brigadeRepository.GetAll());
            }
    Models

     public class Request
        {
            [Field(FieldType = typeof(Ext.Net.Hidden))]
            public int RequestID { get; set; }
            [Field(FieldType = typeof(Ext.Net.Hidden))]
            public int BrigadeID { get; set; }
            [Display(Name = "Название")]
            public string RequestName { get; set; }
            [Display(Name = "Комментарий")]
            public string Comment { get; set; }
            [Display(Name = "Дата начала")]
            public DateTime Start { get; set; }
            [Display(Name = "Дата окончания")]
            public DateTime End { get; set; }
            [Display(Name = "Закрыто")]
            public bool IsDone { get; set; }
            [UIHint("Brigade")]
            public virtual Brigade Brigade { get; set; }
        }
    
    public class Brigade
        {
            [Field(FieldType = typeof(Ext.Net.Hidden))]
            public int BrigadeID { get; set; }
            
            [Required]
            [Display(Name = "Название")]
            public string BrigadeName { get; set; }
            public virtual ICollection<Request> Requests { get; set; }
        }
  2. #2
    Hello @AntonCharov!

    I believe your issue does not belong to Ext.NET, you probably should include .Include(<foreign_key>) while querying data.

    See this stack overflow question that might help: How to load Foreign key referenced tables data also in entity framework
    .

    The .Include() should probably happen after your calls to .GetAll() in controller code.

    Hope this helps!
    Fabrício Murta
    Developer & Support Expert
  3. #3
    I changed my co de to
    @model IEnumerable<GeoSystem.Models.Request>
    
    @(Html.X().Store()
                .ID("BrigadeStore")
                .Model(Html.X().Model()
                    .Fields(
                        new ModelField("id", ModelFieldType.Int) { Mapping = "BrigadeID" },
                        new ModelField("name", ModelFieldType.String) { Mapping = "BrigadeName" }
                    )
                )
                .Proxy(Html.X().AjaxProxy()
                    .Url(Url.Action("GetBrigades"))
                    .Reader(Html.X().JsonReader().RootProperty("data"))
                )
    )
    
    @(Html.X().GridPanel()
                .ID("GridPanelRequest")
                .Store(
                    Html.X().StoreForModel().ID("StoreRequest")
                    .AutoSync(true)
                        .ShowWarningOnFailure(false)                    
                        .SyncUrl(Url.Action("RequestHandleChanges"))
                )
                .Icon(Icon.Table)
                .Frame(true)
                .Title("Заявки")
                .Height(430)
                .Width(500)
                .StyleSpec("margin-top: 10px;")
                .ColumnModel(       
                    Html.X().ColumnFor(Model, m => m.RequestName)
                        .ToBuilder<Column.Builder>()
                        .Flex(1)
                        .Editor(
                            Html.X().TextField().AllowBlank(false)
                        ),
    
                    Html.X().ColumnFor(Model, m => m.Start)
                        .ToBuilder<Column.Builder>()
                        .Flex(1)
                        .Editor(
                            Html.X().TextField().AllowBlank(false)
                        ),
    
                    Html.X().ColumnFor(Model, m => m.Brigade.BrigadeName)
                        .ToBuilder<Column.Builder>()
                        .Flex(1)
                        .Editor(
                            Html.X().ComboBox()
                            .QueryMode(DataLoadMode.Remote)
                            .TriggerAction(TriggerAction.All)
                            .StoreID("BrigadeStore")
                            .ValueField("id")
                            .DisplayField("name")
                        )
                )
                .Plugins(
                    Html.X().CellEditing()
                )
    )
    and add Include()

    public List<Request> GetAllWithBrigade() 
            {
                return GetTable().Include("Brigade").ToList();
            }
    
            [ChildActionOnly]
            public ActionResult ShowAllRequests()
            {
                IEnumerable<Request> list = requestRepository.GetAllWithBrigade();
                return PartialView("RequestsGrid", list);
            }
    Click image for larger version. 

Name:	Скриншот 16-03-2020 003820.png 
Views:	2 
Size:	13.6 KB 
ID:	25315

    but nothing has changed. data in the column is not displayed
    Last edited by AntonCharov; Mar 15, 2020 at 9:44 PM.
  4. #4
    I solved this problem this way

    @model IEnumerable<GeoSystem.Models.Request>
    
        <script>
            var brigadeRenderer = function (value) {
                if (!Ext.isEmpty(value)) {
                    return value.BrigadeName;
                }
    
                return value;
            };
        </script>
    
    @(Html.X().Store()
                .ID("BrigadeStore")
                .Model(Html.X().Model()
                    .Fields(
                        new ModelField("id", ModelFieldType.Int) { Mapping = "BrigadeID" },
                        new ModelField("name", ModelFieldType.String) { Mapping = "BrigadeName" }
                    )
                )
                .Proxy(Html.X().AjaxProxy()
                    .Url(Url.Action("GetBrigades"))
                    .Reader(Html.X().JsonReader().RootProperty("data"))
                )
    )
    
    @(Html.X().GridPanel()
                .ID("GridPanelRequest")
                .Store(Html.X().Store()
                    .Model(Html.X().Model()
                        .Fields(
                            new ModelField("ID", ModelFieldType.Int),
                            new ModelField("RequestName"),
                            new ModelField("Comment"),
                            new ModelField("Start", ModelFieldType.Date),
                            new ModelField("End", ModelFieldType.Date),
                            new ModelField("IsDone", ModelFieldType.Boolean),
                            new ModelField()
                            {
                                Name = "Brigade",
                                Type = ModelFieldType.Object
                            }
                        )
                    )            
                    .AutoSync(true)
                    .ShowWarningOnFailure(true)                    
                    .SyncUrl(Url.Action("RequestHandleChanges"))
                    .DataSource(Model)
                )
                .Icon(Icon.Table)
                .Frame(true)
                .Title("Заявки")
                .Height(430)
                .Width(700)
                .StyleSpec("margin-top: 10px;")
                .ColumnModel(                
                    Html.X().Column()
                        .Text("Номер")
                        .DataIndex("Id"),
    
                    Html.X().Column()
                        .Text("Название")
                        .DataIndex("RequestName")
                        .Editor(
                            Html.X().TextField().AllowBlank(false)
                        ),
    
                    Html.X().Column()
                        .Text("Комментарий")
                        .DataIndex("Comment")
                        .Editor(
                            Html.X().TextField().AllowBlank(false)
                        ),
    
                    Html.X().DateColumn()
                        .Text("Начало")
                        .DataIndex("Start")
                        .Editor(
                            Html.X().DateField().AllowBlank(false)
                        ),
    
                    Html.X().DateColumn()
                        .Text("Конец")
                        .DataIndex("End")
                        .Editor(
                            Html.X().DateField().AllowBlank(false)
                        ),
    
                    Html.X().BooleanColumn()
                        .Text("Завершено")
                        .DataIndex("IsDone")
                        .Editor(
                            Html.X().TextField().AllowBlank(false)
                        ),
    
                    Html.X().Column()
                        .Text("Бригада")
                        .DataIndex("Brigade")
                        .Width(240)
                        .Renderer("brigadeRenderer")
                        .Editor(
                            Html.X().ComboBox()
                            .QueryMode(DataLoadMode.Remote)
                            .TriggerAction(TriggerAction.All)
                            .StoreID("BrigadeStore")
                            .ValueField("id")
                            .DisplayField("name")
                        )
                )
                .Plugins(
                    Html.X().CellEditing()
                )
    )
    But problem with

    .Editor(Html.X().ComboBox()
               .QueryMode(DataLoadMode.Remote)
               .TriggerAction(TriggerAction.All)
               .StoreID("BrigadeStore")
               .ValueField("id")
               .DisplayField("name")
           )
    How to set ComboBox for change value?

    And also

    .AutoSync(true)
          .ShowWarningOnFailure(true)                    
          .SyncUrl(Url.Action("RequestHandleChanges")
    don't work. it's shown warning - status code 500
  5. #5
    Hello again, @AntonCharov.

    We really wanted to help you but, if you can't provide us a runnable test case according to our guidelines on posting threads, we won't be able to help you at all.

    Here, please seriously consider reading through the threads below for this would greatly help us help you:

    - Tips for creating simplified code samples
    - More Information Required
    - Forum Guidelines

    I see in your second post you used a code line like this:

    return GetTable().Include("Brigade").ToList();
    This suggests it would be feasible to "wrap" the EntityFramework request to a static list; then we won't have the requirement to have a database and entity framework set up at all to reproduce your scenario. You most likely can wrap up the repository and classes into static hardcoded data for the sake of simplicity.

    I have tried a little with your initial test case and started wrapping up the classes into this:

    #region 62860 - show table with foreign key
    public class C62860
    {
        public class Request
        {
            public Request(int seed, Brigade brigadeRef)
            {
                RequestID = 100 + seed;
                BrigadeID = 200 + seed;
                RequestName = "Request #" + RequestID;
                Comment = "Comment for " + RequestName;
                Start = DateTime.Now.AddDays(-seed);
                End = DateTime.Now.AddDays(3 - seed);
                IsDone = DateTime.Now > End;
                Brigade = brigadeRef;
            }
    
            [Field(FieldType = typeof(Ext.Net.Hidden))]
            public int RequestID { get; set; }
            [Field(FieldType = typeof(Ext.Net.Hidden))]
            public int BrigadeID { get; set; }
            [Display(Name = "Название")]
            public string RequestName { get; set; }
            [Display(Name = "Комментарий")]
            public string Comment { get; set; }
            [Display(Name = "Дата начала")]
            public DateTime Start { get; set; }
            [Display(Name = "Дата окончания")]
            public DateTime End { get; set; }
            [Display(Name = "Закрыто")]
            public bool IsDone { get; set; }
            [UIHint("Brigade")]
            public virtual Brigade Brigade { get; set; }
        }
    
        public class Brigade
        {
            public Brigade(int seed)
            {
                BrigadeID = 1000 + seed;
                BrigadeName = "Brigade #" + BrigadeID;
                Requests = new List<Request>();
            }
    
            [Field(FieldType = typeof(Ext.Net.Hidden))]
            public int BrigadeID { get; set; }
    
            [Required]
            [Display(Name = "Название")]
            public string BrigadeName { get; set; }
            public virtual ICollection<Request> Requests { get; set; }
        }
    
        public class RequestRepository
        {
            public static List<Request> GetAllIncluding()
            {
                return new List<Request>()
                {
                    new Request(1, new Brigade(10)),
                    new Request(2, new Brigade(20)),
                    new Request(3, new Brigade(30)),
                    new Request(4, new Brigade(40)),
                    new Request(5, new Brigade(50)),
                    new Request(6, new Brigade(60))
                };
            }
        }
    
        public class BrigadeRepository
        {
            public static List<Brigade> GetAllIncluding()
            {
                return new List<Brigade>()
                {
                    new Brigade(1),
                    new Brigade(2),
                    new Brigade(3),
                    new Brigade(4),
                    new Brigade(5),
                    new Brigade(6)
                };
            }
        }
    }
    #endregion 62860 - show table with foreign key
    This should have left in the model, and then from what you provided, we could have the controller with this:

    #region 62860 - show table with foreign key
    [ChildActionOnly]
    public ActionResult c62860_ShowAllRequests()
    {
        IEnumerable<issuesModel.C62860.Request> list = issuesModel.C62860.RequestRepository.GetAllIncluding();
        return PartialView("RequestsGrid", list);
    }
    
    public ActionResult c62860_GetBrigades()
    {
        return this.Store(issuesModel.C62860.BrigadeRepository.GetAllIncluding());
    }
    #endregion 62860 - show table with foreign key
    But we still miss the "bare" implementation for other methods in your page (like the one you complain about the 500 error), so we are but left in the dark and unable to help.

    Please take the time to review our guidelines on posting questions here and review your example to something we could run, else we won't be able to help.

    On a second topic, if you are no longer in need of help regarding the reference using the ColumnFor() syntax, we should then switch to a new forum thread, if the question now is about the editor action not working.

    Hope you understand.
    Fabrício Murta
    Developer & Support Expert

Similar Threads

  1. Enter key behavior as tab key
    By wichogg in forum 2.x Help
    Replies: 7
    Last Post: Dec 03, 2013, 8:00 PM
  2. [CLOSED] MVC 4 Razor Table Layout.Table
    By ashleysbuss in forum 2.x Legacy Premium Help
    Replies: 2
    Last Post: Jun 18, 2013, 10:39 PM
  3. [CLOSED] show hide components in table layout
    By John_Writers in forum 2.x Legacy Premium Help
    Replies: 2
    Last Post: May 22, 2013, 11:27 AM
  4. [CLOSED] Table Layout, 100% width Table
    By sisa in forum 1.x Legacy Premium Help
    Replies: 5
    Last Post: May 20, 2011, 6:40 AM
  5. [CLOSED] pop up a page when i press a key on key board
    By Vasudhaika in forum 1.x Legacy Premium Help
    Replies: 3
    Last Post: Dec 15, 2010, 3:55 PM

Posting Permissions