using System.Security.Claims;
using System.Text;
using System.Text.Encodings.Web;
using Microsoft.AspNetCore.Authentication;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Abstractions;
using Microsoft.AspNetCore.Mvc.Formatters;
using Microsoft.AspNetCore.Mvc.Infrastructure;
using Microsoft.Extensions.Options;

namespace OpenAPISwaggerDoc.Web.Authentication;

public class BasicAuthenticationHandler : AuthenticationHandler<AuthenticationSchemeOptions>
{
    private string _failReason;

    public BasicAuthenticationHandler(
        IOptionsMonitor<AuthenticationSchemeOptions> options,
        ILoggerFactory logger,
        UrlEncoder encoder,
        ISystemClock clock)
        : base(options, logger, encoder, clock)
    {
    }

    protected override Task<AuthenticateResult> HandleAuthenticateAsync()
    {
        if (!Request.Headers.ContainsKey("Authorization"))
        {
            _failReason = "Missing Authorization header";
            return Task.FromResult(AuthenticateResult.Fail(_failReason));
        }

        try
        {
            var authenticationHeader = AuthenticationHeaderValue.Parse(Request.Headers["Authorization"]);
            var credentialBytes = Convert.FromBase64String(authenticationHeader.Parameter);
            var credentials = Encoding.UTF8.GetString(credentialBytes).Split(':');
            var username = credentials[0];
            var password = credentials[1];

            if (string.Equals(username, "DNT", StringComparison.Ordinal) &&
                string.Equals(password, "123", StringComparison.Ordinal))
            {
                var claims = new[] { new Claim(ClaimTypes.NameIdentifier, username) };
                var identity = new ClaimsIdentity(claims, Scheme.Name);
                var principal = new ClaimsPrincipal(identity);
                var ticket = new AuthenticationTicket(principal, Scheme.Name);
                return Task.FromResult(AuthenticateResult.Success(ticket));
            }

            _failReason = "Invalid username or password";
            return Task.FromResult(AuthenticateResult.Fail(_failReason));
        }
        catch
        {
            _failReason = "Invalid Authorization header";
            return Task.FromResult(AuthenticateResult.Fail(_failReason));
        }
    }

    protected override async Task HandleChallengeAsync(AuthenticationProperties properties)
    {
        await base.HandleChallengeAsync(properties);
        if (Response.StatusCode == StatusCodes.Status401Unauthorized &&
            !string.IsNullOrWhiteSpace(_failReason))
        {
            Response.Headers.Add("WWW-Authenticate", _failReason);
            Response.ContentType = "application/json";
            await WriteProblemDetailsAsync(_failReason);
        }
    }

    private Task WriteProblemDetailsAsync(string detail)
    {
        var problemDetails = new ProblemDetails { Detail = detail, Status = Context.Response.StatusCode };
        var result = new ObjectResult(problemDetails)
                     {
                         ContentTypes = new MediaTypeCollection(),
                         StatusCode = problemDetails.Status,
                         DeclaredType = problemDetails.GetType(),
                     };
        var executor = Context.RequestServices.GetRequiredService<IActionResultExecutor<ObjectResult>>();
        var routeData = Context.GetRouteData() ?? new RouteData();
        var actionContext = new ActionContext(Context, routeData, new ActionDescriptor());
        return executor.ExecuteAsync(actionContext, result);
    }
}

password: Joi.string().min(3).max(15).required(),
password_confirmation: Joi.any().valid(Joi.ref('password')).required().options({ language: { any: { allowOnly: 'must match password' } } })

confirmPassword: Joi.string().required().valid(Joi.ref('password')).options({
  language: {
    any: {
      allowOnly: '!!Passwords do not match',
    }
  } 
})

Install-Package Microsoft.AspNet.Odata

namespace ProductService.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
        public string Category { get; set; }
    }
}

Install-Package EntityFramework

<connectionStrings>
    <add name="ProductsContext" connectionString="Data Source=.; 
        Initial Catalog=ProductsContext; Integrated Security=True;MultipleActiveResultSets=True;"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

using System.Data.Entity;
namespace ProductService.Models
{
    public class ProductsContext : DbContext
    {
        public ProductsContext() 
                : base("name=ProductsContext")
        {
        }
        public DbSet<Product> Products { get; set; }
    }
}

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        ODataModelBuilder builder = new ODataConventionModelBuilder();
        builder.EntitySet<Product>("Products");
        config.MapODataServiceRoute(
            routeName: "ODataRoute",
            routePrefix: null,
            model: builder.GetEdmModel());
    }
}

