EF6:同一子查询中的父上下文为空

Question

我的数据库包含以下父子关系表:-

性别->审计->请求(所有都在1:N关系中)

我想从数据库中获取所有性别与相应的请求。我的视图如下所示:-

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

我已经写了下面的LINQ查询语法来实现这个。

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();

当我尝试运行这个查询时，我得到了以下错误:-

对象引用未设置为实例。

经过一些分析，我发现当我注释下面一行时，查询工作正常。然而，它没有给出所需的结果:-

where gender.org_genderid == g.g or_genderid

我发现"g.org_genderid"返回null，所以我得到的是错误

你能帮助我为什么父上下文在子查询中成为空吗?

调用堆栈

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:onMyTuneIPMySQL ProjectsProject Acceleration - Employee Web APIApplication.BusinessBLLBusinessLayerGenderBLL.cs:line 61
at Application.WebAPI.Controllers.GenderController.GetAllGenders() in G:onMyTuneIPMySQL ProjectsProject Acceleration - Employee Web APIProject Acceleration - Employee Web APIControllersGenderController.cs:line 120

观察当SQL Server在后台时，这个查询工作得很好。我查过了。当我用完全相同的表模式移动到MySQL时，这个查询开始给出异常。

Answer 1

mysql和oracle不支持linq查询最好在sqlserver上尝试一下，它会工作得很好如果你想使用mysql，那么试试ADO。. NET实体框架

Answer 2

终于有办法了。下面的Linq查询帮助我得到了想要的结果。将我下面的问题与帖子中提到的旧问题进行比较，然后你就会知道它们的区别。请让我知道你的反馈。

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();