Efcore: Support passing DbParameter instances to raw SQL APIs

[Vasim-DigitalNexus]

Example:-

Instead of:
var UserType = dbcontext.Set().FromSql("dbo.SomeSproc @p0, @p1", 45, "Ada")

Use named parameters:

object[] sqlParams = {
new SqlParameter("@Id", 45),
new SqlParameter("@Name", "Ada")
};

var UserType = dbcontext.Set().FromSql("dbo.SomeSproc", sqlParams)

If SqlParameter is not supported then something like this would suffice:

var parameterDictionary = new Dictionary
{
{"@Id", 45},
{"@Name", "Ada"}
};

This would make it easier to find parameters in SQL profiler, also by using a list of named parameters there would be no need to worry about parameter order

[rowanmiller]

Note for triage: We should support passing in DbParameters as well as just the raw values (we supported this in EF6), but it doesn't work at the moment.

[tuespetre]

Dictionaries are cool and all but the 'micro-orm' approach of allowing anonymous objects to be passed in is much more convenient.

[prasannapattam]

I also vote for anonymous object approach to pass in the named parameters.

[vmudinas]

Hi, is were a way to get FromSql working if I don't have Id or identity column ?

[mikes-gh]

Using ExecuteSQLCommand

Whats the best way of getting the value of an SqlParameter of type System.Data.ParameterDirection.Output as it stands. Do I need to go back to SqlCommand

[rowanmiller]

@mikes-gh - yes, for the moment.

[tessSnap]

How do we return an output value ? For example I have a stored procedure that returns 0 if everything goes well and 1 if an error occurs . Based on @mikes-gh I think SqlCommand is the only way out for now or ?

[divega]

@tessSnap Yes, dropping down to ADO.NET is going to be the solution until we remove this limitation.

[mikes-gh]

For those of you using SQL command I found this useful to get a connection string from existing context.

someContext.Database.GetDbConnection().ConnectionString

then use a new connection .

I tried to borrow context connection and cast to SQLConnection for my command but doing that breaks the context you borrowed from.

[prasannapattam]

This code is working for me. I am using Connection object.

        SqlConnection connection = (SqlConnection)dbContext.Database.GetDbConnection();
        connection.Open();
        SqlCommand command = new SqlCommand("dbo.GetQuarrySummary @CompanyId, @StartDate, @EndDate", connection);

        command.Parameters.Add(CreateParameter(command, "@CompanyId", DbType.Int32, profile.CompanyId));
        command.Parameters.Add(CreateParameter(command, "@StartDate", DbType.DateTime, search.StartDate));
        command.Parameters.Add(CreateParameter(command, "@EndDate", DbType.DateTime, search.EndDate));

        SqlDataReader reader = await command.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection);

[mikes-gh]

Yes that works. But subtly if you try to use the context afterwards it is broken. Hence my message.

[prasannapattam]

Have you enabled Multiple Active Result Sets (MARS).
https://msdn.microsoft.com/en-us/library/h32h3abf(v=vs.110).aspx

In my code, I was using the context after my SP and it is working (without enabling MARS). You need to close the connection after your initial query.
Here is the code I am using

        SqlConnection connection = (SqlConnection)dbContext.Database.GetDbConnection();
        connection.Open();
        SqlCommand command = new SqlCommand("dbo.GetQuarrySummary @CompanyId, @StartDate, @EndDate", connection);

        command.Parameters.Add(CreateParameter(command, "@CompanyId", DbType.Int32, profile.CompanyId));
        command.Parameters.Add(CreateParameter(command, "@StartDate", DbType.DateTime, search.StartDate));
        command.Parameters.Add(CreateParameter(command, "@EndDate", DbType.DateTime, search.EndDate));

        SqlDataReader reader = await command.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection);

        while (await reader.ReadAsync())
        {
            //code here
        }

        reader.Close();  //this closes the connection
        YardEntity entity = await (from yd in dbContext.Yards where yd.YardId == 15 select yd).SingleAsync();

[jvelezc]

