Calling controllers from a different repository and CRUD operations

[rcogan]

I have two different questions but they are somewhat related.

1. I am in the repository of one table and want to call a method in a controller/repository of a different table. First I would like to know if I should be calling the controller or repository of the different table. Currently I instantiate a controller of the different table and call the method using that. Everything is working but I have no idea if what I am doing is the proper way to do this. Is there an easier/better way to call methods in different controllers/repositories?

public virtual int PostMaterialAutoIssue(long mosSeqnum, string userID, string comment) {
                          int status = 1;

                          try {

                                   MOS mos = this.dataContext.MOSs.Where(x => x.MOS_TYPE == 'S' && x.MOS_SEQNUM == mosSeqnum).SingleOrDefault();
                                   if (mos == null) {
                                            throw new Exception("Work Order not found");
                                   }

                                   string location;
                                   decimal? issueQty;

                                   MaterialLocationDTO ml;
                                   MLsController MLCntrl = new MLsController();

                                   RQsController RQCntrl = new RQsController();
                                   var rqList = RQCntrl.GetMaterialByWorkOrder(mos.MOS_JOB, mos.MOS_LOT).ToList();

                                   foreach (var rq in rqList) {
                                            if (rq.QTY_REQUIRED > rq.QTY_ISSUED) {
                                                    location = null;
                                                    issueQty = rq.QTY_REQUIRED - rq.QTY_ISSUED;
                                                    ml = MLCntrl.GetDefaultLocationByItem(rq.ITEM_ID).ToList().First();
                                                    location = ml.LOCATION;
                                                     PostMaterialIssue(TxType.Issue, rq.MATERIAL_KEY, location, issueQty, userID, comment);
                                            }
                                   }
                          }
                          catch (Exception ex) {
                                   status = 0;
                                  throw ex;
                          }

                          return status;
                 }

2. I am not sure of the proper way to insert records into different tables using methods in different repositories. I have the code to insert the record for a specific table in a repository method for that table. What my question is, how should I write my code when I want to insert a record into several different tables using the repository method for each table. This method inserts a location (ML) into the database. It will be called directly to just insert a location and no other processing but it may also be called from another repository when inserting a record for that table (TX) along with other processing. In the TX repository method I am also creating an RRMModel which will still be open when I call the ML method. I have tested this and it works but again I have no idea if this is the proper/best way to do this. I've seen some use of TransactionScope. Do I need to use that? I want both inserts, TX and ML, to be within a single transaction so they either
both commit or both rollback. By creating an RRMModel in each method am I defeating the possibility of a single transaction? How is this supposed to be
done?

public virtual int PostLocation(string location, string imKey, int secondary, string status, int isRMA) {

                          using (RRMModel dbContext = new RRMModel()) {
                                   int success = 1;

                                   try {
                                            MC mc = this.dataContext.MCs.Where(x => x.MC_LOCATION == location).SingleOrDefault();
                                            if (mc == null) {
                                                    mc = new MC();
                                                    mc.MC_LOCATION = location;
                                                    mc.MC_CRDATE = blh.GetStdDate();
                                                    mc.MC_MDDATE = blh.GetStdDate();
                                                    dbContext.Add(mc);
                                            }
                                            ML ml = new ML();
                                            ml.ML_SEQNUM = BusinessLogicHelpers.GetSeqNum();
                                            ml.ML_SECONDARY = (short?)secondary;
                                            ml.ML_IMKEY = imKey;
                                            ml.ML_LOCATION = location;
                                            ml.ML_RMA_LOC = (short?)isRMA;
                                            ml.ML_CRDATE = blh.GetStdDate();
                                            ml.ML_MDDATE = blh.GetStdDate();
                                            if (status == "Available") {
                                                    ml.ML_STATUS = 'A';
                                            }
                                            else if (status == "On Hold" || isRMA == 1) {
                                                    ml.ML_STATUS = 'H';
                                            }
                                            else {
                                                    ml.ML_STATUS = 'U';
                                            }
                                            dbContext.Add(ml);

                                            dbContext.SaveChanges();
                                   }

                                   catch (Exception ex) {
                                            success = 0;
                                            dbContext.ClearChanges();
                                            throw ex;
                                   }

                                   return success;
                          }
                 }

[Yavor Slavchev]

Hello Rick,
Thank you for your interest in DataAccess products.

1. One good approach to deal with multiple entities in one repository is to use these entities directly from the context. It is not a good design to couple a repository to a controller, since the controller is a higher level component and it is responsible for handling HTTP requests. It doesn't touch the database directly. Actually the opposite is true -  a controller depends on a repository.
2. Similar approach can be applied when inserting/updating data in multiple tables. When is necessary to insert/update data in multiple tables in a repository, use the entities exposed by the context and then save changes for this context. This will ensure that data changes will be consistent across affected entities.

An even better approach would be to use the Unit of Work design pattern to handle CRUD operations on multiple entities. The idea is that the controller will work with a unit of work class to make database changes and the class itself is responsible for coordinating the work of multiple repositories by creating a single database context shared by all of them. That way, when a unit of work is complete you can call the SaveChanges() method on that instance and be assured that all related changes are consistent in the database. Look at this article that will you point you in the right direction if you start implementing this pattern. It can be applied successfully when working with Telerik DataAccess ORM.

I hope this information is helpful.