OutOfMemoryException when selecting First()

Question

EDIT: After a lot of testing I have found that the problem had nothing to do with the OutOfMemoryException at all. Thanks for all your help, which led me to search for other reasons.

This case is closed!

I have a strange problem. My program has worked since last summer and now they updated Windows 10 to version 1709 (the computer is not usually connected)

And now I get an System.OutOfMemoryException when I try to access the database.

The table contains about 1 million rows, but since I'm using First() it shouldn't matter (should it??)

Have somebody a clue on what is wrong?

EDIT: Added the code for the context and the POCO.

EDIT 2: Added the SQL Query for context.LaserDate.First();

The database is an MS SQL Server 2014 and is accessed over the Local Network.

The code looks like this:

internal void IsConnectionOK()
{
    try
    {
        using (var context = new DCLasermarkContext())
        {
            var tmp = context.LaserData.First();
        }
    }
    catch (Exception ex)
    {
        Log.Fatal(ex, "IsConnection Failed!");
        throw;
    }
}

and the exception:

2018-04-20 07:29:37.3068 | FATAL | DCMarker.Model.DB.IsConnectionOK | IsConnection Failed! | System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
   at System.Data.Entity.Core.Common.EntitySql.CqlParser.yyparse()
   at System.Data.Entity.Core.Common.EntitySql.CqlParser.internalParseEntryPoint()
   at System.Data.Entity.Core.Common.EntitySql.CqlParser.Parse(String query)
   at System.Data.Entity.Core.Common.EntitySql.CqlQuery.Parse(String commandText, ParserOptions parserOptions)
   at System.Data.Entity.Core.Common.EntitySql.CqlQuery.CompileCommon[TResult](String commandText, ParserOptions parserOptions, Func`3 compilationFunction)
   at System.Data.Entity.Core.Common.EntitySql.CqlQuery.CompileQueryCommandLambda(String queryCommandText, Perspective perspective, ParserOptions parserOptions, IEnumerable`1 parameters, IEnumerable`1 variables)
   at System.Data.Entity.Core.Objects.EntitySqlQueryState.Parse()
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateInlineQueryOfT(ObjectQuery inlineQuery)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.ConstantTranslator.TypedTranslate(ExpressionConverter parent, ConstantExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.ObjectQueryMergeAsTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.ObjectQueryMergeAsTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.UnarySequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert()
   at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__0[TResult](IEnumerable`1 sequence)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.First[TSource](IQueryable`1 source)
   at DCMarker.Model.DB.IsConnectionOK()

EDIT:

public partial class DCLasermarkContext : DbContext
{
    public DCLasermarkContext()
        : base("name=DCLasermarkContext")
    {
        // Without this line, the compiler will optimize away  System.Data.Entity.SqlServer
        var type = typeof(System.Data.Entity.SqlServer.SqlProviderServices);

        // make sure that we don't initialize the database!!
        Database.SetInitializer<DCLasermarkContext>(null);
    }

    public virtual DbSet<Fixture> Fixture { get; set; }
    public virtual DbSet<HistoryData> HistoryData { get; set; }
    public virtual DbSet<LaserData> LaserData { get; set; }
    public virtual DbSet<QuarterCode> QuarterCode { get; set; }
    public virtual DbSet<SerialNumber> SerialNumber { get; set; }
    public virtual DbSet<WeekCode> WeekCode { get; set; }

    protected override System.Data.Entity.Validation.DbEntityValidationResult ValidateEntity(DbEntityEntry entityEntry, System.Collections.Generic.IDictionary<object, object> items)
    {
        if (entityEntry.Entity is LaserData)
        {
            if (string.IsNullOrWhiteSpace(entityEntry.CurrentValues.GetValue<string>("F1")))
            {
                var list = new List<System.Data.Entity.Validation.DbValidationError>();
                list.Add(new System.Data.Entity.Validation.DbValidationError("F1", GlblRes.ArticleF1_is_required));

                return new System.Data.Entity.Validation.DbEntityValidationResult(entityEntry, list);
            }
        }

        return base.ValidateEntity(entityEntry, items);
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //modelBuilder.Types()

        modelBuilder.Entity<Fixture>()
            .Property(e => e.FixturId)
            .IsUnicode(false);

        modelBuilder.Entity<HistoryData>()
            .Property(e => e.Kant)
            .IsFixedLength()
            .IsUnicode(false);

        modelBuilder.Entity<HistoryData>()
            .Property(e => e.DateMark)
            .IsFixedLength()
            .IsUnicode(false);

        modelBuilder.Entity<HistoryData>()
            .Property(e => e.DateMarkLong)
            .IsFixedLength()
            .IsUnicode(false);

        modelBuilder.Entity<HistoryData>()
            .Property(e => e.DateMarkShort)
            .IsFixedLength()
            .IsUnicode(false);

        modelBuilder.Entity<LaserData>()
            .Property(e => e.Kant)
            .IsFixedLength()
            .IsUnicode(false);

        modelBuilder.Entity<QuarterCode>()
            .Property(e => e.QYear)
            .IsFixedLength()
            .IsUnicode(false);

        modelBuilder.Entity<QuarterCode>()
            .Property(e => e.Q1)
            .IsFixedLength()
            .IsUnicode(false);

        modelBuilder.Entity<QuarterCode>()
            .Property(e => e.Q2)
            .IsFixedLength()
            .IsUnicode(false);

        modelBuilder.Entity<QuarterCode>()
            .Property(e => e.Q3)
            .IsFixedLength()
            .IsUnicode(false);

        modelBuilder.Entity<QuarterCode>()
            .Property(e => e.Q4)
            .IsFixedLength()
            .IsUnicode(false);

        modelBuilder.Entity<WeekCode>()
            .Property(e => e.Code)
            .IsFixedLength()
            .IsUnicode(false);
    }
}

[Table("LaserData")]
public partial class LaserData
{
    public int Id { get; set; }

    [Required]
    [StringLength(50)]
    public string F1 { get; set; }

    [StringLength(1)]
    public string Kant { get; set; }

    [StringLength(100)]
    public string Avdelning { get; set; }

    [StringLength(50)]
    public string F2 { get; set; }

    [StringLength(50)]
    public string F3 { get; set; }

    [StringLength(50)]
    public string F4 { get; set; }

    [StringLength(50)]
    public string F5 { get; set; }

    [StringLength(50)]
    public string F6 { get; set; }

    [StringLength(50)]
    public string F7 { get; set; }

    [StringLength(50)]
    public string F8 { get; set; }

    [StringLength(50)]
    public string F9 { get; set; }

    [StringLength(50)]
    public string F10 { get; set; }

    [StringLength(50)]
    public string BC1 { get; set; }

    [StringLength(50)]
    public string BC2 { get; set; }

    [StringLength(50)]
    public string Template { get; set; }

    [StringLength(50)]
    public string P1 { get; set; }

    [StringLength(50)]
    public string P2 { get; set; }

    [StringLength(50)]
    public string P3 { get; set; }

    [StringLength(50)]
    public string P4 { get; set; }

    [StringLength(50)]
    public string P5 { get; set; }

    [StringLength(50)]
    public string P6 { get; set; }

    [StringLength(50)]
    public string FixtureId { get; set; }

    public bool? ExternTest { get; set; }

    public bool? EnableTO { get; set; }

    [StringLength(50)]
    public string TOnr { get; set; }
}

Query for context.LaserData.First()

SELECT TOP (1) 
    [c].[Id] AS [Id], 
    [c].[F1] AS [F1], 
    [c].[Kant] AS [Kant], 
    [c].[Avdelning] AS [Avdelning], 
    [c].[F2] AS [F2], 
    [c].[F3] AS [F3], 
    [c].[F4] AS [F4], 
    [c].[F5] AS [F5], 
    [c].[F6] AS [F6], 
    [c].[F7] AS [F7], 
    [c].[F8] AS [F8], 
    [c].[F9] AS [F9], 
    [c].[F10] AS [F10], 
    [c].[BC1] AS [BC1], 
    [c].[BC2] AS [BC2], 
    [c].[Template] AS [Template], 
    [c].[P1] AS [P1], 
    [c].[P2] AS [P2], 
    [c].[P3] AS [P3], 
    [c].[P4] AS [P4], 
    [c].[P5] AS [P5], 
    [c].[P6] AS [P6], 
    [c].[FixtureId] AS [FixtureId], 
    [c].[ExternTest] AS [ExternTest], 
    [c].[EnableTO] AS [EnableTO], 
    [c].[TOnr] AS [TOnr]
    FROM [dbo].[LaserData] AS [c]

post context implementation, mainly LaserData property i suspect it is loading the entire M records into memory and running First op, in them. — eran otzap, Apr 20 '18 at 10:55
Try logging the query it generates to see what it's doing under the hood. Add this to your context constructor: `Database.Log = s => System.Diagnostics.Debug.WriteLine(s);` — Strikegently, Apr 20 '18 at 10:55
I believe you'r running into this issue https://stackoverflow.com/questions/18169859/entity-framework-large-data-set-out-of-memory-exception — eran otzap, Apr 20 '18 at 10:59
What database are you using? SQL Server, SQL Lite, or something else. Where is the SQL Server? What is the connection string? — jdweng, Apr 20 '18 at 11:01
You are using LINQ First(). That means the operation is propably (80%) be done on the client side. After tranfering close to all the data. Filtering, Paging. Those things should always be done in the Query. Moving tons of data to the client only to do filtering there is a bad idea. It is also a very common mistake, so do not feel bad if it affects you. — Christopher, Apr 20 '18 at 11:13
@Christopher If I log the query then I get an SELECT TOP (1) - wouldn't that be performed on the server? I will add the full query in the original question for clarity — Andis59, Apr 20 '18 at 11:23
Have you set a breakpoint inside `ValidateEntity` method to check if it reaches there, and what happens? — Alisson Reinaldo Silva, Apr 20 '18 at 11:24
With .First, Entity Framework will generate sql that will only fetch the first row so the problem is somewhere else. As I look at your stacktrace, you can clearly see that Entity Framework is trying to loop through some items which should not happen if you want to return just one object. So I presume for some reason EF is trying to load data from other tables as well. Do you have some navigation properties or something else that might do this? — Esko, Apr 20 '18 at 11:35
@Cristopher: I would assume it's actually using [Linq-to-SQL](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/) meaning that it will just get converted to `TOP (1)`. — Matthew Watson, Apr 20 '18 at 11:35
@Esko The table does not have any foreign key so there shouldn't be any navigation properties. How would I go about to see if there is any navigation properties — Andis59, Apr 20 '18 at 11:45
@Andis59 You can see those in the model, though they should not cause loading of data if not called. I might also be wrong and the stacktrace is there just because internally EF uses a list even if there is just one entity in it. I'm sorry I don't know what else to look. Did you log all executed sql when that line was run and was that the only one? — Esko, Apr 20 '18 at 11:52
@Andis59 When you run that query in sql management studio, does it return immediately then? — Daniel Lorenz, Apr 21 '18 at 01:26

OutOfMemoryException when selecting First()

0 Answers0