0

I've a Table SiteReport which can have Many Asset, And each Asset can only have one Certificate. So its One-to-Many & then One-to-One relationship. As shown below.

enter image description here

I'm trying to delete a SiteReport in the given method, So that all Assets under the Report and all associated Certificates will be deleted.

public ActionResult DeleteSiteReport(long siteReportId)
    {
        SiteReport report;
        List<Asset> reportAssets;

        using (var ctx = new ApplicationDbContext())
        {
            report = ctx.SiteReport.Include(i => i.Assets).SingleOrDefault(x => x.site_report_id == siteReportId);
            reportAssets = ctx.Asset.Include(i => i.Certificate).Where(x => x.site_report_id == siteReportId).ToList<Asset>();

            foreach (Asset asset in reportAssets)
            {
                Certificate cert = ctx.Certificate.SingleOrDefault(x => x.report_asset_id == asset.report_asset_id);
                ctx.Certificate.Remove(cert);
                ctx.Asset.Remove(asset);
            }

            ctx.SiteReport.Remove(report);

            ctx.SaveChanges();
        }

        return RedirectToAction("ViewReports");
    }

Is this a correct/best way of doing it ? How to failproof it?

AlphaTry
  • 475
  • 5
  • 27
  • Don't you have any navigation property inside reports and assets? What value do you have for cascade delete? – cassandrad Dec 15 '17 at 15:05
  • In 'SiteReport' table `virtual ICollection Assets` is the navigation property, and in 'Asset' Table `int site_report_id` , `virtual SiteReport SiteReport` & `virtual Certificate Certificate` are the navigational. Similarly in my 'Certificate' table `virtual Report_Asset Report_Asset` is navigational – AlphaTry Dec 15 '17 at 15:34
  • Have you tried to remove selected records from navigation properties? – cassandrad Dec 15 '17 at 15:43
  • Yes, for a passed (siteReportId) im pulling in 'SiteReport' including 'Assets' which will include list of asset under this site report. And also pulling in All 'Asset' including 'Certificate'. Then for each Asset i'm first deleting its 'Certificate' and then 'Asset' itself , as there One-to-one relation. After deleting all them Assets and Certificate I'm deleting SiteReport itself in the end. As at this point i won't have this report's id as foreignKey in 'Asset' table . – AlphaTry Dec 15 '17 at 15:50
  • Your code isn't equal to what you are saying. Have you tried to remove assets and certificates from `report` variable, not touching context's `Asset` and `Certificate` properties? And how cascade deletion is configured? – cassandrad Dec 15 '17 at 16:09
  • If you are talking about the cascade delete option defined in the entity Mapping class ? I haven't configured any cascade delete option there. And I just did a small fix in the code i.e its '.Remove(cert)' instead of (calcert). apart from that, this code is fine and working. If I may ask, How are you saying its not equal to what i've stated?? – AlphaTry Dec 15 '17 at 16:23
  • In code, you are deleting records from properties on the context. I was talking about deleting records from navigation properties of entities. But it's irrelevant I guess. [Here](https://stackoverflow.com/questions/6180927/removing-all-records-from-navigation-properties-in-entity-framework) is a pretty explanation of the difference. – cassandrad Dec 15 '17 at 16:41

0 Answers0