DbContext.DbSet.FromSql() not accepting parameters

I have a database with two tables, and wrote a relatively simple select statement that combines the data and returns me the fields I want. In my SQL developer software it executes just fine.

Now to execute it in my C# .NET Core application, I created a “fake” DbSet in my DbContext that doesn’t have a proper table on the database. The Type of the DbSet is a Model that represents the resulting data structure of the select statement. I use the DbSet field to access the method FromSql() and execute the select like so:

List<ProjectSearchModel> results = _ejContext.ProjectSearch.FromSql(
    @"SELECT combined.Caption, combined.Number FROM 
        (SELECT p.Caption, p.Number, CreateDate FROM dbo.Project AS p
        UNION
        SELECT j.Caption, j.Number, CreateDate  FROM dbo.Job AS j) AS combined
    WHERE combined.Caption LIKE '{0}%' OR combined.Number LIKE '{0}%'
    ORDER BY combined.CreateDate DESC
    OFFSET 0 ROWS
    FETCH FIRST 30 ROWS ONLY", term)
    .ToList();

The SQL does properly return data, I’ve tested that. But the result variable holds 0 entries after executing. In the documentation for FromSql() I found that with MS SQL Servers you have to use OFFSET 0 when using ORDER BY so that’s what I did.

I have no idea what I’m doing wrong.

Answer

As @ChrisPratt said, one of my mistakes was using the DbSet class instead of the DbQuery class. But also, what drove me crazy is that my parameters didn’t work. My mistake was putting them inside a string, which results in them not being recognized as parameters. So my SQL string should be

...
WHERE combined.Caption LIKE {0} + '%' OR combined.Number LIKE {0} + '%'
...

instead of

...
WHERE combined.Caption LIKE '{0}%' OR combined.Number LIKE '{0}%'
...

Advertisement

Answer