CODEWITHSUNDEEP
sqlsql-serverasp.net-coreentity-framework-core
lenny
lenny

Reputation: 784

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.

Upvotes: 0

Views: 599

Answers (2)

lenny
lenny

Reputation: 784

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}%'
...

Upvotes: 0

Chris Pratt
Chris Pratt

Reputation: 239300

You need to use DbQuery<T> instead:

public DbQuery<Project> ProjectSearch { get; set; }

Then, you can issue arbitrary queries using FromSql on that. With DbSet, the query must be composable, which means it can only be a standard select, it must correspond to a real table, and must include all properties on the entity - none of which apply to the query you're trying to perform.

Upvotes: 1

Related Questions