Version Q2 and Q3 difference? Linq error.

[Erik]

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

[Viktor Zhivkov]

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