using ProductService.Models;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Net;
using System.Threading.Tasks;
using System.Web.Http;
using System.Web.OData;
namespace ProductService.Controllers
{
    public class ProductsController : ODataController
    {
        ProductsContext db = new ProductsContext();
        private bool ProductExists(int key)
        {
            return db.Products.Any(p => p.Id == key);
        } 
        protected override void Dispose(bool disposing)
        {
            db.Dispose();
            base.Dispose(disposing);
        }
    }
}

[EnableQuery]
public IQueryable<Product> Get()
{
    return db.Products;
}
[EnableQuery]
public SingleResult<Product> Get([FromODataUri] int key)
{
    IQueryable<Product> result = db.Products.Where(p => p.Id == key);
    return SingleResult.Create(result);
}

public async Task<IHttpActionResult> Post(Product product)
{
    if (!ModelState.IsValid)
    {
        return BadRequest(ModelState);
    }
    db.Products.Add(product);
    await db.SaveChangesAsync();
    return Created(product);
}

public async Task<IHttpActionResult> Patch([FromODataUri] int key, Delta<Product> product)
{
    if (!ModelState.IsValid)
    {
        return BadRequest(ModelState);
    }
    var entity = await db.Products.FindAsync(key);
    if (entity == null)
    {
        return NotFound();
    }
    product.Patch(entity);
    try
    {
        await db.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
        if (!ProductExists(key))
        {
            return NotFound();
        }
        else
        {
            throw;
        }
    }
    return Updated(entity);
}
public async Task<IHttpActionResult> Put([FromODataUri] int key, Product update)
{
    if (!ModelState.IsValid)
    {
        return BadRequest(ModelState);
    }
    if (key != update.Id)
    {
        return BadRequest();
    }
    db.Entry(update).State = EntityState.Modified;
    try
    {
        await db.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
        if (!ProductExists(key))
        {
            return NotFound();
        }
        else
        {
            throw;
        }
    }
    return Updated(update);
}

public async Task<IHttpActionResult> Delete([FromODataUri] int key)
{
    var product = await db.Products.FindAsync(key);
    if (product == null)
    {
        return NotFound();
    }
    db.Products.Remove(product);
    await db.SaveChangesAsync();
    return StatusCode(HttpStatusCode.NoContent);
}

http://localhost:YourPort/Products

{
  "@odata.context":"http://localhost:4516/$metadata#Products","value":[
    {
      "Id":1,"Name":"Ali","Price":2.00,"Category":"aaa"
    },{
      "Id":2,"Name":"Reza","Price":1.00,"Category":"bbb"
    },{
      "Id":3,"Name":"Ahmad","Price":0.00,"Category":"ccc"
    }
  ]
}

/Products(2)

/Products?$filter=Id gt 1

Products?$select=Name

Products?$select=Name,Price

/Products?$top=3

[DbFunction("MyContext", "CustomersByZipCode")]
    public IQueryable<Customer> CustomersByZipCode(string zipCode)
    {
        var zipCodeParameter = zipCode != null ?
            new ObjectParameter("ZipCode", zipCode) :
            new ObjectParameter("ZipCode", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext
            .CreateQuery<Customer>(
                string.Format("[{0}].{1}", GetType().Name, 
                    "[CustomersByZipCode](@ZipCode)"), zipCodeParameter);
    }

    public ObjectResult<Customer> GetCustomersByName(string name)
    {
        var nameParameter = name != null ?
            new ObjectParameter("Name", name) :
            new ObjectParameter("Name", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext.
            ExecuteFunction<Customer>("GetCustomersByName", nameParameter);
    }

context.Database.ExecuteSqlCommand(
            "CREATE PROCEDURE [dbo].[GetCustomersByName] @Name nvarchar(max) AS " +
            "SELECT [Id], [Name], [ZipCode] " +
            "FROM [dbo].[Customers] " +
            "WHERE [Name] LIKE (@Name)");

        context.Database.ExecuteSqlCommand(
            "CREATE FUNCTION [dbo].[CustomersByZipCode](@ZipCode nchar(5)) " +
            "RETURNS TABLE " +
            "RETURN " +
            "SELECT [Id], [Name], [ZipCode] " +
            "FROM [dbo].[Customers] " + 
            "WHERE [ZipCode] = @ZipCode");