- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
how do i write this linq object query for a linqdatasource?
Joined: 16-Mar-2012
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
Joined: 16-Mar-2012
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()
Joined: 28-Nov-2005
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?
Joined: 16-Mar-2012
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.
Joined: 16-Mar-2012
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.
Joined: 21-Aug-2005
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?
Joined: 28-Nov-2005
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.
Joined: 16-Mar-2012
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)
Joined: 21-Aug-2005
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
Joined: 16-Mar-2012
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?
Joined: 16-Mar-2012
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%'
Joined: 28-Nov-2005
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𕿜