Entity Framework 6 Mixed stored procedure and SQL query generation

Question

Reading EF Documentation on CodePlex I understand that it is currently impossible to use it as a mixture of stored procedures and generating SQL queries for a persistent change domain model.

However, there are many reasons why I find it disappointing that it does not allow me to use a mixture of both when its predecessor, Linq 2 SQL, allowed me to do this.

I have a complicated script to save my domain model in multiple tables in a database, which is why I want to use a stored procedure to insert. The reason I don’t want to use the stored procedure for updates is because there is no clean way to start updating only for the affected columns — parsing all the values ​​in update sproc. I would need to update all the fields for this entry (unless I select all the old values ​​for comparison or include the fields in the predicate clause of the UPDATE statement - sort of randomly in both directions). This is a problem due to the fact that triggers are tied to a table in which individual fields are checked for updates, it is rather a problem of an outdated system, so we will not go along the path of the subjective question of whether to use triggers.being a good design.

Passing all fields to the sproc update, even if they have not been changed, is not necessary and can facilitate network traffic, while EF knows which fields are explicitly updated when using the SQL query generation mode.

Are there any add-ons for entity structures that allow me to use a mix of stored procedure mappings for inserts and then generate SQL queries for updates for the constant model?

For my database, I specifically use SQL Server, but I believe that a suitable add-on will not be associated with my selected data warehouse.

Answer 1

( ), .

, :

  List<Ekipa> result = new List<Ekipa>();
        DataSet ds = new DataSet();
        using (UsersContext uc = new UsersContext())
        {
            using (SqlConnection con = new SqlConnection(uc.Database.Connection.ConnectionString))
            {
                con.Open();

                SqlCommand com = new SqlCommand("procedurename", con);
                com.CommandType = CommandType.StoredProcedure;
                 command.Parameters.AddWithValue("@id", 12);
                using (SqlDataAdapter da = new SqlDataAdapter(com))
                {
                    da.Fill(ds);
                }
            }
        }

        result = (from myRow in ds.Tables[0].AsEnumerable()
                  select new Ekipa()
                  {
                      UserId = myRow.Field<int>("UserId"),
                      UserName = myRow.Field<string>("UserName"),
                      Bodovi = myRow.Field<int>("Bod"),
                      OU = myRow.Field<int>("OU"),
                      Gol = myRow.Field<int>("PostignutiGolovi"),
                  }).ToList();

MultipleActiveResult

<add name="DefaultConnection" connectionString="Data Source=**.**.***.***;Initial Catalog=**;User ID=**_DBUser; Password=***; MultipleActiveResultSets=true; " providerName="System.Data.SqlClient" />

Answer 2

. DbSet SqlQuery, SELECT ( ). Store. : https://msdn.microsoft.com/en-us/data/jj592907.aspx, :

using (var context = new BloggingContext()) 
{ 
    var blogs = context.Blogs.SqlQuery("dbo.GetBlogs").ToList(); 
}

:

db.RegistroSet.SqlQuery("DELETE FROM RegistroSET; SELECT * FROM RegistroSet").ToList();
int c = db.RegistroSet.Count();

... 200.000 . , . SELECT . ToList() , .