none
How to Access more than One table from a single controller in ASP.Net core web api without using MVC RRS feed

  • Question

  • I have one Controller Named as Info.
    which contains the following method for sorting data for ascending and descending, price greater than less than and like operation from Only table Named Products.

     

     [HttpGet]
            public IEnumerable<Products> GetData([FromQuery]Products sm, [FromQuery]int skip, [FromQuery]int take, [FromQuery]string sortColName,
        [FromQuery]string sortdirection, [FromQuery(Name = "price[gt]")] float price1, [FromQuery(Name = "price[lt]")] float price2,
        [FromQuery(Name="colour[like]")] string clr)
            {
                var data = _context.Products.ToList();
                var sorting = (from p in data select p);
                var dataOrder = _context.Orders.ToList();
                var sorting1 = (from o in dataOrder select o);
                List<Products> TakeResult = null;
                List<Products> ResultOfSort = null;
                List<Products> ResultOfSkip = null;
                List<Products> ResultOfPrice = null;

                // Sorting Part
                if (sortColName != null && sortdirection != null) 
                {
                    TakeResult = sortMethod(data, sortColName, sortdirection);
                }
                if (skip != 0 || take != 0 && TakeResult!=null)
                {
                    ResultOfSort = SkipMethod(TakeResult, skip, take);
                }
                if(price1 != 0 || price2 != 0 && ResultOfSort!=null)
                {
                    ResultOfSkip = PriceMethod(ResultOfSort, price1, price2);
                }
                if (clr != null && ResultOfSkip!=null)
                {
                    ResultOfPrice = ColourMethod(ResultOfSkip, clr);
                    return ResultOfPrice;
                }
             //Main method return
             return sorting;

            }

            // All Methods
            //Sorting
            private List<Products> sortMethod(List<Products>data,string sortColName, string sortdirection)
            {
                var sorting = (from p in data
                               select p).ToList();
                var stcolName = typeof(Products).GetProperty(sortColName);
                List<Products> sortingRes = null;
                switch (sortdirection)
                {
                    case "desc":
                        sortingRes = sorting.OrderByDescending(o=>stcolName.GetValue(o)).ToList();
                        break;

                    default:
                        sortingRes = sorting.OrderBy(o => stcolName.GetValue(o)).ToList();
                        break;

                }
                return sortingRes;
            }

            //Skip and Take Method
            private List<Products> SkipMethod(List<Products> TakeResult, int skip, int take)
            {
                var data = _context.Products.ToList();
                var result = (from s in TakeResult.Skip(skip).Take(take) select s).ToList();
                return result;
            }

            //Price
            private List<Products> PriceMethod(List<Products> ResultOfSort, float price1, float price2)
            {
                List<Products> priceresult = null;
                if (price1 != 0)
                {
                    priceresult = ResultOfSort.Where(o => o.Price > price1).ToList();
                }
                else if (price2 != 0)
                {
                    priceresult = ResultOfSort.Where(o => o.Price < price2).ToList();
                }
                return priceresult;
            }

            //Colour Method
            private List<Products> ColourMethod(List<Products> ResultOfSkip, string clr)
            {
                List<Products> resClr = null;
                resClr = ResultOfSkip.Where(a => a.Colour == clr).ToList();
                return resClr;
            }

    Now I wanted to access another table Named Order from the same web api and using same method for both tables.
    Please help me out.

    Model Class are like



        public class Products
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public String Category { get; set; }
            public float Price { get; set; }
            public string Brand { get; set; }
            public String Colour { get; set; }
            public string Size { get; set; }
            public float Weight { get; set; }
            
        }
        public class Order
        {
            public int Id { get; set; }
            public String OrderName { get; set; }
            public string OrderCategory { get; set; }
            public float Price { get; set; }
            public string Brand { get; set; }
            public string Colour { get; set; }
            public string Size { get; set; }
            public float Weight { get; set; }


        }
        public class PDetailsContext : DbContext
        {
            public PDetailsContext()
            {
            }

            public PDetailsContext(DbContextOptions<PDetailsContext> options) :
                base(options)
            {
            }
            public DbSet<Products> Products { get; set; }
            public DbSet<Order> Orders { get; set; }

        }

    Tuesday, June 25, 2019 6:10 AM

