Due to some performace issues with passing large datasets im having to convert some of the llblgen pages to use linqdatasources. Ive had to use some workarounds to get them to hookup with the devexpress grids.. most of which i learned from user posts on this forum.

Im trying to figure out how to convert the following linq object query to a linq datasource query. Any help or pointers in the right direction would be appreciated.

LLBLGen 3.1 SelfServicing, .NET 4.0, SQL Server 2008

using devexpress' linqdatasource (should i use a diff datasource?)

Old code - works but slow

  Dim c As New HelpDeskCaseCollection
  c.GetMulti(Nothing)
    
  Dim tagpaths As New List(Of String)
  For Each i In TagList.SelectedItems
       tagpaths.Add(i.ToString)
  Next

'linq to parse tags
 q = From i In c Where i.HelpDeskCaseTags.Any(Function(x) tagpaths.Any(Function(y) x.Path.StartsWith(y)))

new code - stuck

Private Sub linqHelpDesk_Selecting(sender As Object, e As DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs) Handles linqHelpDesk.Selecting

  Dim m As New LinqMetaData()
  Dim q As IQueryable

  If tagpaths.Count > 0 Then
    'does not work - obviously
     q = From i In m.HelpDeskCase  Where i.HelpDeskCaseTags.Any(Function(x) tagpaths.Any(Function(y) x.Path.StartsWith(y)))
  Else
  'works OK!
      q = From i In m.HelpDeskCase Select i
  End If

The old code is slow because you fetch the entire table, and filter at the cient side. Why don't you use a filter in the database side? i.e. pass a filter to the GetMulti() method.

Yes I realize thats why its slow. Thats why I switched to linq data source, which is also needed for server side paging with devexpress grids.. and has now become a requirement for most of our apps.

Also we have many linqs using lambdas that this methodology will ultimately apply to.. so i need to figure out how to do this if it is possible.

Bideaway - when I run the code in debug I receive the following error

"The expression with type 'Convert' couldn't be converted to a SetExpression."

