C#•2y ago

Need some help with my end-point

So i have this end point:

c++
{
    [Route("[controller]")]
    [ApiController]
    public class StepController : ControllerBase
    {
        private readonly DataContext _context;

        public StepController(DataContext context)
        {
            _context = context;
        }


        [HttpGet]
        public async Task<ActionResult<List<Step>>> Get(string databaseName, int workflowKey)
        {
            string query = "SQLQUERY";

            // some of the data is indeed NULL, my guess would be that calling the below function on that data gives the error?
            var steps = await _context.steps.FromSqlRaw(query).ToListAsync();

            return Ok(steps);

        }
    }

c++
{
    [Route("[controller]")]
    [ApiController]
    public class StepController : ControllerBase
    {
        private readonly DataContext _context;

        public StepController(DataContext context)
        {
            _context = context;
        }


        [HttpGet]
        public async Task<ActionResult<List<Step>>> Get(string databaseName, int workflowKey)
        {
            string query = "SQLQUERY";

            // some of the data is indeed NULL, my guess would be that calling the below function on that data gives the error?
            var steps = await _context.steps.FromSqlRaw(query).ToListAsync();

            return Ok(steps);

        }
    }

My model:

c++
    public class Step
    {
        [Key]
        public int Key { get; set; }
        public string Type { get; set; }
        public int ParentKey { get; set; }
        public string SubWorkflowCode { get; set; }
        public int Sequence { get; set; }
        public int WorkflowKey { get; set; }
        [NotMapped]
        public List<Parameters> Parameters { get; set; }
    }

    public class Parameters
    {
        public string Name { get; set; }
        public string Source { get; set; }
        public string Value { get; set; }
    }

c++
    public class Step
    {
        [Key]
        public int Key { get; set; }
        public string Type { get; set; }
        public int ParentKey { get; set; }
        public string SubWorkflowCode { get; set; }
        public int Sequence { get; set; }
        public int WorkflowKey { get; set; }
        [NotMapped]
        public List<Parameters> Parameters { get; set; }
    }

    public class Parameters
    {
        public string Name { get; set; }
        public string Source { get; set; }
        public string Value { get; set; }
    }

The query works fine in mssql, some of the data is indeed NULL, my guess would be that calling the below function on that data gives the error?

c++
var steps = await _context.steps.FromSqlRaw(query).ToListAsync();

c++
var steps = await _context.steps.FromSqlRaw(query).ToListAsync();

This is the error im getting:

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

This is my first Dotnet project so all tips and hints are welcome!

20 Replies

ero•2y ago

don't know much about this, but as a guess, if some things are nullable, you have to mark them as nullable in the models

Angius•2y ago

I wonder, why FromSqlRaw and not just using EF? Is the issue, perhaps, that you're selecting only some properties with your query? Because that would leave the non-selected properties null, yes

ZilOP•2y ago

This actually worked like this:

c++
{
        [Key]
        public int Key { get; set; }
        public string Type { get; set; }
        public int? ParentKey { get; set; }
        public string? SubWorkflowCode { get; set; }
        public int Sequence { get; set; }
        public int WorkflowKey { get; set; }
        [NotMapped]
        public List<Parameters> Parameters { get; set; }
    }

    public class Parameters
    {
        public string Name { get; set; }
        public string Source { get; set; }
        public string Value { get; set; }
    }

c++
{
        [Key]
        public int Key { get; set; }
        public string Type { get; set; }
        public int? ParentKey { get; set; }
        public string? SubWorkflowCode { get; set; }
        public int Sequence { get; set; }
        public int WorkflowKey { get; set; }
        [NotMapped]
        public List<Parameters> Parameters { get; set; }
    }

    public class Parameters
    {
        public string Name { get; set; }
        public string Source { get; set; }
        public string Value { get; set; }
    }

No reason, just first project and watching tutorials

Angius•2y ago

Just use EF then And .Select() into a DTO That way you won't have to mark properties as nullable when they shouldn't be nullable on the database

ZilOP•2y ago

Could u show me an example of how u would use that ?

Angius•2y ago

record Thing(string Type, int Sequence);

record Thing(string Type, int Sequence);

var steps = await _context.Steps
    .Where(s => s.Id == id)
    .OrderBy(s => s.WorkflowKey)
    .Select(s => new Thing(s.Type, s.Sequence))
    .ToListAsync();

var steps = await _context.Steps
    .Where(s => s.Id == id)
    .OrderBy(s => s.WorkflowKey)
    .Select(s => new Thing(s.Type, s.Sequence))
    .ToListAsync();

This would give you a list of objects with just Type and Sequence pulled from your Step entity Without having to mark other properties as nullable

ZilOP•2y ago

Where do u implement the query in this example ?

Angius•2y ago

Nowhere You don't write a single line of SQL code EF generates it for you My code would generate something like

SELECT s.Type, s.Sequence
FROM Steps s
WHERE s.Id = @id
ORDER BY s.WorkflowKey

SELECT s.Type, s.Sequence
FROM Steps s
WHERE s.Id = @id
ORDER BY s.WorkflowKey

ZilOP•2y ago

i see, and how does it know to what table it should talk ?

Angius•2y ago

DbContext represents the database DbSets within it represent the tables Tables generated by EF will be named after their respective DbSets

ZilOP•2y ago

I see, and i have 1 query that just pulls all the database names that have a certain table. this this also possible with EF ?

Angius•2y ago

First of all, why would you have multiple databases? Second of all, I'm afraid not

ZilOP•2y ago

each client has its own database where all of its workflows are saved, not something i came up with but something i have to work with unfortunately its an application where u can visualize these workflows

Angius•2y ago

Ah, oof, some demented version of multi-tenancy

ZilOP•2y ago

with C# as backend

Angius•2y ago

In that case, yeah, you'd have to rely on SQL But if you do want to execute arbitrary SQL, don't use EF for that, use Dapper

ZilOP•2y ago

Do you maybe have a good tutorial for a good set up. I first used tim correys minimal API tutorial but people told me i shouldnt do that because its not for a beginner project. Also problem was that i didnt really understand the code, you helped me before and I had a problem with dynamic, dynamic. idk if you remember. I tried fixing it and realised i dont really understand my own code so I decided to just start fresh and keep things a basic as possible.

Angius•2y ago

https://github.com/DapperLib/Dapper https://www.learndapper.com/ Far as the API itself, going with the regular controllers template was definitely a good choice I'm afraid I don't have any recommended tutorials on that, though I tend to learn by doing

ZilOP•2y ago

Right i think API itself set up is fine and i understand it. ill just dive into dapper. thanks!

Gaming

Programming

Need some help with my end-point