locked
Basing on the value from another tables in EE RRS feed

  • Question

  • User798743916 posted

    Hey, I am following this article: "https://dotnettutorials.net/lesson/web-api-with-sql-server/" and try to enhance it by add another table into model and try to check the value again that new table before return result from "/api/employees".  Can I do that, any suggestion in code level?  Thanks first! 

    Thursday, March 4, 2021 8:42 PM

Answers

  • User1686398519 posted

    Hi toronto89999, 

    If you use the example in the link you provided to create the project, that is to say, you are using DB First, you need to add a table called "Department" to the database, and add a field to "Employee" To specify which department it belongs to.

    Then you need to open the EmployeeDBContext.edmx file, then right-click and select "Update Model From Database...".

    Then you need to create an action in your controller to filter data based on DepartmentId.

    In addition, if there are multiple get methods in the same controller, I modify the routing template in WebApiConfig.cs so that the corresponding method can be found correctly.

    Below is an example (based on the example in the link you provided), you can refer to it.

    Create Table Department

    CREATE TABLE [dbo].[Department] (
        [DepartmentID]   INT           IDENTITY (1, 1) NOT NULL,
        [DepartmentName] NVARCHAR (50) NULL,
        PRIMARY KEY CLUSTERED ([DepartmentID] ASC)
    );

    Add a field to "Employee"

    • You can right-click the Emploee table and select "View Designer", and then add a new field called DepartmentID.

    Controller

        public class EmployeesController : ApiController
        {
            public IEnumerable<Employees> Get()
            {
                using (EmployeeDBContext dbContext = new EmployeeDBContext())
                {
                    return dbContext.Employees.ToList();
                }
            }
            public Employees Get(int id)
            {
                using (EmployeeDBContext dbContext = new EmployeeDBContext())
                {
                    return dbContext.Employees.FirstOrDefault(e => e.ID == id);
                }
            }
            public IEnumerable<Employees> GetEmployeesWithDepartmentId(int id)
            {
                using (EmployeeDBContext dbContext = new EmployeeDBContext())
                {
                    return dbContext.Employees.Where(m=>m.DepartmentID==id).ToList();
                }
            }
        }

    WebApiConfig.cs

            public static void Register(HttpConfiguration config)
            {
                config.MapHttpAttributeRoutes();
                config.Routes.MapHttpRoute(
                    name: "CustomApi",
                    routeTemplate: "api/{controller}/{action}/{id}",
                    defaults: new { id = RouteParameter.Optional }
                );
            }

    Here is the result. 

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 8, 2021 8:35 AM

