I need to connect to my SAP Business One SQL Server database, specifically to OHEM table

This table has more than 100 fields but I only need seven of them

I have this dbContext

public class SAPContext:DbContext
{
    public DbSet<Empleado> Empleados { get; set; }

    public SAPContext(){    }

    public SAPContext(DbContextOptions<SAPContext> options)
        : base(options){}

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder
            .UseSqlServer("Data Source = SBO92PL00; Initial Catalog = FULCRUM;....  )
            .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking)
            .LogTo(Console.WriteLine,
                new[] { DbLoggerCategory.Database.Command.Name },
                LogLevel.Information)
            .EnableSensitiveDataLogging();
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Empleado>().ToTable("OHEM");

        modelBuilder.Entity<Empleado>()
        .Property(e => e.firstName)
        .IsRequired(false);

        modelBuilder.Entity<Empleado>()
        .Property(e => e.email)
        .IsRequired(false);

        modelBuilder.Entity<Empleado>()
        .Property(e => e.middleName)
        .IsRequired(false);

        modelBuilder.Entity<Empleado>()
        .Property(e => e.lastName)
        .IsRequired(false);

        modelBuilder.Entity<Empleado>()
        .Property(e => e.U_Activo)
        .IsRequired(false);

        modelBuilder.Entity<Empleado>()
        .Property(e => e.U_Oficina)
        .IsRequired(false);

I have this entity

public class Empleado
{       
      public int empId { get; set; }
      public string firstName { get; set; } = string.Empty;
      public string middleName { get; set; } = string.Empty;
      public string lastName { get; set; } = string.Empty;
      public string email { get; set; } = string.Empty;
      public string U_Oficina { get; set; } = string.Empty;
      public string U_Activo { get; set; } = string.Empty;
}

With the same field names.

I have this method in my repository:

public async Task<IEnumerable<EmpleadoDTO>> GetExtensionesEmpleados()
{
        try
        {
            List<Empleado> listaEmpleados = new List<Empleado>();
            
            listaEmpleados = await _context.Empleados
            .FromSqlRaw("GetExtensionesIntranet")
            .ToListAsync();
            
            if (listaEmpleados.Count() == 0)
            {
                _logger.LogInformation($"No se han encontrado empleados");
            }

            return EmpleadosToDTO(listaEmpleados);
        }
        catch (Exception ex)
        {
            _logger.LogCritical($"Se ha producido una excepcion obteniendo los empleados", ex.Message);
            return null;
        }
}

And this is my stored procedure:

Then in my controller

[HttpGet()]
public async Task<ActionResult<IEnumerable<Empleado>>> GetEmpleados()
{
        var listaEmpleadosDTO = await _repo.GetExtensionesEmpleados();

        if (listaEmpleadosDTO.Count() == 0)
        {
            return NotFound();
        }

        return Ok(listaEmpleadosDTO);
}

But when I try this code:

listaEmpleados = await _context.Empleados
            .FromSqlRaw("GetExtensionesIntranet")
            .ToListAsync();

I keep getting this error:

System.ArgumentNullException: Value cannot be null. (Parameter 'source')

at System.Linq.ThrowHelper.ThrowArgumentNullException(ExceptionArgument argument)
at System.Linq.Enumerable.Count[TSource](IEnumerable`1 source)
at Intranet.API.Controllers.EmpleadosController.GetEmpleados() in /Users/kintela/Repos/Intranet-WebAPI/Intranet.API/Controllers/EmpleadosController.cs:line 25

Any ideas, please?

Thanks

CodePudding user response：

GetExtensionesEmpleados is returning null, so see your logger output for exception in _logger.LogCritical($"Se ha producido una excepcion obteniendo los empleados", ex.Message);

CodePudding user response：

In the end I had several problems. On the one hand My entity did not have any primary key assigned and in fact I do not need it so I had to add this

modelBuilder.Entity<Empleado>().ToTable("OHEM").HasNoKey();

And in the stored procedure it was not returning. the U_Activo field that I had defined in the entity so I had to add it too and now everything works