Version Q2 and Q3 difference? Linq error.

2 posts, 0 answers
  1. Erik
    Erik avatar
    314 posts
    Member since:
    Feb 2008

    Posted 01 Dec 2012 Link to this post

    Hello,

    Could you tell me why I have an error in Q3 that I do not have in Q2?

    Private mobj_AllTables As List(Of Entities.NetBas.DataDict.Data.Table) = Nothing
    Private mobj_AllColumns As List(Of Entities.NetBas.DataDict.Data.TableColumn) = Nothing
    ...
    Dim qry_Tables As IQueryable(Of Entities.NetBas.DataDict.Data.Table)
    qry_Tables = From t In DBC.GetAll(Of Entities.NetBas.DataDict.Data.Table)()
                 Join pi In DBC.GetAll(Of Entities.NetBas.ProjectItem)() On t.TableId Equals pi.BinderId
                 Where pi.ProjectId = obj_Project.ProjectId And pi.BinderType.ToUpper.Trim = "TABLE"
                 Order By t.TableId
                 Select ConvertProjectItem(t, pi)
    mobj_AllTables = qry_Tables.ToList ' result in 44 tables
     
    Dim qry_TableColumns2 As IQueryable(Of Entities.NetBas.DataDict.Data.TableColumn)
    qry_TableColumns2 = From tc In DBC.GetAll(Of Entities.NetBas.DataDict.Data.TableColumn)()
                        Join t In mobj_AllTables On tc.TableId Equals t.TableId
                        Select tc

    This works fine in Q2 but in Q3 I get an error or get 0 objects returned...

    Erik
  2. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 05 Dec 2012 Link to this post

    Hi Erik,

    Are you experiencing a similar exception to the one below on line 15 in your code snippet?

    System.InvalidOperationException was unhandled by user code
    HResult=-2146233079
      Message=An exception occured during the execution of 'Extent<Northwind.OrderDetail>().Join(value(System.Collections.Generic.List`1[Northwind.Order]), od => od.OrderID, o => o.Id, (od, o) => od)'. Failure: Non Queryable Source
    See InnerException for more details.
    Complete Expression: [omitted]


    If you are, you can work around the exception changing the second query to use Contains instead of Join.
    To achieve that you should modify your code in the following manner:
    01.Private mobj_AllTables As List(Of Entities.NetBas.DataDict.Data.Table) = Nothing
    02.Private mobj_AllColumns As List(Of Entities.NetBas.DataDict.Data.TableColumn) = Nothing
    03....
    04.Dim qry_Tables As IQueryable(Of Entities.NetBas.DataDict.Data.Table)
    05.qry_Tables = From t In DBC.GetAll(Of Entities.NetBas.DataDict.Data.Table)()
    06.             Join pi In DBC.GetAll(Of Entities.NetBas.ProjectItem)() On t.TableId Equals pi.BinderId
    07.             Where pi.ProjectId = obj_Project.ProjectId And pi.BinderType.ToUpper.Trim = "TABLE"
    08.             Order By t.TableId
    09.             Select ConvertProjectItem(t, pi)
    10.mobj_AllTables = qry_Tables.ToList ' result in 44 tables
    11. 
    12.Dim mobj_AllTableIds as IQueryable(Of Int32)
    13.mobj_AllTableIds = mobj_AllTables.Select(Func(i) i.TableId).ToList
    14.  
    15.Dim qry_TableColumns2 As IQueryable(Of Entities.NetBas.DataDict.Data.TableColumn)
    16.qry_TableColumns2 = From tc In DBC.GetAll(Of Entities.NetBas.DataDict.Data.TableColumn)()
    17.         Where mobj_AllTableIds.Contains(tc.TableId)
    18.         Select tc

    Changes include introduction of a new in-memory list of TableIds (lines 12, 13) that later in the second query are used as filter to get the proper table columns (line 17).

    Joining server side and in-memory collections is not a good idea since they will pull the whole left table into the memory and we have taken measures to prevent that. Please excuse us for the inconvenience.

    If you have any concerns about the work around, please share them with us.


    Kind regards,
    Viktor Zhivkov
    the Telerik team
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  3. DevCraft banner
Back to Top