none
Stored Procedures without a complete mapping to EF tables RRS feed

  • Question

  • Good Morning Folks;

    I am using CRUD stored procedures toa carry out changes to my data.  I see how you are supposed to map the parameters to the EF table's fields.  My question comes because I have an extra field in the Stored Procedure that does not map to the table reflecting the userRecID of the person making the change.  (That is because this has to be stored in a tracking table in the database.)

    However, when I tried to set the mapping I would get errors because there was not a one to one correspondence between the parameters and the columns. 

    How do I get around this?


    Edward R. Joell MCSD MCDBA

    Monday, February 24, 2014 5:36 PM

Answers

  • But again.  why map it at all if I can't just do the below code ?

    prc_Requirement.Add(model.requirement)

    In the code both of you referenced,  and in the stored procedure complex object which I demonstrated with my HttpPost Edit method,

     [HttpPost]
            [ValidateAntiForgeryToken]
            public ActionResult Create(RequirementSummaryViewModel ReqView)
            {
               if (ModelState.IsValid)
                {
                    int userRecID = (int)Session["UserRecID"];
                    _db.Requirement_Insert(ReqView.Requirement.ReqNum, 
                                            ReqView.Requirement.RequistionNumber, 
                                            ReqView.Requirement.FiscalYear, 
                                            ReqView.Requirement.DoDIC, 
                                            ReqView.Requirement.PPN, 
                                            ReqView.Requirement.NSN, 
                                            ReqView.Requirement.Download, 
                                            ReqView.Requirement.CustomerRDD, 
                                            ReqView.Requirement.ServiceID, 
                                            ReqView.Requirement.Import_CreateDate, 
                                            ReqView.Requirement.Quantity, 
                                            ReqView.Requirement.StatusID, 
                                            ReqView.Requirement.CC, 
                                            ReqView.Requirement.CustomerID, 
                                            ReqView.Requirement.AmsID, 
                                            ReqView.Requirement.Remarks, 
                                            ReqView.Requirement.ReportRemarks, userRecID);
                    _db.SaveChanges();
                    return RedirectToAction("Index");
                }
                return View(ReqView);
            }

    I just have to call the complex object representing stored procedure _db.Requirement_Insert( model.prop1, Model.prop2, etc)   without doing any mapping to the table at all.  If you are going to just call the stored procedure anyway why are you bothering with the mapping?  It is its own object with a list of parameters and if you call it, it will run and do as it has been instructed.  No mapping required. 

    So I don't understand why you are mapping it, then ignoring the mapping.

    And since I am calling the stored procedure directly I don't even have to do a _db.SaveChanges(); because the stored procedure is taking care of all the writes to the database.

    Hoever, on each post, I am doing a write to the database.  What I was hoping for is the ability to save the contents of the model into the table object in memory without doing the write to the database until later.  While this is not so important for this model, in my later models to be designed in this project I will be wanting to write add multiple records to the Entity and making the write to the database in one trip.  That is what we could do with our custom built objects. 

    Now I am probably going to have to make a collection or List of these subordinate objects and add that to a custom object representing the main table's record and all of its associated records to save this in memory then call one stored procedure once and the other multiple times while iterating through the list which can amount to making over 100 calls to the database in order to store one contract record. 

    Edward R. Joell MCSD MCDBA



    • Edited by joeller Thursday, February 27, 2014 8:01 PM
    • Marked as answer by joeller Thursday, March 6, 2014 3:45 PM
    Thursday, February 27, 2014 7:43 PM

