Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support passing DbParameter instances to raw SQL APIs #3115

Closed
Vasimovic opened this issue Sep 12, 2015 · 27 comments

Comments

Projects
None yet
@Vasimovic
Copy link

commented Sep 12, 2015

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

This comment has been minimized.

Copy link
Member

commented Sep 18, 2015

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.

@rowanmiller rowanmiller added this to the 7.0.0 milestone Sep 18, 2015

@tuespetre

This comment has been minimized.

Copy link
Contributor

commented Sep 22, 2015

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

@prasannapattam

This comment has been minimized.

Copy link

commented Oct 2, 2015

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

@vmudinas

This comment has been minimized.

Copy link

commented Oct 13, 2015

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

@rowanmiller rowanmiller changed the title Need to pass "named" parameters in FromSql() Support passing DbParameter instances to raw SQL APIs Dec 7, 2015

@mikes-gh

This comment has been minimized.

Copy link

commented Dec 9, 2015

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

This comment has been minimized.

Copy link
Member

commented Dec 9, 2015

@mikes-gh - yes, for the moment.

@rowanmiller rowanmiller modified the milestones: 7.0.0-rc2, 7.0.0 Dec 9, 2015

@tessSnap

This comment has been minimized.

Copy link

commented Dec 9, 2015

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

This comment has been minimized.

Copy link
Member

commented Dec 9, 2015

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

@rowanmiller rowanmiller modified the milestones: 7.0.0, 7.0.0-rc2 Dec 10, 2015

@mikes-gh

This comment has been minimized.

Copy link

commented Dec 13, 2015

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 comment has been minimized.

Copy link

commented Dec 14, 2015

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

This comment has been minimized.

Copy link

commented Dec 14, 2015

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

@prasannapattam

This comment has been minimized.

Copy link

commented Dec 14, 2015

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

This comment has been minimized.

Copy link

commented Jan 8, 2016

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

@prasannapattam

This comment has been minimized.

Copy link

commented Jan 12, 2016

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

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

This comment has been minimized.

Copy link

commented Jan 12, 2016

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

This comment has been minimized.

Copy link

commented Jan 12, 2016

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

This comment has been minimized.

Copy link

commented Jan 12, 2016

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

This comment has been minimized.

Copy link

commented Jan 13, 2016

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

//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

    public class ApplicationDbContext : IdentityDbContext<ApplicationUser, ApplicationRole, string>
    {
           // rest of DbSet objects
           public DbSet<ProductSummaryEntity> ProductSummary { get; set; }
     }
//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

This comment has been minimized.

Copy link
Member

commented Jan 13, 2016

@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 (#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.

mikary added a commit that referenced this issue Feb 16, 2016

Fix #3704 - Explicit seperation of command construction and execution
Fix #3115 - Support passing DbParameter instances to ExecuteSqlCommand
and FromSql

@mikary mikary added the 2 - Done label Feb 19, 2016

mikary added a commit that referenced this issue Feb 22, 2016

Fix #3704 - Explicit seperation of command construction and execution
Fix #3115 - Support passing DbParameter instances to ExecuteSqlCommand
and FromSql

mikary added a commit that referenced this issue Mar 2, 2016

Fix #3704 - Explicit separation of command construction and execution
Fix #3115 - Support passing DbParameter instances to ExecuteSqlCommand
and FromSql
Fix #2315 - Improve FromSql command caching

mikary added a commit that referenced this issue Mar 9, 2016

Fix #3704 - Explicit separation of command construction and execution
Fix #3115 - Support passing DbParameter instances to ExecuteSqlCommand
and FromSql
Fix #2315 - Improve FromSql command caching

mikary added a commit that referenced this issue Mar 11, 2016

Fix #3704 - Explicit separation of command construction and execution
Fix #3115 - Support passing DbParameter instances to ExecuteSqlCommand
and FromSql
Fix #2315 - Improve FromSql command caching

@mikary mikary closed this in #4575 Mar 11, 2016

@mikary mikary modified the milestones: 1.0.0-rc2, 1.0.0 Mar 11, 2016

@mikes-gh

This comment has been minimized.

Copy link

commented Mar 11, 2016

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

@mikary

This comment has been minimized.

Copy link
Contributor

commented Mar 11, 2016

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

This comment has been minimized.

Copy link

commented Mar 11, 2016

Thanks appreciate that

@prasannapattam

This comment has been minimized.

Copy link

commented Mar 12, 2016

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

This comment has been minimized.

Copy link

commented Oct 13, 2016

@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

This comment has been minimized.

Copy link
Member

commented Oct 13, 2016

@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

This comment has been minimized.

Copy link

commented Oct 14, 2016

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

@divega

This comment has been minimized.

Copy link
Member

commented Oct 14, 2016

Glad it helped! 😄

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.