All replies

  • User475983607 posted

    Hey, I am following this article: "https://dotnettutorials.net/lesson/web-api-with-sql-server/" and try to enhance it by add another table into model and try to check the value again that new table before return result from "/api/employees".  Can I do that, any suggestion in code level?  Thanks first! 

    Do you have a question?  Do you need help solving a coding problem?  It is customary to to share a code sample that reproduces the problem. Then explain the expected results and actual results.

    Thursday, March 4, 2021 9:51 PM
  • User1686398519 posted

    Hi toronto89999, 

    try to enhance it by add another table into model and try to check the value again that new table before return result from "/api/employees".

    Can I understand your question this way?

    Suppose there is another table called Department (DepartmentId, DepartmentName), and a new field (DepartmentId) is added to Employee to specify which department the employee belongs to. You need to get all employees in the department based on the selected department.

    Best Regards,

    YihuiSun

    Friday, March 5, 2021 3:07 AM
  • User798743916 posted

    YihuiSun

    Hi toronto89999, 

    toronto89999

    try to enhance it by add another table into model and try to check the value again that new table before return result from "/api/employees".

    Can I understand your question this way?

    Suppose there is another table called Department (DepartmentId, DepartmentName), and a new field (DepartmentId) is added to Employee to specify which department the employee belongs to. You need to get all employees in the department based on the selected department.

    Best Regards,

    YihuiSun

    Hi, Sun   Yes,  you are right, so my following question, in the controller script, how can I add code to do that? Below is current controller script, FYI, thank!   I am following the script within this article : https://dotnettutorials.net/lesson/web-api-with-sql-server/

    <div class=""> <div>namespace EmployeeService.Controllers</div> </div> <div class=""> <div>{</div> </div> <div class=""> <div> public class EmployeesController : ApiController</div> </div> <div class=""> <div> {</div> </div> <div class=""> <div> public IEnumerable<Employee> Get()</div> </div> <div class=""> <div> {</div> </div> <div class=""> <div> using (EmployeeDBContext dbContext = new EmployeeDBContext())</div> </div> <div class=""> <div> {</div> </div> <div class=""> <div> return dbContext.Employees.ToList();</div> </div> <div class=""> <div> }</div> </div> <div class=""> <div> }</div> </div> <div class=""> <div> public Employee Get(int id)</div> </div> <div class=""> <div> {</div> </div> <div class=""> <div> using (EmployeeDBContext dbContext = new EmployeeDBContext())</div> </div> <div class=""> <div> {</div> </div> <div class=""> <div> return dbContext.Employees.FirstOrDefault(e => e.ID == id);</div> </div> <div class=""> <div> }</div> </div> <div class=""> <div> }</div> </div> <div class=""> <div> }</div> </div> <div class=""> <div>}</div> </div>

    Friday, March 5, 2021 2:46 PM
  • User1686398519 posted

    Hi toronto89999, 

    If you use the example in the link you provided to create the project, that is to say, you are using DB First, you need to add a table called "Department" to the database, and add a field to "Employee" To specify which department it belongs to.

    Then you need to open the EmployeeDBContext.edmx file, then right-click and select "Update Model From Database...".

    Then you need to create an action in your controller to filter data based on DepartmentId.

    In addition, if there are multiple get methods in the same controller, I modify the routing template in WebApiConfig.cs so that the corresponding method can be found correctly.

    Below is an example (based on the example in the link you provided), you can refer to it.

    Create Table Department

    CREATE TABLE [dbo].[Department] (
        [DepartmentID]   INT           IDENTITY (1, 1) NOT NULL,
        [DepartmentName] NVARCHAR (50) NULL,
        PRIMARY KEY CLUSTERED ([DepartmentID] ASC)
    );

    Add a field to "Employee"

    • You can right-click the Emploee table and select "View Designer", and then add a new field called DepartmentID.

    Controller

        public class EmployeesController : ApiController
        {
            public IEnumerable<Employees> Get()
            {
                using (EmployeeDBContext dbContext = new EmployeeDBContext())
                {
                    return dbContext.Employees.ToList();
                }
            }
            public Employees Get(int id)
            {
                using (EmployeeDBContext dbContext = new EmployeeDBContext())
                {
                    return dbContext.Employees.FirstOrDefault(e => e.ID == id);
                }
            }
            public IEnumerable<Employees> GetEmployeesWithDepartmentId(int id)
            {
                using (EmployeeDBContext dbContext = new EmployeeDBContext())
                {
                    return dbContext.Employees.Where(m=>m.DepartmentID==id).ToList();
                }
            }
        }

    WebApiConfig.cs

            public static void Register(HttpConfiguration config)
            {
                config.MapHttpAttributeRoutes();
                config.Routes.MapHttpRoute(
                    name: "CustomApi",
                    routeTemplate: "api/{controller}/{action}/{id}",
                    defaults: new { id = RouteParameter.Optional }
                );
            }

    Here is the result. 

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 8, 2021 8:35 AM
  • User798743916 posted

    Hi toronto89999, 

    If you use the example in the link you provided to create the project, that is to say, you are using DB First, you need to add a table called "Department" to the database, and add a field to "Employee" To specify which department it belongs to.

    Then you need to open the EmployeeDBContext.edmx file, then right-click and select "Update Model From Database...".

    Then you need to create an action in your controller to filter data based on DepartmentId.

    In addition, if there are multiple get methods in the same controller, I modify the routing template in WebApiConfig.cs so that the corresponding method can be found correctly.

    Below is an example (based on the example in the link you provided), you can refer to it.

    Create Table Department

    CREATE TABLE [dbo].[Department] (
        [DepartmentID]   INT           IDENTITY (1, 1) NOT NULL,
        [DepartmentName] NVARCHAR (50) NULL,
        PRIMARY KEY CLUSTERED ([DepartmentID] ASC)
    );

    Add a field to "Employee"

    • You can right-click the Emploee table and select "View Designer", and then add a new field called DepartmentID.

    Controller

        public class EmployeesController : ApiController
        {
            public IEnumerable<Employees> Get()
            {
                using (EmployeeDBContext dbContext = new EmployeeDBContext())
                {
                    return dbContext.Employees.ToList();
                }
            }
            public Employees Get(int id)
            {
                using (EmployeeDBContext dbContext = new EmployeeDBContext())
                {
                    return dbContext.Employees.FirstOrDefault(e => e.ID == id);
                }
            }
            public IEnumerable<Employees> GetEmployeesWithDepartmentId(int id)
            {
                using (EmployeeDBContext dbContext = new EmployeeDBContext())
                {
                    return dbContext.Employees.Where(m=>m.DepartmentID==id).ToList();
                }
            }
        }

    WebApiConfig.cs

            public static void Register(HttpConfiguration config)
            {
                config.MapHttpAttributeRoutes();
                config.Routes.MapHttpRoute(
                    name: "CustomApi",
                    routeTemplate: "api/{controller}/{action}/{id}",
                    defaults: new { id = RouteParameter.Optional }
                );
            }

    Here is the result. 

    Best Regards,

    YihuiSun

    Hey, YihuiSun   Thank you very much for the help.   Your solution work great! 

    Tuesday, March 9, 2021 4:38 PM