All replies

  • Hello,

    >>How do I get around this?

    If we open the .edmx file with XML Editor, we can see that it needs the property type rather than the property name.

    So choice a property type which the extra field is to mapped to the extra field. Here I made an example:

    My SP, it has an extra field:

    CREATE PROCEDURE [dbo].[ProUpdateOrder]
    
           @param1 int,
    
           @param2 varchar(50),
    
           @param3 varchar(50),
    
           @param4 varchar(50)--extra field
    
    AS
    
           update [Order] set OrderCode = @param2,OrderName= @param2 where OrderID = @param1
    
    return 0
    

    Since I defined it to be varchar type, I choice a string type property to map it:

    I called it as:

    using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())
    
                {
    
                    db.UpdateOrder(1, "2", "3", "4");
    
                }
    

    And it could successfully pass the “4” to store produce.

    If this does not work for you, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, February 25, 2014 5:09 AM
    Moderator
  • Thank you for your reply;

    However I apparently did not make myself clear.  I am already calling a number of imported stored procedures in my code through the db context.  This is not the issue.

    In this MSDN article http://msdn.microsoft.com/en-us/data/gg699321.aspx in the section entitled "Mapping Modification Stored Procedures to Entities"

    It states:

    Now you’ll modify the model so that the Entity Framework will use the UpdateOrder stored procedure defined in the database instead of generating its own command. You’ll do this by mapping the UpdateOrder stored procedure to the Order entity in the designer. This is referred to as Stored Procedure Mapping or Function Mapping.

    1. In the designer, right click on the Order entity and select Stored Procedure mapping.
    2. Click select Function="" Insert="" and then click the drop down arrow that appears. This exposes the list of all Functions found in the Store metadata.
    3. Select InsertOrder from the list. The designer will do its best job of matching the stored procedure’s parameters with the entity properties using the names. In this case, since all of the property names match the parameter names, it maps every one correctly so you don’t need to make any changes. One thing that the designer was not able to map is the newly generated SalesOrderID returned by the stored procedure. This is because the designer is not able to automatically detect the name of the field being returned.
    4. Under the Result Column Bindings section, click add Binding="" Result="" and enter SalesOrderID. The designer should automatically select the entity key property, OrderID, for this final mapping.

    When completed, Figure 6 displays what the mapping should look like.

    However when I follow this process because my stored procedure contains an extra parameter I get the error, "Error 2037: A mapping function bindings specifies a function VFS_ProcurementTrackModel.Store.prc_ext_Requirement_Insert but does not map the following function parameters: UserRecID."

    This has me stymied in attempting to "map" the prc_ext_Requirement_Insert stored procedure to the prc_Requirements Entity.

    How do I get around this?


    Edward R. Joell MCSD MCDBA



    • Edited by joeller Tuesday, February 25, 2014 2:47 PM removal of automagically generated html elements
    Tuesday, February 25, 2014 2:44 PM
  • >> However when I follow this process because my stored procedure contains an extra parameter

    Please share us with your store procedure.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, February 26, 2014 9:35 AM
    Moderator
  • Here it is.

    USE [VFS_ProcurementTrack]
    GO
    /****** Object:  StoredProcedure [dbo].[prc_ext_Requirement_Insert]    Script Date: 02/26/2014 08:50:46 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    /****************************************************************************************************************************************************************
    **		Name:  prc_ext_Requirement_Insert 
    **		Desc: Stored procedure to insert New Requirements and update change tracking table. 
    		
    ****              
    **		Return values:  ReqID
    ** 
    **		Called by:  VFS/Procurement Tracking Web App.
    **              
    **		Parameters:
    **		Input									Output
    		@ReqNum varchar(25),
    		@RequisitionNumber varchar(17) = null,
    		@FiscalYear varchar(4),
    		@DoDIC varchar(5) = null,
    		@PPN varchar(20) = null,
    		@NSN varchar(20) = null,
    		@Download bit = 0,
    		@CustomerRDD date = null,
    		@ServiceID int = null,
    		@ImportCreateDate date =null,
    		@Quantity int = null,
    		@StatusID int,
    		@CC int,
    		@CustomerID int,
    		@AmsID int,
    		@Remarks varchar(max),
    		@ReportRemarks varchar(max),
    		@UserRecID int	
    			-----------
    **
    **		Auth: E.R. Joell
    **		Date: 01/20/2014
    ***************************************************************************************************************************************************************
    **		Change History
    ****************************************************************************************************************************************************************
    **		Date:			Author:				Description:
    **		--------			--------			---------------------------------------------------------------------------------
    **
    ****************************************************************************************************************************************************************/
    ALTER PROCEDURE [dbo].[prc_ext_Requirement_Insert] 
    (
    	@ReqNum varchar(25),
    	@RequisitionNumber varchar(17) = null,
    	@FiscalYear varchar(4),
    	@DoDIC varchar(5) = null,
    	@PPN varchar(20) = null,
    	@NSN varchar(20) = null,
    	@Download bit = 0,
    	@CustomerRDD date = null,
    	@ServiceID int = null,
    	@ImportCreateDate date =null,
    	@Quantity int = null,
    	@StatusID int,
    	@CC int,
    	@CustomerID int,
    	@AmsID int,
    	@Remarks varchar(max),
    	@ReportRemarks varchar(max),
    	@UserRecID int
    )
    AS
    BEGIN
    	Declare @ReqID int
    	Declare  @ChangesTracker Table ( ReqID int,
    						   ChangeTypeID int,
    						   FieldName varchar(50),
    						   ChangeDateTime datetime, 
    						   ContentsAfter varchar(150),
    						   ChangerUserRecID int)
    						   
    	Insert Into prc_Requirements(ReqNum, 
    								 RequistionNumber,
    								 FiscalYear, 
    								 DoDIC,
    								 PPN,
    								 NSN,
    								 Download,
    								 CustomerRDD,
    								 ServiceID,
    								 [Import/CreateDate],
    								 Quantity,
    								 StatusID,
    								 CC,
    								 CustomerID,
    								 AmsID,
    								 Remarks,
    								 ReportRemarks)
    	Values (@ReqNum,
    			@RequisitionNumber,
    			@FiscalYear,
    			@DoDIC,
    			@PPN,
    			@NSN,
    			@Download,
    			@CustomerRDD,
    			@ServiceID,
    			@ImportCreateDate,
    			@Quantity,
    			@StatusID,
    			@CC,
    			@CustomerID,
    			@AmsID,
    			@Remarks,
    			@ReportRemarks)
    			
    	Set @ReqID = SCOPE_IDENTITY()
    	
    	Insert into @ChangesTracker(ReqID,
    							    ChangeTypeID,
    							    FieldName,
    							    ChangeDateTime, 
    							    ChangerUserRecID)
    	Select @ReqID, 1, c.name, GETDATE(), @UserRecID
    	FROM sys.all_columns c JOIN sys.all_objects o on c.object_id = o.object_id
    	where o.name = 'prc_Requirements' and c.name <> 'ReqID'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @ReqNum
    	Where FieldName = 'ReqNum'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @RequisitionNumber
    	Where FieldName = 'RequistionNumber'
    		
    	Update @ChangesTracker 
    	Set ContentsAfter = @FiscalYear
    	Where FieldName = 'FiscalYear'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @DoDIC
    	Where FieldName = 'DoDIC'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @PPN
    	Where FieldName = 'PPN'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @NSN
    	Where FieldName = 'NSN'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @Download
    	Where FieldName = 'Download'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @CustomerRDD
    	Where FieldName = 'CustomerRDD'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @ServiceID
    	Where FieldName = 'ServiceID'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @ImportCreateDate
    	Where FieldName = 'Import/CreateDate'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @Quantity
    	Where FieldName = 'Quantity'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @StatusID
    	Where FieldName = 'StatusID'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @CC
    	Where FieldName = 'CC'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @CustomerID
    	Where FieldName = 'CustomerID'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @AmsID
    	Where FieldName = 'AmsID'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @Remarks
    	Where FieldName = 'Remarks'
    	
    	Update @ChangesTracker 
    	Set ContentsAfter = @ReportRemarks
    	Where FieldName = 'ReportRemarks'
    	
    	Delete from @ChangesTracker Where ContentsAfter is null	
    	
    	Insert into prc_RequirementChanges(ReqID,
    										ChangeTypeID,
    										FieldName,
    										ChangeDateTime,
    										ContentsAfter, 
    										ChangerUserRecID)
    	SELECT * From @ChangesTracker	
    	
    	Select @ReqID ReqID					 
    END

    I don't see how that helps you though.


    Edward R. Joell MCSD MCDBA

    Wednesday, February 26, 2014 1:52 PM
  • I am very interested in this response. In our database, when the user logs in, he is given a session token. It is a requirement that all access to the database be through stored procedures. Every stored procedure takes a session token as a parameter and this session token is validated at the beginning of the procedure before processing continues. This token is also used for tracking purposes. However, most of the tables in the database do not have a session token column and therefore, in Entity Framework, when mapping stored procedures to functions there is nothing to map this parameter to. 

    GregP

    Wednesday, February 26, 2014 2:34 PM
  • Hi Greg;

    I've got a sneaking suspicion that either we will have to make custome models in the EF which will handle all of the CRUDs using Complex objects, or scrap EF altogether and just use "roll your own" models for our database objects. 

    However hope springs eternal.  I wll submit a similar post on Experts-Exchange and if I hear anything I will let you know.


    Edward R. Joell MCSD MCDBA

    Wednesday, February 26, 2014 3:02 PM
  • It was my boss that "suggested" using Entity Framework because he went to some conference and now thinks it's the greatest thing ever. Generally, I don't like using frameworks like these because they end up doing 90% of what you want, but getting them to do the last 10% ends up being so painful or convoluted that it isn't worth it. I'll be perfectly happy if there isn't a work around for this.

    GregP

    Wednesday, February 26, 2014 3:36 PM
  • Hello joeller,

    Thanks for sharing the store procedure. From the store procedure, if I understand correctly, you may have an entity which contains all parameters except the UserRecID and in the model you have mapped all entity fields to parameters except UserRecID as:

    Then built it and it thrown an error like:

    “DataBaseFirstDBModel.Store.ProInsertOrder but does not map the following function parameters: extrafield.”

    Is it right?

    If my description is what you have done, then you just need to check my first post: choice a correspondent type because it actually use the type to map parameter with fileds.

    If I misunderstand, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 27, 2014 3:44 AM
    Moderator
  • Allow me to paraphrase so I understand. I think what you are suggesting is that when mapping stored procedures to CUD functions, map any extra parameters in the stored procedure to some other property in the model with the same underlying datatype. Correct?

    I tried this and it seems to work and propbably will work as long as there is a property in the model that has a similar datatype to any extra stored procedure parameters.

    If this does prove to work, it is obviously a hack. Can you tell me if there will be support for this situation in the future? This does not seem to be an uncommon scenario.

    Thanks.


    GregP

    Thursday, February 27, 2014 3:08 PM
  • If I understand you correctly it seems that you've got the picture.

    choice a correspondent type because it actually use the type to map parameter with fileds.
    Since I defined it to be varchar type, I choice a string type property to map it:

    I don't know what you are trying to say.
    All you did above was to map one property of the table object to two different parameters.
    This would not resolve my issue because all I would be doing is passing an irrelevant field into the stored procedure upon calling:

    _db.prc_Requirements.Add(ReqView.Requirement);

    all I would be doing is passing the wrong value into the stored procedure.

    But in your code you are doing the equivalent of

               if (ModelState.IsValid)
                {
                    int userRecID = (int)Session["UserRecID"];
                    _db.Requirement_Insert(ReqView.Requirement.ReqNum, 
                                            ReqView.Requirement.RequistionNumber, 
                                            ReqView.Requirement.FiscalYear, 
                                            ReqView.Requirement.DoDIC, 
                                            ReqView.Requirement.PPN, 
                                            ReqView.Requirement.NSN, 
                                            ReqView.Requirement.Download, 
                                            ReqView.Requirement.CustomerRDD, 
                                            ReqView.Requirement.ServiceID, 
                                            ReqView.Requirement.Import_CreateDate, 
                                            ReqView.Requirement.Quantity, 
                                            ReqView.Requirement.StatusID, 
                                            ReqView.Requirement.CC, 
                                            ReqView.Requirement.CustomerID, 
                                            ReqView.Requirement.AmsID, 
                                            ReqView.Requirement.Remarks, 
                                            ReqView.Requirement.ReportRemarks, userRecID);
                    _db.SaveChanges();
                    return RedirectToAction("Index");
                }

    Which is essentially what I would be doing with a ADO.Net command so there is then no benefit at all to having an Entity Framework.  Just a lot of extra work for nothing.

    In fact I could design a set of customer business objects which do the same thing with less work than maintaining the EF.


    Edward R. Joell MCSD MCDBA

    Thursday, February 27, 2014 3:12 PM
  • Edward, give it a try. I think what he is suggesting is to get around the build error is to just map the extra column to some other column. When the code is generated, it will create a method with a parameter list where you can pass any values you like.

    For example, you have a Person table with FirstName and LastName columns and then have a stored procedure called usp_insertPerson with three parameters @firstName, @lastName, and @trackingToken.

    When you generate the model you will have a Person model with FirstName and LastName properties. Then map the usp_insertPerson procedure to the insert function of the model. As part of that process map the sessionToken parameter to LastName or FirstName. When the code is generated you will end up with an InsertPerson method on the data context that takes three parameters:

    var context = new DatabaseContext();

    context.InsertPerson("Abe", "Lincoln", "trackingToken");

    This should correctly pass the values to the usp_insertPerson stored procedure where you do whatever work you need to do. The key is that the type of the @sessionToken parameter must match the type of one of your other parameters. If it doesn't then I think you're out of luck or have to come up with something else.

    This certainly does not constitute proper support for this situation, but I think it will work without a bunch of extra work.


    GregP

    Thursday, February 27, 2014 4:11 PM
  • But again.  why map it at all if I can't just do the below code ?

    prc_Requirement.Add(model.requirement)

    In the code both of you referenced,  and in the stored procedure complex object which I demonstrated with my HttpPost Edit method,

     [HttpPost]
            [ValidateAntiForgeryToken]
            public ActionResult Create(RequirementSummaryViewModel ReqView)
            {
               if (ModelState.IsValid)
                {
                    int userRecID = (int)Session["UserRecID"];
                    _db.Requirement_Insert(ReqView.Requirement.ReqNum, 
                                            ReqView.Requirement.RequistionNumber, 
                                            ReqView.Requirement.FiscalYear, 
                                            ReqView.Requirement.DoDIC, 
                                            ReqView.Requirement.PPN, 
                                            ReqView.Requirement.NSN, 
                                            ReqView.Requirement.Download, 
                                            ReqView.Requirement.CustomerRDD, 
                                            ReqView.Requirement.ServiceID, 
                                            ReqView.Requirement.Import_CreateDate, 
                                            ReqView.Requirement.Quantity, 
                                            ReqView.Requirement.StatusID, 
                                            ReqView.Requirement.CC, 
                                            ReqView.Requirement.CustomerID, 
                                            ReqView.Requirement.AmsID, 
                                            ReqView.Requirement.Remarks, 
                                            ReqView.Requirement.ReportRemarks, userRecID);
                    _db.SaveChanges();
                    return RedirectToAction("Index");
                }
                return View(ReqView);
            }

    I just have to call the complex object representing stored procedure _db.Requirement_Insert( model.prop1, Model.prop2, etc)   without doing any mapping to the table at all.  If you are going to just call the stored procedure anyway why are you bothering with the mapping?  It is its own object with a list of parameters and if you call it, it will run and do as it has been instructed.  No mapping required. 

    So I don't understand why you are mapping it, then ignoring the mapping.

    And since I am calling the stored procedure directly I don't even have to do a _db.SaveChanges(); because the stored procedure is taking care of all the writes to the database.

    Hoever, on each post, I am doing a write to the database.  What I was hoping for is the ability to save the contents of the model into the table object in memory without doing the write to the database until later.  While this is not so important for this model, in my later models to be designed in this project I will be wanting to write add multiple records to the Entity and making the write to the database in one trip.  That is what we could do with our custom built objects. 

    Now I am probably going to have to make a collection or List of these subordinate objects and add that to a custom object representing the main table's record and all of its associated records to save this in memory then call one stored procedure once and the other multiple times while iterating through the list which can amount to making over 100 calls to the database in order to store one contract record. 

    Edward R. Joell MCSD MCDBA



    • Edited by joeller Thursday, February 27, 2014 8:01 PM
    • Marked as answer by joeller Thursday, March 6, 2014 3:45 PM
    Thursday, February 27, 2014 7:43 PM
  • It seems as if you have come up with your own way around the problem by using a complex object. The distinction between your method and the "mapping" method is that you had to create a complex object for your method to work and using the "mapping" method you do not need the complex object.

    Like most problems, it seems as if multiple approaches to solve it are available. So it is up to you to choose which approach works best for you.

    In my opinion, I don't consider either approach to be proper support for the problem we are trying to solve. Proper support would be to have the ability to not have to map the extra parameter of a stored procedure to a property in the model, but still be able to pass data to the required "extra" parameters of that procedure.


    GregP

    Thursday, February 27, 2014 8:50 PM
  • Absofragginglutely

    But it is what it is.

    The complex Object is not really difficult. 

    1. Right Click on the entity Framework model page, and click Update From Database.
    2. In the Add panel of the wizard, expand stored procedures and schema and select the stored procedure you want.
    3. After pushing the OK buttons open the model browser and there are the store procedures you selected.  By default the wizard also creates an imported function with the same name and a complex object with the same name plus "_result" for the ones that return a result set.  I don't like those names because of the naming conventions we are required to use in our databases.  So I delete the imported function and created Complex objects then I right click on the stored procedure in the model.store section of the Model Browser and click add Function Import.  I rename the stored procedure the way I want to see it in the database context, I click get columns button then i click the create complex object button and the returns radio button changes to complex object and shows the newly created complex object with the import name that you chose above plus "_Result".
    4. For those stored procedures that don't return a result set then just leave the radio button set ton none and rename the imported  function to the name you want.  My Insert after it carries out the insert into the table and the tracking table returns the new record ID. However, at this point it time I am not seeing any need for it.

    Like I said before, the main issue is that I can't just use Object.Add to execute the SP.  I am still not familar with all the intricities of the Entity Framework, but it appears to me that the that if you do execute the Object.Add(model) then if it were mapped to a stored procedure it would have to make a trip to the database to execute this stored procedure which would still defeat the purpose of saving all my changes in the EF until I was ready to do one big massive save.  So I figure I will still end up designing custom business objects to call the stored procedures all at once all the updates have been made.


    Edward R. Joell MCSD MCDBA

    Thursday, February 27, 2014 9:18 PM
  • Thanks for the info. Going back to what I said earlier, I'm really struggling to figure out what using this framework is going to do for me. By the time I figure out how this thing is supposed to work, I could have written all the code for my own solution. With rolling my own, at least that way I know exactly what the code is doing.

    Another thing to consider is what happens when you get a new version of this thing? I have already been reading posts about issues upgrading from previous versions of EF to the latest version. How much time are people wasting trying to figure that stuff out? What is the compelling reason to use this thing? 


    GregP

    Thursday, February 27, 2014 9:56 PM
  • Now you've touched upon a very sore point with me and a big beef I have with Microsoft.  If you don't care about my opinion of this I would suggest you don't bother reading the rest of this.

    Having made this disclaimer, the way I see it, their logic is to get everyone using Object Oriented Programming for accessing data.  Apparently they did not consider the ADO.Net set of database objects sufficiently object oriented.  (I kind of liked the idea of having a little mini database in memory with its own set of relationships that did not get saved back to the database until everything was complete. )   In addition, their move to establishing data objects did much the same without without using up the resources eaten up by datasets.

    I also don't understand why they went away from their various data controls (although they wouldn't work in MVC anyway), because they also had built into them all the needed selects, deletes, inserts and updates to carry out any operation.  But I suppose it was because they were starting to to infect the presentation layer with code that belonged in the Data Access Layer. 

    I remember in 1.1 unlike classic Asp, the markup page was all and only about markup even if those markups were calling server controls, the code for actually filling in and doing anything with those server controls was in the code behind. although we had something called Page Objects in classic Asp which started approaching the idea of seaparation of code from markup, but a lot of that ended up mixing markup in the code section by generating markup that was Response.written  I used to advocate to our web designers that all they needed to do was to learn to use the drag and drop interface to design attractive web pages and we programmers would write the code to provide the data to that page.  Then they started moving away from that and started moving back to mixing code up on the mark-up page so that designers would now either have to learn to program or find another line of work.  (one of the best designers I know was lost to us because she was not going to be a programmer. 

    Now in MVC they advertise that the views are all about presentations but if so then why is there some much C# code mixed up in it.  In going to EF, they were trying to create data objects they called models that people could use in programming with having to build their own.  But the latest iterations of EF now advocate what they call a code first approach, i.e. the developer builds his models of the actual objects that the web site needs to work and then updates the database to design a database based on those models.  As a DBA this offends me.  You have developers making decisions of the best data structure for the database based on their objects.  Now if we were to allow that we would be ending up with databases that barely make it out of the first normal form with tables full of muiltiple bit fields and showing what should be one - many joins in the database as multple columns in one table, and relating tables with multi-part foreign keys that consist of hand entered data.  (In the environment in which I am working we are still struggling with this legacy inflicted upon us some 17 years ago and some of the same programmers who designed those atrocities are the ones who would be building models to implement the code first EF). I believe that the database should be designed to efficiently store and retrieve data not to fit someone's ideas because they found it easier to think of it that way. You should then retrieve data from the database to populate the objects you want to use in your code.

    To put this in MVC terms youi would make a bunch of models then you would populate those models with queries to the database.  Then you would make queries against these models using something like LINQ to populate your views.  Every change you made in the view to the data would be stored in the models.  Then when you have made all of your changes to those models, and you are satisfied, you should call methods on the models that would then save your changes to the database.  I thought that is where they were going with EF which is why I've been spending time learning to use it.  Now I am not so sure.  I think I may redesign my application to work that way.


    Edward R. Joell MCSD MCDBA

    Thursday, February 27, 2014 11:20 PM
  • Okey, now I know that I completely misunderstand you, I assume that you would have add a function import which is mapped to the store procedure and use the function to do these operations and it is my mistake to map fields with parameters using function imports, it does not need, sorry for this.

    With ways like db.ImageTables.Add(newimage);, it requires an entity type and the fields of the entity should be completely mapped with the parameters of the store procedure.

    There is a solution but I do not recommend to use it because it needs us to modify the .edmx file and after change it, we cannot do the query operation unless we use another .edmx model.

    The solution is:

    In the model, find the table and add a scalar property and save the model like:

    In the Mapping Details, map the extra parameter to the new added scalar property.

    Open the .edmx model with XML editor and in SSDL content find the table declaration and add the extra field Property like:

    <Property Name="ExtraField" Type="int" />


    In CSL content, find the table declaration and add a ScalarProperty for the extra field like:

    <ScalarProperty Name="ExtraField" ColumnName="ExtraField" />

    Then we can add a new entity like below successfully and pass the correct value:

    ImageTable newimage = new ImageTable() { Id = 2, Picture = new Byte[] { }, ExtraField = 4 };
    
    db.ImageTables.Add(newimage);
    
    db.SaveChanges();
    

    Suggest you add the extra field to the table.

    Hope it to be helpful to you.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Fred BaoModerator Thursday, March 6, 2014 7:35 AM
    • Unmarked as answer by joeller Thursday, March 6, 2014 3:25 PM
    Friday, February 28, 2014 9:41 AM
    Moderator
  • Fred

    You marked this as an answer!? This isn't an answer, this is a total hack. I would never use something like this in production code. You must get some sort of credit for answering forum posts.


    GregP

    Thursday, March 6, 2014 2:24 PM
  • @Fred. Please do me the courtesy of allowing me to determine whether a reply answers my question.  After all it is MY question. 

    @Greg Please note the number of point beside Fred's name in his signature.  They do get points for being designated as the correct answerer.  If I were running the forum I would not allow the moderator that replied to the question to determine if that reply is the answer, if the orignal poster fails to indicate it.  I would also mandate a period of at least a month of inactivity before a moderator could determine an answer.  In far to many cases I've seen moderators post a reply on Friday after work, and then mark their reply as the answer on Monday morning at 1:00 AM.  Like the OP has had an opportunity to even read the post when they haven't been at work in that whole time.

    The SharePoint and SQL Server Forums are infamous for that.

    As far as the answer, it is my belief that calling the stored procedure explicitly like I am doing is the only answer so that in fact there really is no advantage in using Entity Framework except as a source of ready made table and stored procedure objects.


    Edward R. Joell MCSD MCDBA

    Thursday, March 6, 2014 3:37 PM
  • Fred,

    thanks for confirming that the only way around this is to employ some kind of hack. 

    I would suggest that rather than change table design, use a view as an entity thereby lessening the hackiness of adding a dummy column, i.e., your view would simply have '' as DummyColumn.  That way hard coded table design changes are not required. 

    although, I'm not sure why "overmapping" is not the best answer.  That is mapping a parameter to any column of the same type. 

    Microsoft really needs to address this.  or at least acknowledge in the documentation. 

    Wednesday, April 26, 2017 1:31 PM