'EF6:Parent context null inside same child query

My database contains below table in parent child relationships:-

Gender->Audit->Request(All are in 1:N Relationships)

I want to fetch all Genders with corresponding requests from database. My viewmodel looks like below:-

public class GenderViewModel
    {
        public string GenderId { get; set; }
        public string Name { get; set; }       
        public List<RequestNumberViewModel> OldRequestNumber { get; set; }
    }

I have written below LINQ Query syntax to achieve this.

List<GenderViewModel> vmGender = (from g in ctxGetGender.genders 
                            select new GenderViewModel
                            {
                                GenderId = g.org_genderid,
                                Name = g.org_gendername,                                    
                                OldRequestNumber = (from egender in ctxGetGender.genders
                                                    join audit in ctxGetGender.audits
                                                    on new { f1 = egender.org_genderid, f2 = tableName } equals new { f1 = audit.org_keydataid, f2 = audit.org_audittabletype } into temp
                                                    from audit in temp.DefaultIfEmpty()
                                                    join req in ctxGetGender.eventrequestworkflows
                                                    on audit.org_requestid equals req.org_eventreqworkflowid into reqtemp
                                                    from req in reqtemp.DefaultIfEmpty()
                                                    where egender.org_genderid == g.org_genderid
                                                    select new RequestNumberViewModel
                                                    {                                                           
                                                        RequestId = req.org_eventreqworkflowid,
                                                        RequestNumber = req.org_siterequestnumber
                                                    }).Distinct().ToList()
                            }).ToList();

When I try to run this query I got the below error:-

Object reference not set to an instance.

After some analysis I found that when I comment the below line then the query is working fine. However it is not giving the required result:-

where egender.org_genderid == g.org_genderid

I found out that "g.org_genderid" is coming as null and so basically I am getting the error.

Can you please help me as why parent context is becoming null in child subquery?

Call Stack

   at MySql.Data.EntityFramework.SelectStatement.AddColumn(ColumnFragment column, Scope scope)
   at MySql.Data.EntityFramework.SelectGenerator.Visit(DbApplyExpression expression)
   at System.Data.Entity.Core.Common.CommandTrees.DbApplyExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at MySql.Data.EntityFramework.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type)
   at MySql.Data.EntityFramework.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type)
   at MySql.Data.EntityFramework.SelectGenerator.Visit(DbProjectExpression expression)
   at System.Data.Entity.Core.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at MySql.Data.EntityFramework.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type)
   at MySql.Data.EntityFramework.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type)
   at MySql.Data.EntityFramework.SelectGenerator.Visit(DbUnionAllExpression expression)
   at System.Data.Entity.Core.Common.CommandTrees.DbUnionAllExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at MySql.Data.EntityFramework.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type)
   at MySql.Data.EntityFramework.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type)
   at MySql.Data.EntityFramework.SelectGenerator.Visit(DbSortExpression expression)
   at System.Data.Entity.Core.Common.CommandTrees.DbSortExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at MySql.Data.EntityFramework.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type)
   at MySql.Data.EntityFramework.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type)
   at MySql.Data.EntityFramework.SelectGenerator.Visit(DbProjectExpression expression)
   at System.Data.Entity.Core.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at MySql.Data.EntityFramework.SelectGenerator.GenerateSQL(DbCommandTree tree)
   at MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory)
   at System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext context, DbQueryCommandTree tree)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Boolean streaming, Span span, IEnumerable`1 compiledQueryParameters, AliasGenerator aliasGenerator)
   at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass41_0.<GetResults>b__1()
   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__DisplayClass41_0.<GetResults>b__0()
   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.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__31_0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Application.Business.BLL.BusinessLayer.GenderBLL.GetAllGenders(String tableName) in G:\onMyTune\IP\MySQL Projects\Project Acceleration - Employee Web API\Application.Business\BLL\BusinessLayer\GenderBLL.cs:line 61
   at Application.WebAPI.Controllers.GenderController.GetAllGenders() in G:\onMyTune\IP\MySQL Projects\Project Acceleration - Employee Web API\Project Acceleration - Employee Web API\Controllers\GenderController.cs:line 120

Observation This query worked fine when SQL Server is in backend. I checked that. When I am moving with exact same table schema to MySQL then this query start giving exceptions.

Solution 1:^[1]

mysql and oracle doesn't support linq query better to try it with sqlserver it will work fine if you wanna use mysql then try ADO.NET Entity Framework

Solution 2:^[2]

Finally I got solution. Below Linq Query helped me in getting the desired result. Compare my below query with old query mentioned in the post then you get to know the difference. Please let me know your feedback.

vmGender = (from g in ctxGetGender.genders
                            let gReqID = (from egender in ctxGetGender.genders
                                          join audit in ctxGetGender.audits                                          
                                          on new { f1 = egender.org_genderid, f2 = tableName } equals new { f1 = audit.org_keydataid.Value, f2 = audit.org_audittabletype } into temp                                         
                                          from audit in temp.DefaultIfEmpty()
                                          join req in ctxGetGender.eventrequestworkflows
                                          on audit.org_requestid equals req.org_eventreqworkflowid into reqtemp
                                          from req in reqtemp.DefaultIfEmpty()
                                          select new RequestNumberViewModel
                                          {                                              
                                              KeyId = egender.org_genderid,
                                              RequestId = req.org_eventreqworkflowid,
                                              RequestNumber = req.org_siterequestnumber
                                          }).Distinct().ToList()
                            select new GenderViewModel
                            {
                                GenderId = g.org_genderid,
                                Name = g.org_gendername,                                   
                                OldRequestNumber = gReqID.Where(x => x.KeyId.HasValue && x.KeyId.Value == g.org_genderid).ToList()                                                                
                            }).ToList();

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Developer
Solution 2	simple user

'EF6:Parent context null inside same child query

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]