1
votes

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; }
}

enter image description here

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:

enter image description here

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");
1

1 Answers

0
votes

The Contains<UnitInstanceOccurrencesEntity> method you are using in your second query is not String.Contains or IEnumerable<string>.Contains. It is an IEnumerable<UnitInstanceOccurrencesEntity>.Contains.

Your current logic is asking if the string x.FES_UNIT_INSTANCE_CODE is contained in a collection of UnitInstanceOccurrencesEntity, which is not possible.

Change your Where method in second query to:

x => ACTIVE_COURSE_INSTANCE_CODES
    .Select(code => code.FES_UINS_INSTANCE_CODE)
    .Contains(x.FES_UNIT_INSTANCE_CODE)