How to pass null in a string parameter placeholder

Question

I have a dynamic query that I need to build based on a value passed in the URL parameters. The value passed can be null or with value. In this example, the parameter is the title. I'm using the string parameter placeholder with the FromSql C# fonction :

var query = this.BookContext.BookModel.FromSql(
            @"SELECT FROM Books b
            WHERE b.title = {0}, title)

If the title has a value, the query works fine, but I have a problem when the title is null. When the title is null, the condition should be b.title IS NULL since b.title = NULL will not work. My question is how to pass IS NULL condition in a string parameter placeholder?

I tried to build a dynamic condition but it's not allowed in a string parameter placeholder and it makes sense since the string parameter placeholder will have no benefit

string bookCondition = title != "null" ? title : "title IS NULL";

var query = this.BookContext.BookModel.FromSql(
            @"SELECT FROM Books b
            WHERE b.title {0}, bookCondition)

Thanks,

Do you really dislike adding a simple if and building two different queries? — Steve, Aug 04 '20 at 20:58
It's a long query with multiple conditions. I just posted the short version to simplify my question. So a if condition it's not the option i'm looking for — Kivo, Aug 04 '20 at 21:00
`string bookCondition = title == null ? "IS NULL" : $"= '{title}'";` — Rufus L, Aug 04 '20 at 21:06
`.FromSql(string.Format("SELECT FROM Books b WHERE b.title {0}", bookCondition));` **BUT** you should use parameters instead of building a string like this, to avoid sql injection. For example, what if `title = "IS NULL; DROP TABLE [Books];--"`? That would be trouble. see: [Little Bobby Tables](https://bobby-tables.com/) — Rufus L, Aug 04 '20 at 21:07
@RufusL, indeed i want to use parameters, hence my question. — Kivo, Aug 04 '20 at 21:14
You may want to think harder about whether this is really the behavior you want. If the user is doing a search for a book, and they haven't provided a title, does that really mean they want to find books without titles? Or do they just not care what the title is? — StriplingWarrior, Aug 04 '20 at 21:25
`using (var connection = new SqlConnection("connectionString")) { var command = new SqlCommand("SELECT * FROM Books b", connection); if (title != null) { command.CommandText += " WHERE b.Title = @title"; command.Parameters.Add("@title", SqlDbType.VarChar); command.Parameters["@title"].Value = title; } connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine(reader["Title"]); } }` — Rufus L, Aug 04 '20 at 22:26
@RufusL I will not insist, but as I understand the question isn't about assigning null to a SqlParameter. The question is about writing specific query that will work with NULL and NOT NULL parameter. Also the question has EF Core specific. So i think you closed this question prematurely ;) — Dmitry Kolchev, Aug 04 '20 at 22:38
@RufusL You can build injection-safe queries dynamically if you're careful about how you compose it. If you just concatenate different strings that come from knon code, there should be no problem. SQL injection comes in when incorporating user input directly into a query. — Alejandro, Aug 04 '20 at 23:12
@DmitryKolchev No problem - I've re-opened the question; your answer looks good! :) — Rufus L, Aug 04 '20 at 23:17
@Alejandro Yes, that's true, but generally using parameters is a better habit than concatenating strings. — Rufus L, Aug 04 '20 at 23:20

Dmitry Kolchev · Answer 1 · 2020-08-05T08:52:58.787

3

You don't need write dynamic SQL. I think you should use features provided by EF Core and try to create linq query

var query = context.BookModel;

query = from book in query where book.Title == title select book;
// additional search criteria
if(author.HasValue) 
{
    query = from book in query where book.Author == author.Value select book;
}
// and so on
...
var items = await query.ToListAsync();

for sample above EF will generate right SQL code depending on title parameter value.

If you really need dynamic SQL you can use DbParameter as query parameter, but in this case you code becomes platform dependent.

This code for EF Core 3

var parameter = new SqlParameter("@t", SqlDbType.VarChar);
parameter.Value = title == null ? (object)DBNull.Value : (object)title;
var items1 = context.BookModel
    .FromSqlRaw("select * from Books b where (@t is null and b.title is null) or (b.title = @t)"), parameter);

EF Core version 2.2 and earlier had two overloads of method named FromSql, which behaved in the same way as the newer FromSqlRaw and FromSqlInterpolated

edited Aug 05 '20 at 08:52

answered Aug 04 '20 at 21:21

Dmitry Kolchev

2,116
14
16

that's what i was looking for. Thank you :) – Kivo Aug 04 '20 at 21:49
Note that this destroys sargability if the column in question is indexed. You may want to think twice about it if performance becomes a problem. – Alejandro Aug 04 '20 at 23:09
@Alejandro do you a better approach then? and what is sargability ? – Kivo Aug 04 '20 at 23:52
@Alejandro this predicate does not affect performance and SQL Server will use index – Dmitry Kolchev Aug 04 '20 at 23:56

score -1 · Answer 2 · answered Aug 05 '20 at 03:49

    var items1 = null;
    if (title == null)
        items1 = context.BookModel.FromSqlRaw("select * from Books b where tile is null");
    else
    {
        var parameter = new SqlParameter("@t", SqlDbType.VarChar);
        parameter.Value = title;
        items1 = context.BookModel.FromSqlRaw("select * from Books b where tile = @t", parameter);
    }

How to pass null in a string parameter placeholder

2 Answers2