|
|
jsspam
User
Location:
Joined on:
16-Mar-2012 05:27:18
Posted:
18 posts
|
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
| Code: |
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
| Code: |
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
|
|
|
|
Walaa
Support Team
Location:
Joined on:
21-Aug-2005 16:03:48
Posted:
14017 posts
|
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.
|
|
|
jsspam
User
Location:
Joined on:
16-Mar-2012 05:27:18
Posted:
18 posts
|
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
| Code: |
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() |
|
|
|
daelmo
Support Team
Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
7759 posts
|
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?
|
|
|
jsspam
User
Location:
Joined on:
16-Mar-2012 05:27:18
Posted:
18 posts
|
| Quote: |
| What is the problematic code there? |
| Code: |
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.
| Quote: |
| Also, the exception you posted, what is the line that originates it? |
It originates on the databind of the gridview.
| Code: |
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.
|
|
|
jsspam
User
Location:
Joined on:
16-Mar-2012 05:27:18
Posted:
18 posts
|
|
|
Walaa
Support Team
Location:
Joined on:
21-Aug-2005 16:03:48
Posted:
14017 posts
|
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?
|
|
|
jsspam
User
Location:
Joined on:
16-Mar-2012 05:27:18
Posted:
18 posts
|
3.1.11.701
We use lambdas quite extensively right now. Can you confirm with me if all lambdas dont work?
|
|
|
daelmo
Support Team
Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
7759 posts
|
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.
|
|
|
jsspam
User
Location:
Joined on:
16-Mar-2012 05:27:18
Posted:
18 posts
|
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
| Code: |
q = From i In m.HelpDeskCase Where i.Description.ToLower.Contains(s) |
works
| Code: |
q = From i In m.HelpDeskCase Where i.Title.ToLower.Contains(s) |
Does not work ??
| Code: |
q = From i In m.HelpDeskCase Where i.Description.ToLower.Contains(s) Or i.Title.ToLower.Contains(s) |
|
|
|
Walaa
Support Team
Location:
Joined on:
21-Aug-2005 16:03:48
Posted:
14017 posts
|
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:
| Code: |
q = From i In m.HelpDeskCase
Where i.Description.ToLower().IndexOf(s) >= 0
Or i.Title.ToLower().IndexOf(s) >= 0 |
|
|
|
jsspam
User
Location:
Joined on:
16-Mar-2012 05:27:18
Posted:
18 posts
|
| Walaa wrote: |
And what about this one:
| Code: |
q = From i In m.HelpDeskCase
Where i.Description.ToLower().IndexOf(s) >= 0
Or i.Title.ToLower().IndexOf(s) >= 0 |
|
does not work either..
| Code: |
{"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?
|
|
|
jsspam
User
Location:
Joined on:
16-Mar-2012 05:27:18
Posted:
18 posts
|
|
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'
|
|
|
jsspam
User
Location:
Joined on:
16-Mar-2012 05:27:18
Posted:
18 posts
|
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
| Code: |
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
| Code: |
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%'
|
|
|
|
daelmo
Support Team
Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
7759 posts
|
|
|