EF Core 2.2, Passing String Parameter to FromSql Statement

Question

public List<JC_PHASE_MASTER_TOTAL1_MC> GetAllByCompanyCodeJobNumber()
{
    return _context.JC_PHASE_MASTER_TOTAL1_MC.FromSql(
        $"SELECT * FROM JC_PHASE_MASTER_TOTAL1_MC WHERE (Job_Number = '  123')"
    ).ToList();
}

Corresponding SQL:

SELECT *
FROM JC_PHASE_MASTER_TOTAL1_MC
WHERE
    (Job_Number = '  123')

The Job_Number field in the table is a varchar and is stored with leading spaces for any unused digits. When I run the above function with Job_Number hard-coded, the query runs as intended. However, the SQL produced by passing the same value as a parameter looks like this and the SQL run appears to pass a number rather than a string:

public List<JC_PHASE_MASTER_TOTAL1_MC> GetAllByCompanyCodeJobNumber(string jobNumber)
{
    return _context.JC_PHASE_MASTER_TOTAL1_MC.FromSql(
        $"SELECT * FROM JC_PHASE_MASTER_TOTAL1_MC WHERE (Job_Number = {jobNumber})"
    ).ToList();
}

Corresponding SQL with '   123' passed to the jobNumber string parameter:

SELECT *
FROM JC_PHASE_MASTER_TOTAL1_MC
WHERE
    (Job_Number = 123)

Why is the string parameter passed into the SQL statement as 123 and not ' 123'? I'm unsure how to adjust my function so that the parameter is not passed as a number.

Revised code per accepted solution:

public List<JC_PHASE_MASTER_TOTAL1_MC>GetAllByCompanyCodeJobNumber(string jobNumber)
{
    var jobNumberParameter = new SqlParameter("@jobNumber", SqlDbType.VarChar);
    jobNumberParameter.Value = jobNumber;

    return _context.JC_PHASE_MASTER_TOTAL1_MC.FromSql(
        $"SELECT * FROM JC_PHASE_MASTER_TOTAL1_MC WHERE (Job_Number = {jobNumberParameter})"
    ).ToList();
}

I've adjusted my initial post to isolate my question. It's less of a question about performance and more of a realization that the parameter I created is not being passed as I intended. — DGulledge
– DGulledge, Commented Mar 14, 2019 at 22:35
EF Core 2.2 (as the title states), SQL Server, the table field is varchar (as stated in the question). I will consider refactoring and cleaning up the code once the query works. Until then I would rather figure out the cause of the problem. — DGulledge
– DGulledge, Commented Mar 17, 2019 at 2:43

Ryan Sparks · Accepted Answer · 2019-03-18 22:13:55Z

2

Have you tried explicitly setting the SQL data type, something like:

public List<JC_PHASE_MASTER_TOTAL1_MC>GetAllByCompanyCodeJobNumber(string jobNumber)
{
    var jobNumberParameter = new SqlParameter("@jobNumber", SqlDbType.VarChar);
    jobNumberParameter.Value = jobNumber;

    return _context.JC_PHASE_MASTER_TOTAL1_MC.FromSql(
        $"SELECT * FROM JC_PHASE_MASTER_TOTAL1_MC WHERE (Job_Number = {jobNumberParameter})"
    ).ToList();
}

edited Mar 18, 2019 at 22:13

answered Mar 14, 2019 at 23:55

Ryan Sparks

1,38614 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DGulledge Over a year ago

Thank you, this lead me down the path to fix my issue. I've updated my post with the code I ended up using.

Ryan Sparks Over a year ago

I didn't have a machine to test on at the time, glad it got you close enough, I've aligned my answer with your updated question

Collectives™ on Stack Overflow

EF Core 2.2, Passing String Parameter to FromSql Statement

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related