c# - Facing issues while converting stored procedure into entity framework function -

c# - Facing issues while converting stored procedure into entity framework function -

i new entity framework. have procedure save shipment info , have convert procedure entity framework function. simple insert/update able utilize entity framework particular procedure facing issue.

in below procedure have update shipment table have addressid, shipmenstatusid , serviceid foreign key. particular shipment record if address exists add together existing address id in foreign key column otherwise first add together new address address table , pick newly address id , update shipment address id column , same step shipmentstatustype , service type.

here procedure script.

create procedure spsavepackage @trackingnbr varchar(50), @carrier varchar(10), @packagetype varchar(20) = null, @shippeddate datetime = null, @scheduleddate datetime = null, @addressline1 varchar(50)= null, @addressline2 varchar(50)= null, @city varchar(50) = null, @state varchar(2) = null, @country varchar(2) = null, @statusdescription varchar(50) = null begin declare @addressid int, @deliverystatusid int , @packageid int if exists (select id tblpackages trackingnr = @trackingnbr , carrier = @carrier) begin if exists(select id tbldeliveryaddress address1 = @addressline1 , address2 = @addressline2 , city = @city , state = @state , country = @country) begin select @addressid = id tbldeliveryaddress address1 = @addressline1 , address2 = @addressline2 , city = @city , state = @state , country = @country end else begin select @addressid = max(id) tbldeliveryaddress set @addressid = @addressid + 1 insert tbldeliveryaddress values(@addressid , @addressline1 , @addressline2 , @city , @state , @country) end if exists (select id tbldeliverystatus status = @statusdescription) begin select @deliverystatusid = id tbldeliverystatus status = @statusdescription end else begin select @deliverystatusid = max(id) tbldeliverystatus set @deliverystatusid = @deliverystatusid + 1 insert tbldeliverystatus values(@deliverystatusid , @statusdescription) end update tblpackages set deliveryaddressid = @addressid, deliverystatusid = @deliverystatusid, shippeddate = @shippeddate , packagetype = @packagetype, scheduleddate = @scheduleddate trackingnr = @trackingnbr , carrier = @carrier end else begin select @packageid = max(id) tblpackages set @packageid = @packageid + 1 insert tblpackages(id , trackingnr , carrier) values (@packageid , @trackingnbr , @carrier) end end

for implementing functionality of procedure had created written next ef code.

public void updateshipments(list<tuple<tblshipment, list<tblactivity>>> shipments) { tblshipment shipment = null; var manager = ((iobjectcontextadapter)this._context).objectcontext.objectstatemanager; foreach (var tuple in shipments) { shipment = tuple.item1; if (shipment.consigneeaddress != null) { shipment.consigneeaddressid = this.addaddress(shipment.consigneeaddress).id; shipment.consigneeaddress = null; } else { shipment.consigneeaddressid = null; shipment.consigneeaddress = null; } if (shipment.shipperaddress != null) { shipment.shipperaddressid = this.addaddress(shipment.shipperaddress).id; shipment.shipperaddress = null; } else { shipment.shipperaddressid = null; shipment.shipperaddress = null; } if (shipment.service != null) { shipment.serviceid = this.addservice(shipment.service).id; shipment.service = null; } else { shipment.serviceid = null; shipment.service = null; } if (shipment.shipmentstatustype != null) { shipment.shipmentstatusid = this.addstatustype(shipment.shipmentstatustype).id; shipment.shipmentstatustype = null; } else { shipment.shipmentstatusid = null; shipment.shipmentstatustype = null; } this._context.entry(shipment).state = system.data.entity.entitystate.modified; } this._context.savechanges(); } public tbladdresstype addaddresstype(tbladdresstype addresstype) { tbladdresstype tempaddresstype = (from m in this._context.addresstypes m.type.toupper() == addresstype.type.toupper() select m).firstordefault(); if (tempaddresstype == null) { tempaddresstype = this._context.addresstypes.add(addresstype); this._context.savechanges(); } homecoming tempaddresstype; } public tbladdress addaddress(tbladdress address) { tbladdress tempaddress = (from m in this._context.addresses m.addressline1.toupper() == address.addressline1.toupper() && m.addressline2.toupper() == address.addressline2.toupper() && m.city.toupper() == address.city.toupper() && m.stateprovincecode.toupper() == address.stateprovincecode.toupper() && m.countrycode.toupper() == address.countrycode.toupper() select m).firstordefault(); if (tempaddress == null) { address.addresstype = this.addaddresstype(address.addresstype); address.addresstypeid = address.addresstype.id; address.addresstype = null; tempaddress = this._context.addresses.add(address); this._context.savechanges(); } homecoming tempaddress; }

after spending lots of time found way implement not satisfied implementation. have lot's of nail saving/updating shipment records slowing process. need optimized way update shipment records saving records have single database hit. have multiple shipments record(records in collection) , want single database nail save records or 1 database nail save 1 shipment records.

i tried clarify problems if 1 facing issue understand allow me know. using c# programming language, sql server database , entity framework 6.0 orm.

any help appreciated.

thanks, awadhendra

i've had similar problem in past. while considered hack others due nature of hard coding things may change, if implement simple integration tests (see later example) possible maintain track of such changes

here solution

namespace contextnamespace { public partial class contextclass { public task updateshipments(list<tuple<tblshipment, list<tblactivity>>> shipments) { homecoming this.database.executesqlcommandasync("exec spsavepackage @p1 = @p1..", new sqlparameter("@p1", shipments.???),....); } } }

along side have integration tests one, purpose pass without exceptions -of course of study can fleshed out include more proper checks

[testmethod] public async task sproc_test() { //create object context context = new context(); await context.updateshipments(/*object*/); }

an illustration of utilize in code this

public task dosomething(object data) { homecoming context.updateshipments(data); }

c# entity-framework entity-framework-6