none
.Any NotSupportedException with generic list RRS feed

  • Question

  • I am trying to use Any with 2 conditions and getting a NotSupportedException, here is my query.

    context.PayEntities.Where(p => payIds.Any(qp => qp.PayId == p.lngPKPay && 
                        qp.TaxYearId == p.lngFKLUTaxYear) && p.lngPayStatus == 3).ToList();

    Now I know .Any is supported so not sure why when use 2 conditions in a generic list I get an exception.

    PayIds is a generic list of  objects with 2 properties, PayId and TaxYearId

    Please advise how I can resolve this issue.

    Thursday, April 3, 2014 3:10 PM

Answers

  • Hello Stuart1962,

    Does your list like below, a collection of a object?

    List<Order> OrderIDs = new List<Order>() { new Order() { OrderID = 1, OrderName = "1" }, new Order() { OrderID = 3, OrderName = "2" } };

    If it is, then it should does not matter with the .Any() method. It just is not supported by LINQ2Entities, because your query will firstly be translated TSQL, however,  type like List<T> is not supported. For this, we may use AsEnumerable()/ToList() as:

    List<Order> OrderIDs = new List<Order>() { new Order() { OrderID = 1, OrderName = "1" }, new Order() { OrderID = 3, OrderName = "2" } };
    
                using (DFDBEntities db = new DFDBEntities())
    
                {
    
                    var result = db.Orders.AsEnumerable().Where(o => OrderIDs.Any(oid => oid.OrderID == o.OrderID && oid.OrderName == o.OrderName)).ToList();
    
                }
    

    Of course, this will have a performance since it will load all data to memory first.

    Or you can split the List<Object> to List<int>/List<string>, these primitive types are supported by LINQ2Entities.

    If I misunderstand, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 7, 2014 8:11 AM
    Moderator

All replies

  • What were the details of the NotSupportedException?  Usually it tells you why it couldn't translate it all the way to SQL.

    Basically you're trying to offload work to SQL that it can't do.  You are nesting the .Any, which might be part of it.  I guess it depends what payIds is too.

    Thursday, April 3, 2014 7:36 PM
  • Here is the Stack Trace:

    Exception Info: System.NotSupportedException Stack: at System.Data.Objects.ObjectQuery`1[[System.__Canon, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]].GetResults(System.Nullable`1<System.Data.Objects.MergeOption>) at System.Data.Objects.ObjectQuery`1[[System.__Canon, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]].System.Collections.Generic.IEnumerable<T>.GetEnumerator() at System.Collections.Generic.List`1[[System.__Canon, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]..ctor(System.Collections.Generic.IEnumerable`1<System.__Canon>) at System.Linq.Enumerable.ToList[[System.__Canon, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]](System.Collections.Generic.IEnumerable`1<System.__Canon>) at DataObjects.EntityFramework.Implementation.EntityPayDao.GetNewPayslips(System.Collections.Generic.List`1<BusinessObjects.Pay>) at IntegrationProcessing.PayslipService.Execute() at System.Threading.ThreadHelper.ThreadStart_Context(System.Object) at System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean) at System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean) at System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object) at System.Threading.ThreadHelper.ThreadStart()

    Friday, April 4, 2014 7:21 AM
  • Hello Stuart1962,

    Does your list like below, a collection of a object?

    List<Order> OrderIDs = new List<Order>() { new Order() { OrderID = 1, OrderName = "1" }, new Order() { OrderID = 3, OrderName = "2" } };

    If it is, then it should does not matter with the .Any() method. It just is not supported by LINQ2Entities, because your query will firstly be translated TSQL, however,  type like List<T> is not supported. For this, we may use AsEnumerable()/ToList() as:

    List<Order> OrderIDs = new List<Order>() { new Order() { OrderID = 1, OrderName = "1" }, new Order() { OrderID = 3, OrderName = "2" } };
    
                using (DFDBEntities db = new DFDBEntities())
    
                {
    
                    var result = db.Orders.AsEnumerable().Where(o => OrderIDs.Any(oid => oid.OrderID == o.OrderID && oid.OrderName == o.OrderName)).ToList();
    
                }
    

    Of course, this will have a performance since it will load all data to memory first.

    Or you can split the List<Object> to List<int>/List<string>, these primitive types are supported by LINQ2Entities.

    If I misunderstand, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 7, 2014 8:11 AM
    Moderator