I had to resort to SQLCommand. In EF6 I use to enjoy dbCtx.Database.SqlQuery("select... blah from...

[prasannapattam]

@jvelezc - you can still do the dynamic sql query as shown below

c# var actual = await context.Set<Customer>() .FromSql(@"SELECT * FROM ""Customers"" WHERE ""ContactName"" LIKE '%z%'")

[jvelezc]

My problem is that Customer has to be DbSet (an entity). In the past I was able to create a viewmodel that was not part of DbSet on dbcontext class and return it.

[prasannapattam]

Entity Framework needs to create an object for you, hence you need to define your ViewModel as DbSet (there is no requirement of having a table with the same name in the database).

If you are looking for a dynamic object, please vote for this enhancement #2344

[jvelezc]

So... If I do where announcement is an entity

public DbSet<Announcement> Announcement { get; set; }

   var _dbCtx = GetDbContext();
            var t = _dbCtx.Set <Announcement>()
.FromSql(@"SELECT AnnouncementId, BeginDate, EndDate, IsUrgent, Note FROM Announcement").ToArray();

Everything works as expected. However!

If I do a ViewModel and for simplicity sake I will make it exactly equal to announcement just to show that the only difference is the type.

  public DbSet<AnnouncementViewModel> AnnouncementViewModel { get; set; }

and then
builder.Ignore(); //purpose of this line is not to create a table in the back end

Then

 var _dbCtx = GetDbContext();
            var t = _dbCtx.Set <Announcement>().FromSql(@"SELECT AnnouncementId, BeginDate, EndDate, IsUrgent, Note FROM Announcement").ToArray();

Creates an exception Value cannot be null.Parameter name: entityType

If I then remove builder.Ignore(); and re tun the test after having updated to the latest model changes and adding a [key] attribute to annoucement viewmodel then it works. However, it created a table in my database.

So ... how do you do it so that you can use viewmodels FromSQl('') and not have to register in the database.

[prasannapattam]

Are you using EnsureCreated() to create tables from code. If yes, then you need to use builder.Ignore(). Otherwise you don't need to use Ignore. You still need to declare a Key for your ViewModel, but you don't need the Table attribute.

I am using FromSql to execute a stored procedure and here is my code for this

``` c#
//defining the entity class
public class ProductSummaryEntity
{
[Key]
public long RowId { get; set; }
public int ProductTypeId { get; set; }
public string ProductTypeName { get; set; }
public int QuarryId { get; set; }
public string QuarryName { get; set; }
public int MaterialCount { get; set; }
}

My DbContext

``` c#
    public class ApplicationDbContext : IdentityDbContext<ApplicationUser, ApplicationRole, string>
    {
           // rest of DbSet objects
           public DbSet<ProductSummaryEntity> ProductSummary { get; set; }
     }

c# //calling the stored proc return await dbContext.Set<ProductSummaryEntity>().FromSql("dbo.ProductSummaryGet @CompanyId = {0}, @QuarryIds = {1}, @ProductTypeIds = {2}, @StartDate = {3}, @EndDate = {4}" , profile.CompanyId, quarryIds, productTypeIds, search.StartDate, search.EndDate ).Select(m => Mapper.Map<ProductSummaryEntity, ProductSummaryModel>(m)).ToListAsync();

[divega]

@jvelezc Note that this issue is only about the ability to pass database provider DbParameters to raw SQL queries.

We are using a separate issue in the backlog (https://github.com/aspnet/EntityFramework/issues/1862) to track the ability to use ad-hoc queries to materialize types that are not part of the model directly.

In the meanwhile the approach explained by @prasannapattam can be used as a workaround, but it implies that there is an entity type from which you will later project your view model or DTO, and from the perspective of EF such entity has to map to a table so EnsureCreated() and generated migrations will always try to create the table for it. Having a type in the model that is not mapped to an actual table is covered in other issues.

[mikes-gh]

Thanks for this :clap:
Any chance of a quick usage sample using in and out params.
Maybe from your test code.

[mikary]

Two good places to look for examples of ExecuteSqlCommand and FromSql are in the SqlExecutorTestBase and FromSqlQueryTestBase tests.

A simplified example of SqlExecutorTestBase.Executes_stored_procedure_with_parameter for SQL Server would be something like:

using (var context = new NorthwindContext())
{
    var parameter = new SqlParameter
    {
        ParameterName = "@CustomerID",
        Value = "ALFKI"
    }

    context.Database.ExecuteSqlCommand("[dbo].[CustOrderHist] @CustomerID", parameter)
}

Likewise FromSqlQueryTestBase.From_sql_with_dbParameter for SQL Server could be written more like:

using (var context = new NorthwindContext())
{
    var parameter = new SqlParameter
    {
        ParameterName = "@City",
        Value = "London"
    }

    var customers = context.Customers
        .FromSql(@"SELECT * FROM ""Customers"" WHERE ""City"" = @city", parameter)
        .ToArray();
}

There isn't an example of an output dbParameter in the test code, but the pattern should be fairly similar with appropriate stored procedures on the server.

There are also some tests in FromSqlSprocQueryTestBase where values from a stored procedure are materialized into CLR types that were built to align with the stored procedure output (Note: these types aren't part of the model, so updates/saving isn't supported).

[mikes-gh]

Thanks appreciate that

[prasannapattam]

Thanks for the named parameters. In addition can you also implement the return as dynamic or ExpandoObject, so that there is no need to predefine the return type.

[Antaris]

@rowanmiller @divega

Can you offer up a example of using a table-valued parameter for ExecuteStoreCommand? The DataTable APIs haven't been fleshed out for .NET Standard yet :-/

[divega]

@Antaris you can still use an IEnumerable<SqlDataRecord>. It is not as convenient to use as a DataTable so I hacked together a small builder. Look at this gist.

[Antaris]

@divega YOU ARE THE MAN!! I was having to only support a particular operation for net46 but now can support across the board!

[divega]

Glad it helped! :smile: