Asp.Net Core 3.1 with Razor - Foreign key in grid does not work with EntityFrameworkCore

[Avinash]

Hi,

I have a project connecting to SQL Server DB using EF-Core.

ejs-grid works fine for all features for a single table. However as soon as I change the following

<e-grid-column field="DepartmentRefId" headerText="Department" width="200"></e-grid-column>

to

<e-grid-column field="DepartmentRefId" headerText="Department" width="200" 

foreignKeyField="DeptId" foreignKeyValue="DeptName" dataSource="@Model.Departments"></e-grid-column>

the whole grid stops rendering on the screen.

When I change the Datasource to a load hard-coded list of Employees and Department, it works fine.

The only difference between the hard-coded list vs the list loaded from the DB with EF-Core is as follows, marked in red.

Department object	Hard Coded	EF-Core
DeptId	1	1
DeptName	Sales	Sales
Location	Mumbai	Mumbai
Employees	null	{Collection of Employees objects from this Dept}

Employee object	Hard Coded	EF-Core
Id	1	1
EmpCode	ABC001	ABC001
Name	Avinash Tauro	Avinash Tauro
Email	[email protected]	[email protected]
DateOfBirth	17-08-1976	17-08-1976
Salary	100000	100000
DepartmentRefId	1	1
Department	null	{Department object for DeptId = 1}
Active	TRUE	TRUE

I have attached the project and the sql server script. Please refer index.cshtml & index.cshtml.cs

Hard coded values (<ejs-grid id="Grid">)

public Employee[] Employees { get; set; }        

public Department[] Departments { get; set; }

Loaded from SQL Server - EFCore (<ejs-grid id="Grid2">)

public Employee[] Employees2 { get; set; }

public Department[] Departments2 { get; set; }

Thanks

Avinash Tauro

Attachment: VS2019__SQL_Server_Script_ac104cb1.zip

[Avinash]

Hi,

I figured out the workaround to the issue with EF Core.

So Initially my Dept and Employee classes were as below. EF Core has an inbuilt feature, that if we load List<Dept> and List<Employee> in the model, it automatically creates a list of Employees in Dept class and loads it with all Employee references belonging to the Dept. Similarly in the Employee class the Department object is automatically assigned. This is perfect because we can always reference the full Department object for every Employee as well as access all employees for a department automatically, without any Linq operations.

But this was the reason for my issue on ejs-grid. When I commented the Employees collection, in the Department class like this 

//public ICollection<Employee> Employees { get; set; }

The ejs-grid rendered correctly and everything works fine. 

     public class Department
     {
          [Key]
          public int DeptId { get; set; }

          [Required]
          [StringLength(50)]
          [Column(TypeName = "VARCHAR(50)")]
          public string DeptName { get; set; }

          [StringLength(50)]
          [Column(TypeName = "VARCHAR(50)")]
          public string Location { get; set; }

       public ICollection<Employee> Employees { get; set; }

     }

     public class Employee
     {
          [Key]
          public int Id { get; set; }

          [Required]
          [StringLength(7, MinimumLength = 7)]
          [Column(TypeName = "CHAR(7)")]
          [RegularExpression("^[A-Z]{3}[0-9]{4}$")]
          public string EmpCode { get; set; }

          [StringLength(100)]
          [Column(TypeName = "VARCHAR(100)")]
          public string Name { get; set; }

          [ForeignKey("Department")]
          public int DepartmentRefId { get; set; }

          public Department Department { get; set; }
     }

Query: Is my workaround the only way to resolve this? Are there any other solutions where I can keep the Employees collection in the Department class?

Thanks 

Avinash Tauro

[Balaji Sekar]

Hi Avinash, 

  

Greetings from the Syncfusion support. 

  

We have analyzed your code with information provided and our foreignKeyColumn feature same functionality because of, foreignKeyColumn used two different table data and maintain the common data column on both table data so, we can access one table to another one using common data column reference. Hence, you have achieved same functionality in your application 

  

If you use another class property in your own class property, you can use common property of reference in both table(data type of reference property should be use model class(<Employee>) or related class property.) 

  

Sine you can generate model based on you r requirement. 

  

Regards, 

Balaji Sekar 

[Avinash]

We have analyzed your code with information provided and our foreignKeyColumn feature same functionality because of, foreignKeyColumn used two different table data and maintain the common data column on both table data so, we can access one table to another one using common data column reference. Hence, you have achieved same functionality in your application

>> If you mean that I can access Dept object from employee instance and vice versa, I already know this and I have explained this same thing in the ticket.

If you use another class property in your own class property, you can use common property of reference in both table(data type of reference property should be use model class(<Employee>) or related class property.)

Sine you can generate model based on you r requirement.

>> Sorry, I did not understand this at all. 

Could you please confirm my query

Query: Is my workaround the only way to resolve this? Are there any other solutions where I can keep the Employees collection in the Department class?

Thanks

Avinash Tauro

[Balaji Sekar]

Hi Avinash, 

 

Already, you have achieved the two combination of table and relationship between one to one concept. In this case, it automatically creates a list of Employees in Dept class and loads it with all Employee references belonging to the Dept. Similarly in the Employee class the Department object is automatically assigned. It is default functionalities. 

Since you can generate model class(Employees and Department) based on your requirement that we have tried to explain in previous update. 

Regards, 

Balaji Sekar