How to Use Dbcontext.Database.Sqlquery≪Telement≫(Sql, Params) With Stored Procedure? Ef Code First Ctp5

How to use DbContext.Database.SqlQuery TElement (sql, params) with stored procedure? EF Code First CTP5

You should supply the SqlParameter instances in the following way:

context.Database.SqlQuery<myEntityType>(
    "mySpName @param1, @param2, @param3",
    new SqlParameter("param1", param1),
    new SqlParameter("param2", param2),
    new SqlParameter("param3", param3)
);

What is my generic type/entity? (For Database.SqlQuery)

The myEntityType, or TElement, is the type of object that should be created to house the result. If your procedure did this:

SELECT FieldA FROM table

then you'd need a class like this:

public class myEntityType
{
    public string FieldA { get; set; }
}

where myEntityType is the class name for the type -that's up to you.

must declare the scalar variable '@custid' using dbcontext.Database.SqlQuery?

Since you're using named parameters, you have to specify the matching name for the parameter you're passing.

var results = _MiscContext.Database.SqlQuery<int>(
    "exec sp_GetStaff @custid",
    new SqlParameter("custid", customerNumber)).ToList<int>();

how to use DbContext.Database.SqlQuery(sql, params) without parameters and waiting for a data result

This should work:

dataContext.Database.SqlQuery<Datatype>("name");

The second parameter has the params keyword. Which means you can have from zero to n parameters. Read more on params.

Why does Entity Framework throw an exception when changing SqlParameter order?

It's not because of the parameter order in your parameters object - it's because in your second code snippet you're explicitly passing the @Code value as the first parameter when the SP is expecting a Member INT value.

var result  = context.Database.SqlQuery<resultClass>("mySpName @Code,  @member,@PageSize,@PageNumber" parameters).ToList();

...you're passing in "0165210662660001" as the first parameter and the conversion to INT is failing.

The order of your parameters in your parameters object is irrelevant as EF (ADO.NET actually) will map those parameters to the @parametername values in your query string. So the new SqlParameter("Code","0165210662660001") will be mapped into the @Code position in your query - which int the second code snipped is actually the position for the Member value as expected by the SP.

However... you can execute a SP using named parameters as well and in that case you can pass the parameters to the SP in any order as below:

db.Database.SqlQuery<resultClass>("mySpName PageNumber=@PageNumber,Code=@Code,PageSize=@PageSize,Member=@member", parameters).ToList();

You see that I'm not passing the params to the SP in the order they were defined [by the SP] but because they're named I don't have to care.

For different ways of passing params see: This Answer for some good examples.

How Do I Use CancellationTokenSource With DbContext.Database.SqlQuery TElement (Sp, Param)?

You have to pass the token to .ToListAsync(). Only when .ToListAsync(cancellationToken) is called the query is materialized and send to the server.

But beware that the option to pass a CancellationToken might not have the effect you are hoping to see, see this Q&A/ TL;DR: the query will still run to completion at sql server database level.

DbUpdateException error trying to use EF core code first

You need to call context.Database.EnsureCreated(); to "ensure" the tables are created. More about this method here.

---

Related Topics