No mapping to a relational type can be found for the CLR type 'SqlParameter'

Question

When trying to execute a SQL request with named parameters on EntityFrameworkCore (2.2) I get the following exception :

InvalidOperationException: No mapping to a relational type can be found for the CLR type 'SqlParameter'.

What I've tried and works but unsatisfactory:

Raw SQL request: Works but unsafe
Unamed parameters: Also works but less explicit imo

await context.Database.ExecuteSqlCommandAsync(
                    "UPDATE dbo.Table SET ActivationDate = @p0, SubscriptionEndTime = @p1 WHERE SerialNumber IN (@p2) AND UserId = @p3;",
                    DateTime.UtcNow,
                    DateTime.UtcNow.AddYears(1),
                    string.Join("','", eqs.Select(e => e.SerialNumber)),
                    user.Id);

What does not work:

Passing an array of SqlParameters or objects

This is my code:

await context.Database.ExecuteSqlCommandAsync(
                    "UPDATE dbo.Table SET ActivationDate = @actDate, SubscriptionEndTime = @endDate WHERE SerialNumber IN (@serials) AND UserId = @userId;",
                    new SqlParameter("@actDate", DateTime.UtcNow),
                    new SqlParameter("@endDate", DateTime.UtcNow.AddYears(1)),
                    new SqlParameter("@serials", string.Join("','", eqs.Select(e => e.SerialNumber))),
                    new SqlParameter("@userId", user.Id));

I don't understand why it isn't working because according to the documentation (link):

This allows you to use named parameters in the SQL query string. context.Database.ExecuteSqlCommandAsync("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @author", new SqlParameter("@author", userSuppliedAuthor));

So how can I use named parameters in my request ?

EDIT :

I don't think the problem comes from the request itself. The following one results in the same exact Exception:

await context.Database.ExecuteSqlCommandAsync(
                    "UPDATE dbo.Table SET Name = @name WHERE SerialNumber = 'XXXX'",
                    new SqlParameter("@name", "testing"));

You can't use a single parameter for a `WHERE … IN` clause — stuartd, May 15 '19 at 09:17
@stuartd It is actually a concatenation of SerialNumbers. The resulting query is something like `WHERE ... IN ('serial1', 'serial2')` — Guillaume B, May 15 '19 at 09:28
Yes, and that's your problem. Each value in the `IN…` list needs to be a separate parameter (or you can send a table valued parameter) — stuartd, May 15 '19 at 09:32
@stuartd Alright thanks, I'll fix that. But even a simple request fails with the same exception (see my edit) — Guillaume B, May 15 '19 at 09:43
@GuillaumeB The link you posted though is to EF 6.2 which *doesn't* work on the .NET Core runtime — Panagiotis Kanavos, May 15 '19 at 09:50
@PanagiotisKanavos True, but the exception occurs even while using `context.Table.FromSql(...)` which works on .NET Core — Guillaume B, May 15 '19 at 12:37

score 0 · Accepted Answer · answered May 15 '19 at 12:52

0

Found my solution...

The imported SqlParameter was from namespace:

Microsoft.Azure.Documents.SqlParameter

The correct one is:

System.Data.SqlClient.SqlParameter

answered May 15 '19 at 12:52

Guillaume B

1
4

No mapping to a relational type can be found for the CLR type 'SqlParameter'

1 Answers1