I need to select all the records where FES_UNIT_INSTANCE_CODE IN IQueryable using LINQ query. I am IQuerable list in ACTIVE_COURSE_INSTANCE_CODES but getting error in second LINQ query
public IList<ActiveUnitInstancesViewModel> GetActiveUnitInstances()
{
IList<ActiveUnitInstancesViewModel> _ActiveUnitInstances = null;
using (var _uow = new FES_UOF())
{
IQueryable<UnitInstanceOccurrencesEntity> ACTIVE_COURSE_INSTANCE_CODES =
from UIO in _uow.UnitInstanceOccurrencesRepository.GetAll()
where UIO.STATUS != "WITHDRAWN" && UIO.STATUS != "INACTIVE"
group UIO by new { UIO.FES_UINS_INSTANCE_CODE, UIO.OWNING_ORGANISATION } into grp
select new UnitInstanceOccurrencesEntity {FES_UINS_INSTANCE_CODE = grp.Key.FES_UINS_INSTANCE_CODE, OWNING_ORGANISATION = grp.Key.OWNING_ORGANISATION};
var query = (from unitInstance in _uow.UnitInstancesRepository.GetAll().
Where(x => ACTIVE_COURSE_INSTANCE_CODES.Contains<UnitInstanceOccurrencesEntity>( x.FES_UNIT_INSTANCE_CODE ))
select new ActiveUnitInstancesViewModel
{
UnitInstances = unitInstance
}).
ToList();
}
..
public class ActiveUnitInstancesViewModel
{
public UnitInstancesEntity UnitInstances { get; set; }
public UnitInstanceOccurrencesEntity UnitInstanceOccurrences { get; set; }
public VerifierEntity Verifiers { get; set; }
public OrganisationUnitEntity OrganisationUnits { get; set; }
}
SQL Script
WHERE UNIT_INSTANCES.FES_UNIT_INSTANCE_CODE IN
(SELECT ACTIVE_COURSE_INSTANCE_CODES.FES_UINS_INSTANCE_CODE
FROM ACTIVE_COURSE_INSTANCE_CODES);
Error2:
Exception
System.NotSupportedException was unhandled by user code
HResult=-2146233067
Message=Unable to cast the type 'System.Linq.IOrderedQueryable`1[[App.Entities.UnitInstanceOccurrences, App.Entities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]' to type 'System.Data.Entity.Core.Objects.ObjectQuery`1[[App.Entities.UnitInstanceOccurre nces, App.Entities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]'. LINQ to Entities only supports casting EDM primitive or enumeration types.
Source=EntityFramework
StackTrace:
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.ValidateAndAdjustCastTypes(TypeUsage toType, TypeUsage fromType, Type toClrType, Type fromClrType)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.GetCastTargetType(TypeUsage fromType, Type toClrType, Type fromClrType, Boolean preserveCastForDateTime)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.CreateCastExpression(DbExpression source, Type toClrType, Type fromClrType)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.ConvertTranslator.TranslateUnary(ExpressionConverter parent, UnaryExpression unary, DbExpression operand)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.UnaryTranslator.TypedTranslate(ExpressionConverter parent, UnaryExpression 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.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.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.MethodCallTranslator.ContainsTranslator.TranslateContains(ExpressionConverter parent, Expression sourceExpression, Expression valueExpression)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.ContainsTranslator.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.TranslateLambda(LambdaExpression lambda, DbExpression input)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.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.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.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__DisplayClassb.<GetResults>b__a()
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__DisplayClassb.<GetResults>b__9()
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__0()
at System.Lazy`1.CreateValue()
at System.Lazy`1.LazyInitValue()
at System.Lazy`1.get_Value()
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 App.EBSMiddleware.Engine.CurriculumServices.GetActiveUnitInstances() in C:\MyDevelopment\EBSMiddlewareApplication\App.EBSMiddlewareServices\Services\Curriculum Mangement\ActiveUnitInstances.cs:line 80
at ManagementConsole.MainWindow..ctor() in C:\MyDevelopment\EBSMiddlewareApplication\ManagementConsole\MainWindow.xaml.cs:line 128
InnerException:
This work from 1st Query
IQueryable<UnitInstanceOccurrences> ACTIVE_COURSE_INSTANCE_CODES =
(from UIO in _uow.UnitInstanceOccurrencesRepository.GetAll()
where UIO.STATUS != "WITHDRAWN" && UIO.STATUS != "INACTIVE"
group UIO by new { UIO.FES_UINS_INSTANCE_CODE , UIO.OWNING_ORGANISATION } into grp
select new UnitInstanceOccurrences { FES_UINS_INSTANCE_CODE = grp.Key.FES_UINS_INSTANCE_CODE, OWNING_ORGANISATION = grp.Key.OWNING_ORGANISATION })
.Distinct()
.OrderBy(o => o.FES_UINS_INSTANCE_CODE);
var d = ACTIVE_COURSE_INSTANCE_CODES.Select(code => code.FES_UINS_INSTANCE_CODE).ToList();
var d2 = ACTIVE_COURSE_INSTANCE_CODES.Select(code => code.FES_UINS_INSTANCE_CODE).Contains("IH5MD019");