CODEWITHSUNDEEP
c#asp.net-core-2.2
Kivo
Kivo

Reputation: 435

How to pass null in a string parameter placeholder

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,

Upvotes: 1

Views: 846

Answers (2)

Dmitry Kolchev
Dmitry Kolchev

Reputation: 2216

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

Upvotes: 3

An Nguyen
An Nguyen

Reputation: 1

    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);
    }

Upvotes: -1

Related Questions