none
Insufficient Memory with Entity Framework Core RRS feed

  • Question

  • Hello! I am still new to using Entity Framework and Entity Framework Core and was wondering if anyone else can offer any advice with an issue that I am having.

    I have a database that has 645 tables with a lot of foreign keys. The issue that I am running into is that I can generate the entity context just fine using Entity Framework Core but when I try to begin work for debugging I am met with an "Insufficient Memory" exception. This happens before I even try and place any code that makes a call to the data. I was wondering if anyone else has had this happen and if so did you happen to find a way to address this exception?

    Thanks for any help. I understand that I am still new to Entity Framework so I am hoping that I just overlooked something obvious.

    Monday, March 9, 2020 3:53 PM

All replies

  • Hi Organization Dev Member,
    Based on your description, I can't reproduce the situation. And I have some suggestions you can refer to.
    1.The issue is that when you get data from EF there are actually two copies of the data created, one which is returned to the user and a second which EF holds onto and uses for change detection (so that it can persist changes to the database). EF holds this second set for the lifetime of the context and its this set thats running you out of memory.
    You can solve this by following methods:
    1.1Renew your context each batch.
    1.2Use .AsNoTracking() in your query:

    IEnumerable<IEnumerable<Town>> towns = dbContext.Towns.AsNoTracking().OrderBy(t => t.TownID).Batch(200000);

    2.If you have IIS running in 64-bit on your machine, check if you can run via that instead of via the debugger.
    3.Use one connection for reading and one for writing or updating.
    More details you can refer to these documents.
    [Troubleshooting System.OutOfMemoryExceptions in ASP.NET]
    [Entity Framework and AsNoTracking]
    Hope these are helpful for you.
    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.
    Best Regards,
    Daniel Zhang

    Tuesday, March 10, 2020 2:10 AM
  • I have a database that has 645 tables with a lot of foreign keys.

    It doesn't seem to be a database that is a candidate for an ORM, IMO. 

    The issue that I am running into is that I can generate the entity context just fine using Entity Framework Core but when I try to begin work for debugging I am met with an "Insufficient Memory" exception.

    IMO, having a Dbcontext that is topping-off at 100 tables is pushing it, let alone one that has 645 model objects in it, which could only slow the solution down as it has to instance a Dbcontext that has 645 objects on the model. 

    This happens before I even try and place any code that makes a call to the data. I was wondering if anyone else has had this happen and if so did you happen to find a way to address this exception?

    IMO, you should try to have multiple Dbcontexts and try to implement some kind of table segregation, find another ORM like NHibernate or Dapper, or not use an ORM and just use straight-up ADO.NET, DB command objects, T-SQL the DTO pattern and the DAO pattern.

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    https://en.wikipedia.org/wiki/Data_access_object

    https://javarevisited.blogspot.com/2013/01/data-access-object-dao-design-pattern-java-tutorial-example.html

    https://www.tutorialspoint.com/design_pattern/data_access_object_pattern.htm

    I use the patterns even when using EF Core in the DAL.

    using System;
    using System.Collections.Generic;
    using System.Text;
    using Entities;
    
    namespace DAL
    {
        public interface IDaoProject
        {
            DtoProject GetProjectById(int id);
            List<DtoProject> GetProjectsByUserId(string userid);
            void CreateProject(DtoProject dto);
            void UpdateProject(DtoProject dto);
            void DeleteProject(int id);
        }
    }
    =====================================================
    using System.Collections.Generic;
    using System.Linq;
    using System.Transactions;
    using DAL.Models.DB;
    using Entities;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.Extensions.Options;
    
    namespace DAL
    {
        public class DaoProject :IDaoProject
        {
            private readonly IOptions<ConnectionStrings> _options;
            
            public DaoProject(IOptions<ConnectionStrings> options)
            {
                _options = options;
            }
    
            public DtoProject GetProjectById(int id)
            {
                var dto = new DtoProject();
    
                using (var context = new ProjectManagementContext(_options))
                {
                    var project = (context.Projects.Where(a => a.ProjectId == id)).SingleOrDefault();
    
                    if (project == null) return dto;
                    dto.ProjectId = project.ProjectId;
                    dto.ClientName = project.ClientName;
                    dto.ProjectName = project.ProjectName;
                    dto.Technology = project.Technology;
                    dto.ProjectType = project.ProjectType;
                    dto.UserId = project.UserId;
                    dto.StartDate = project.StartDate;
                    dto.EndDate = project.EndDate;
                    dto.Cost = project.Cost;
                }
    
                return dto;
            }
    
            public List<DtoProject> GetProjectsByUserId(string userid)
            {
                var dtos = new List<DtoProject>();
    
                using (var context = new ProjectManagementContext(_options))
                {
                    
                    dtos = (from a in context.Projects.Where(a => a.UserId.Contains(userid))
                        select new DtoProject
                        {
                            ProjectId = a.ProjectId,
                            ClientName = a.ClientName,
                            ProjectName = a.ProjectName,
                            Technology = a.Technology,
                            ProjectType = a.ProjectType,
                            UserId = a.UserId,
                            StartDate = a.StartDate,
                            EndDate = a.EndDate,
                            Cost = a.Cost
                        }).ToList();
                }
    
                return dtos;
            }
    
            public void CreateProject(DtoProject dto)
            {
                using (var context = new ProjectManagementContext(_options))
                {
                    var project = new Projects
                    {
                        ClientName = dto.ClientName,
                        ProjectName = dto.ProjectName,
                        Technology = dto.Technology,
                        ProjectType = dto.ProjectType,
                        UserId = dto.UserId,
                        StartDate = dto.StartDate,
                        EndDate = dto.EndDate,
                        Cost = dto.Cost
                    };
    
                    context.Projects.Add(project);
                    context.SaveChanges();
               }
            }
    
            public void UpdateProject(DtoProject dto)
            {
                var project = new Projects();
                
                using (var context = new ProjectManagementContext(_options))
                {
                   project = (context.Projects.Where(a => a.ProjectId == dto.ProjectId)).SingleOrDefault();
                }
    
                if (project != null)
                {
                    project.ClientName = dto.ClientName;
                    project.ProjectName = dto.ProjectName;
                    project.Technology = dto.Technology;
                    project.ProjectType = dto.ProjectType;
                    project.UserId = dto.UserId;
                    project.StartDate = dto.StartDate;
                    project.EndDate = dto.EndDate;
                    project.Cost = dto.Cost;
                }
    
                using (var dbcontext = new ProjectManagementContext(_options))
                {
                    if (project == null) return;
                    dbcontext.Entry(project).State = EntityState.Modified;
                    dbcontext.SaveChanges();
                }
            }
    
            public void DeleteProject(int id)
            {
                Projects project;
    
                using (var context = new ProjectManagementContext(_options))
                {
                   project = (context.Projects.Where(a => a.ProjectId == id)).SingleOrDefault();
                }
    
                if (project == null) return;
    
                using (var newContext = new ProjectManagementContext(_options))
                {
                   
                    var tasks = new DaoTask(_options).GetTasksByProjectId(project.ProjectId);
                    using (TransactionScope scope = new TransactionScope())
                    {
                        foreach (var task in tasks)
                        {
                            new DaoTask(_options).DeleteTask(task.TaskId);
                        }
    
                        newContext.Entry(project).State = EntityState.Deleted;
                        newContext.SaveChanges();
    
                        scope.Complete();
                    }
                }
            }
        }
    }
    

    using System;
    
    namespace Entities
    {
        public class DtoProject
        {
            public int ProjectId { get; set; }
            public string ClientName { get; set; }
            public string ProjectName { get; set; }
            public string Technology { get; set; }
            public string ProjectType { get; set; }
            public string UserId { get; set; }
            public DateTime StartDate { get; set; }
            public DateTime EndDate { get; set; }
            public decimal Cost { get; set; }
        }
    }
    

    Tuesday, March 10, 2020 5:58 AM