Foreign key column with Entity Framework

[Omar Muscatello]

Hello Syncfusion Team,

I have a problem with foreign key of grid control.

I'm using EntityFramework 6 Code-first.

These are my class:

public class User{

[Key]

public int UserId {get;set;}

public string Name {get;set;}

public virtual  UserType UserType {get;set;}

}

public class UserType{

[Key]

public int UserTypeId {get;set;}

public string Name {get;set;}

}

--------------------------------

HomeController.cs

public ViewResult Index(){

ViewBag.TypesList = dbContext.UserTypes.ToList();

return View();

}

public JsonResult GetList(){

var result = dbContext.Users.ToList();

return Json(new{ result, count = result.Count() });

}

--------------------------------

Index.cshtml

       @(Html.EJ().Grid<User>("tbListaSistemi")

            .Datasource(ds => ds.URL(Url.Action("GetList")).Adaptor(AdaptorType.UrlAdaptor))

            //Paging

            .AllowPaging()

            .PageSettings((p) => { p.PageCount(20).PageSize(30); })

            .Columns(col =>

            {

               col.Field(p => p.UserId).IsPrimaryKey(true).Visible(false).Add();

               col.Field(p => p.Name).Add();

                col.Field(p => p.UserType.UserTypeId)

                .ForeignKeyField("UserTypeId")

                .ForeignKeyValue("Name")

                .DataSource(ViewBag.TypesList)

                .EditType(EditingType.Dropdown)

                .Width(150)

                .Add();

            })

--------------------------------

The problem is that the UserType field is empty.

If i try to edit a row, the dropdown datasource for UserType is linked correctly to ViewBag.TypesList.

I need to use these classes structure (instead of declaring UserTypeId in User class).

How can I solve this problem?

[Dmytro Kuzmin]

Hello,

you can use .Include to load related entities:

public JsonResult GetList(){

var result = dbContext.Users.Include(p => p.Usertype).ToList();

return Json(new{ result, count = result.Count() });

}

[Madhu Sudhanan P]

Hi Omar,

Thanks for using Syncfusion products.

From the provided code example we understood that you want to display the related entities in the grid. To do so, please refer to the below code example.

@(Html.EJ().Grid<user>("tbListaSistemi")

    .Datasource(ds => ds.URL(Url.Action("GetList")).Adaptor(AdaptorType.UrlAdaptor))   

    .AllowPaging()

    .PageSettings((p) => { p.PageCount(20).PageSize(30); })

    .Columns(col =>

    {

    col.Field(p => p.UserId).IsPrimaryKey(true).Visible(false).Add();

    col.Field(p => p.Name).Add();

    col.Field(p => p.UserType.UserTypeId).DataSource(ViewBag.TypesList).EditType(EditingType.Dropdown).Width(150).Add();
    }))

But we have a bug in defining the complex field names through lambda expression and hence we considered this requirement “Lambda-based field definitions is not working for complex property” as bug and a support incident has been created under your account to track the status of this requirement. Please log on to our support website to check for further updates.

https://www.syncfusion.com/account/login?ReturnUrl=/support/directtrac/incidents

For now to show related entities in the grid, use the below code example.

@(Html.EJ().Grid<user>("tbListaSistemi")

    . . .

    .Columns(col =>

    {

       . . .                    

      col.Field("UserType.UserTypeId").DataSource(ViewBag.TypesList).EditType(EditingType.Dropdown).Add();  

 }))

The above code example will work when the related entities are eager loaded(using Include).

Regards,
Madhu Sudhanan. P

[Omar Muscatello]

Hi Omar,

Thanks for using Syncfusion products.

From the provided code example we understood that you want to display the related entities in the grid. To do so, please refer to the below code example.

@(Html.EJ().Grid<user>("tbListaSistemi")

    .Datasource(ds => ds.URL(Url.Action("GetList")).Adaptor(AdaptorType.UrlAdaptor))   

    .AllowPaging()

    .PageSettings((p) => { p.PageCount(20).PageSize(30); })

    .Columns(col =>

    {

    col.Field(p => p.UserId).IsPrimaryKey(true).Visible(false).Add();

    col.Field(p => p.Name).Add();

    col.Field(p => p.UserType.UserTypeId).DataSource(ViewBag.TypesList).EditType(EditingType.Dropdown).Width(150).Add();
    }))

But we have a bug in defining the complex field names through lambda expression and hence we considered this requirement “Lambda-based field definitions is not working for complex property” as bug and a support incident has been created under your account to track the status of this requirement. Please log on to our support website to check for further updates.

https://www.syncfusion.com/account/login?ReturnUrl=/support/directtrac/incidents

For now to show related entities in the grid, use the below code example.

@(Html.EJ().Grid<user>("tbListaSistemi")

    . . .

    .Columns(col =>

    {

       . . .                    

      col.Field("UserType.UserTypeId").DataSource(ViewBag.TypesList).EditType(EditingType.Dropdown).Add();  

 }))

The above code example will work when the related entities are eager loaded(using Include).

Regards,
Madhu Sudhanan. P

Thank you Dmytro Kuzmin and Madhu Sudhanan P.

I applied the changes but it doesn't work. I attached the solution to see the result. It shows the UserTypeId (instead of Name) and the dropdownlist is not linked with the datasource.

Attachment: GridTest_b5d3015e.zip

[Seeni Sakthi Kumar Seeni Raj]

Hi Omar,

Query #1: the dropdownlist is not linked with the datasource

The datasource given to the dropdown edit type columns must be a text and value pair. Please refer to the below code example and screenshot.

@(Html.EJ().Grid<User>

            ("tbListaSistemi")

            .Datasource(ds => ds.URL(Url.Action("GetList")).Adaptor(AdaptorType.UrlAdaptor))

            .EditSettings(e => e.AllowEditing())

            .AllowPaging()

            .Columns(col =>

            {

                . .  . .

                col.Field("UserType.UserTypeId")

                .DataSource(ViewBag.dropdata)

                .EditType(EditingType.Dropdown)

                .Width(150)

                .Add();
            }))

[ctrl]

List<dropData> drop = new List<dropData>();

        public ActionResult Index()

        {

            drop.Add(new dropData(1,"1"));

            . . .  ..

            drop.Add(new dropData(5,"5"));

            ViewBag.dropdata = drop;

            return View();

        }

        public class dropData

        {

            public dropData() { }

            public dropData(int val, string txt)

            {

                this.value = val;

                this.text = txt;

            }

            public int value { get; set; }

            public string text { get; set; }
        }

Query #2: It shows the UserTypeId (instead of Name)

To display the Name column in the Grid, you have to mention the UserType.Name in the column’s field.

col.Field("UserType.Name").Width(150).Add();

Regards,
Seeni Sakthi Kumar S.

[Omar Muscatello]

Thank you Seeni Sakthi Kumar Seeni Raj.

It works!

But there is another problem, similar to this thread which I reported (here I used foreign-key columns)

When I double-click on a row to start editing, it always shows me the first value in the dropdown instead of the "old" selected value. (UserType.Name column)

I attached the solution so you can try.

Thank you.

Essential Studio MVC 13.3.0.18

Attachment: GridTest_9a81d095.zip

[Omar Muscatello]

Also, when I insert a new user and select an existing value in the dropdown (UserType.Name column) (see user-input.png attached)

the UserType.UserTypeId is 0 and the UserType.Name contains the id of the selected value (see action-parameter.png attached)

How can I solve this problem?

Attachment: images_fb22240f.zip

[Seeni Sakthi Kumar Seeni Raj]

Hi Omar,

Query #1: the UserType.UserTypeId is 0

In the provided sample, you haven’t define the UserType.UserTypeId column in the Grid, which is cause of the problem. For undefined column, while updating the record, the value will be taken as null or zero.

Query #2: UserType.Name contains the id of the selected value

Grid will always takes value of the Dropdown and not the text. So you got a Name as “2”, instead of the “UserType 2”. Please refer to the code example and screenshot.

ViewBag.dropdata = dbContext.UserTypes.Select(p => new dropData() { value = p.UserTypeId/*value is 2*/, text = p.Name/*text is UserType 2*/ }).ToList();
           

 Query #3: When I double-click on a row to start editing, it always shows me the first value in the dropdown instead of the "old" selected value. (UserType.Name column)

While rendering the selected value for the dropdown, Grid uses the corresponding data to search for a value in below given dropdown value. If it is not exist, it will select a first value “UserType 1” as a default. In your sample, Name’s value is “UserType 2” but the corresponding value for the dropDown is “2”. They differs, which is the cause of the problem. If you render a UserTypeId column it works fine.



In the dropdown, if you give the same or identical text and value, it will resolve your problem. Please refer to the below code example

public ActionResult Index()

        {

            drop.Add(new dropData(”UserType 1”,"UserType 1"));

            . . .  ..

            drop.Add(new dropData(“UserType 5”,"UserType 5"));

            ViewBag.dropdata = drop;

            return View();

        }

If we misunderstood your query, could please explain us the way would like to display Name column in Grid and also in the dropdown.

Regards,
Seeni Sakthi Kumar S.