All replies

  • The ASP.NET WebAPI is using the ASP.NET MVC design pattern, which has a Models folder. Now keeping in mind that your usage of the WebAPI has no MVC UI views, it could have the views too, but the WebAPI doesn't and that's 99% of the time, understand the information that is being discussed about the Models folder and the controller  and how to use them in implementing seperation of concerns should be implemented

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/overview/understanding-models-views-and-controllers-cs

    <copied>

    Understanding Models

    We have discussed controllers and we have discussed views. The last topic that we need to discuss is models. What is an MVC model?

    An MVC model contains all of your application logic that is not contained in a view or a controller. The model should contain all of your application business logic, validation logic, and database access logic. For example, if you are using the Microsoft Entity Framework to access your database, then you would create your Entity Framework classes (your .edmx file) in the Models folder.

    A view should contain only logic related to generating the user interface. A controller should only contain the bare minimum of logic required to return the right view or redirect the user to another action (flow control). Everything else should be contained in the model.

    In general, you should strive for fat models and skinny controllers. Your controller methods should contain only a few lines of code. If a controller action gets too fat, then you should consider moving the logic out to a new class in the Models folder.

    <end>

    https://en.wikipedia.org/wiki/Separation_of_concerns

    IMO, there is no reason for you to be having business logic and database logic in the controller. All of the logic you have can be moved to one class in the Models folder that implements an Interface that can be placed into an IoC, dependency injected into the controller and a method or methods of the injected object into the controller can be acted upon by controller action methods.

    Two ways to implement SoC are the usage of Visual Stuido project folder, like the Models folder or you make your own folder, which is called namespace seperation, and the other way is to create a classlib project, make a class or classes in the classlib project, move all the logic into a class or classes that implement an Interface in the classlib project that can be referenced by the WebAPI project and the object or objects in the classlib project DI into the WebAPI controller.

    You have more code in pne action method of the WebAPI controller than I am showing in the entire controller, and it's becuase SoC is being implemented, which can be easily unit tested due to usage of an Interface implemented on the class in the classlib project called the Data Access Layer that is being DI into the WebAPI controller. 

    So you could have all the logic in one class if you choose to do so, and you can do anything you want in code execution of a class or classes  the controller is using, like make multiple database calls.  

    One controller action method doing a single functionality. So it looks to me that you need two Get action methods, which would be the optimal approach.

    You'll also notice I am using attribute routing.

    https://codeopinion.com/asp-net-core-mvc-attribute-routing/

    BTW, ASP.NET issues such as ASP.NET Core and WebAPI have their own forums in ASP.NET forums.

    http://forums.asp.net/

    using DAL;
    using Entities;
    using Microsoft.AspNetCore.Mvc;
    using System.Collections.Generic;
    
    namespace ProgMgmntCore2Api.Controllers
    {
        [Produces("application/json")]
        [Route("api/[controller]")]
        [ApiController]
    
        public class TaskController : ControllerBase
        {
            private readonly IDaoTask _daoTask;
    
            public TaskController(IDaoTask daoTask)
            {
                _daoTask = daoTask;
            }
    
            [HttpGet]
            [Route("GetTaskById")]
            public DtoTask GetTaskById(int id)
            {
                return _daoTask.GetTaskById(id);
            }
    
            [HttpGet]
            [Route("GetTasksByProjId")]
            public List<DtoTask> GetTasksByProjectId(int id)
            {
                return _daoTask.GetTasksByProjectId(id);
            }
    
            [HttpPost]
            [Route("CreateTask")]
            public void CreateTask(DtoTask dto)
            {
                _daoTask.CreateTask(dto);
            }
    
            [HttpPost]
            [Route("UpdateTask")]
            public void UpdateTask(DtoTask dto)
            {
                _daoTask.UpdateTask(dto);
            }
    
            [HttpPost]
            [Route("DeleteTask")]
            public void DeleteTask(DtoId dto)
            {
                _daoTask.DeleteTask(dto.Id);
            }
        }
    }



    • Edited by DA924x Tuesday, June 25, 2019 9:31 AM
    Tuesday, June 25, 2019 9:27 AM
  • But I don't want to use MVC in my project. 
    I only want to check or request via postman or fedler.
    Wednesday, June 26, 2019 5:30 AM
  • But I don't want to use MVC in my project. 
    I only want to check or request via postman or fedler.

    What are you talking about you don't want to use the MVC pattern in your project? You can't advoid that, becuase the ASP.NET MVC WebAPI is derived from the MVC pattern, and you are using the pattern in ASP.NET MVC WebAPI involving Core and non Core ASP,NET solutions

    In order to successful use something, you must understand what you are working with, right?

    https://www.tutorialspoint.com/asp.net_mvc/asp.net_mvc_web_api.htm

    The project structore for ASP.NET MVC using views and an ASP.NET MVC WebAPI project are the same, except for WebAPI doesn't have the Views folder. 

    Fidler or Postman have nothing to do with the architecting of the solution by you and how to use WebAPI effectively and successfully.

    SoC plays a role in ASP.NET MVC with or without using views.

    https://www.c-sharpcorner.com/UploadFile/56fb14/understanding-separation-of-concern-and-Asp-Net-mvc/

    Dependency Injection plays a role  in a .NET Core solution in writing clean code.

    https://msdn.microsoft.com/en-us/magazine/mt703433.aspx?f=255&MSPPError=-2147217396

    And your question about making two reads from the database with returning different models from a single Action result, IMHO the answer is no.

    And you should understand how the DTO pattern plays a role in the WebAPI client and WebAPI service sides.

    https://en.wikipedia.org/wiki/Data_transfer_object

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/data/using-web-api-with-entity-framework/part-5

    https://www.codeproject.com/articles/1050468/data-transfer-object-design-pattern-in-csharp

    Have you deployed the WebAPI project to local IIS to warrant using Postman or Fidler?



    • Edited by DA924x Wednesday, June 26, 2019 8:02 AM
    Wednesday, June 26, 2019 7:52 AM