stack trace

   at SD.LLBLGen.Pro.LinqSupportClasses.LinqUtils.CoerceToSet(Expression toCoerce, MappingTracker trackedMappings, Boolean throwOnFailure) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LinqUtils.cs:line 1975
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallAllAny(MethodCallExpression expressionToHandle, Boolean isAll) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 892
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 1700
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 1624
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 711
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\GenericExpressionHandler.cs:line 293
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 170
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleLambdaExpression(LambdaExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\GenericExpressionHandler.cs:line 1370
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\GenericExpressionHandler.cs:line 296
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 170
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallAllAny(MethodCallExpression expressionToHandle, Boolean isAll) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 900
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 1700
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 1624
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 711
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\GenericExpressionHandler.cs:line 293
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 170
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleLambdaExpression(LambdaExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\GenericExpressionHandler.cs:line 1370
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\GenericExpressionHandler.cs:line 296
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 170
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallWhere(MethodCallExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 1540
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 1808
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 1624
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 711
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\GenericExpressionHandler.cs:line 293
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 170
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallSelect(MethodCallExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 1481
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 1775
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 1624
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 711
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\GenericExpressionHandler.cs:line 293
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 170
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleAggregateFunctionMethodCall(MethodCallExpression expressionToHandle, AggregateFunction function) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 1034
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 1709
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 1624
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 711
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\GenericExpressionHandler.cs:line 293
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\PreProcessor.cs:line 170
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 115
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 92
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 697
   at DevExpress.Data.Linq.Helpers.CriteriaToQueryableExtender.Count(IQueryable src)
   at DevExpress.Data.Linq.Helpers.LinqServerModeCache.GetCountStatic(IQueryable q, ICriteriaToExpressionConverter converter, CriteriaOperator criteriaOperator)
   at DevExpress.Data.Linq.Helpers.LinqServerModeCache.GetCount(CriteriaOperator criteriaOperator)
   at DevExpress.Data.Helpers.ServerModeKeyedCache.PrepareTopGroupCount()
   at DevExpress.Data.Helpers.ServerModeCache.PrepareTopGroupInfoWithTrick(ServerModeSummaryDescriptor[] summaries)
   at DevExpress.Data.Helpers.ServerModeCache.CreateTopGroupInfo()

I'm kind of lost figuring out what is the real problem here. Your second query where you are fetching and applying a filter in linq, What is the problematic code there?

Also, the exception you posted, what is the line that originates it?

What is the problematic code there?

 q = From i In m.HelpDeskCase Where i.HelpDeskCaseTags.Any(Function(x) tagpaths.Any(Function(y) x.Path.StartsWith(y)))

Thats what Im asking. The code does not work in that context. Why? And how can I modify it appropriatley? It works fine when querying an object.

Also, the exception you posted, what is the line that originates it?

It originates on the databind of the gridview.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

  With gvCases
             .DataSource = linqHelpDesk
            .KeyFieldName = "CaseId"
            .DataBind()
   End With

Ive been using llblgen for about a year now and this is the first time im completely stuck.

I see that there is a similar issue here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=15155

But am unsure of how to apply this to my situation. any help would be appreciated.

 tagpaths.Any(...)

I guess the above part is Linq2Objects and you are trying to use it with Linq2LLBLgen, which won't work. Try reformulating the query.

Also which exact build of the LLBLGen Pro runtime library are you using?

3.1.11.701

We use lambdas quite extensively right now. Can you confirm with me if all lambdas dont work?

jsspam wrote:

3.1.11.701

Please update to the latest build. When you have problems using LINQ2LLBL the best is to have the latest dlls.

jsspam wrote:

We use lambdas quite extensively right now. Can you confirm with me if all lambdas dont work?

It's not that they won't work, is that your in-memory lambda functions must be in the outer projection. See this.

Thanks. Ive updated to the latest and am reviewing the documentation. It looks like we will be unable to use lambdas effectively on related entities.

Im also doing some basic testing but am still a little confused. For example:

works

q = From i In m.HelpDeskCase Where i.Description.ToLower.Contains(s)

works

q = From i In m.HelpDeskCase Where i.Title.ToLower.Contains(s)

Does not work ??

q = From i In m.HelpDeskCase Where i.Description.ToLower.Contains(s) Or i.Title.ToLower.Contains(s)

Please examine and post the generated SQL Query for each of these linq statements.

ToLower is a method, right? Should be used like this: ToLower()

And what about this one:

q = From i In m.HelpDeskCase 
Where i.Description.ToLower().IndexOf(s) >= 0 
Or i.Title.ToLower().IndexOf(s) >= 0

Walaa wrote:

And what about this one:

q = From i In m.HelpDeskCase 
Where i.Description.ToLower().IndexOf(s) >= 0 
Or i.Title.ToLower().IndexOf(s) >= 0

does not work either..

{"The multi-part identifier ".LPFA_3" could not be bound."}

    Query: SELECT TOP(@p2) COUNT(*) AS [LPAV_] FROM [it_dept].[dbo].[HelpDeskCases]  [LPLA_1]   WHERE ( ( ( .[LPFA_3] = @p4)))
    Parameter: @p2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @p4 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: True.

whats the best way to capture the generated sql? sql profiler?

well i think i have solved some of our query problems. llblgen to linq does not like the 'or' in vb.net. It is working when i use 'orelse'

I have been able to convert some of our linq now.. but I am banging my head on this last one which should be easy. We have a keyword search that should return items that contain all of the searched keywords - regardless of the order.

I can get this to work about 5 different ways with regular linq but am stuck on the llblgen linq. Any help would be appreciated. I have broken it down to its simplest form below

For Each word In searchtems.Trim.Split(" ")
     q = q.Where(Function(x) x.Title.Contains(word))
Next

the generated code from llblgen always returns only the last keyword. For exampl: Searching for "test distribution" only finds distribution

c sp_executesql N'SELECT TOP(@p2) COUNT(*) AS [LPAV_] FROM [it_dept].[dbo].[HelpDeskCases]  [LPLA_1]   WHERE ( ( ( ( [LPLA_1].[Title] LIKE @p3)) AND ( [LPLA_1].[Title] LIKE @p4)))',N'@p2 bigint,@p3 varchar(14),@p4 varchar(14)',@p2=1,@p3='%distribution%',@p4='%distribution%'

The problem is in the loop. It's a "Access to modified closure" situation. Workaround this by creating a local variable of word inside the loop. See these links for more info:

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=16115&StartAtMessage=0&#90076

http://weblogs.asp.net/fbouma/archive/2009/06/25/linq-beware-of-the-access-to-modified-closure-demon.aspx