CODEWITHSUNDEEP
c#linqexpression
A Bit of Help
A Bit of Help

Reputation: 1796

Build Linq Expression to return all questions that have keywords assigned to it that match a list of keyword ids?

I am having an issue with creating a proper Expression using Linq, and I hope that you can help me...

I have the following relationships:

Question (1) --- (n) QuestionsKeywords (n) --- (1) Keyword 
PK Id                PK Id                         PK Id
string DisplayAs     int QuestionId                string DisplayAs
                     int KeywordId

So, a Question can have many Keywords assigned to it, and a Keyword can be assign to many Questions.

The data model has a Question with a QuestionsKeywords collection.

The user selects keywords in a CheckedListBox control that he/she wants to use to filter the list of Questions in a data grid. I create a List keywordIds that contains the record id for each of selected keywords. This list will be used to filter the list of questions later...

Finally... The question...

How can I implement an Expression that will return all Questions that have any QuestionKeyword entities that have a KeywordId in keywordIds?

I've tried the following, but get an runtime exception, which follows. 

Expression<Func<QuestionEntity, bool>> expression = 
q => q.QuestionsKeywords.Any(qk => keywordIds.Any(k => k.Id == qk.Keyword.Id));

Thank you for your time and suggestions!

Mike

System.InvalidCastException was unhandled HResult=-2147467262 Message=Unable to cast object of type 'System.Linq.Expressions.ConstantExpression' to type 'SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.SetExpression'. Source=SD.LLBLGen.Pro.ORMSupportClasses StackTrace: at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.MemberAccessEvaluator.HandleLinqExpressionAsSetExpression(LinqExpressionAsSetExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\MemberAccessEvaluator.cs:line 183 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 192 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleAllAnyExpression(AllAnyExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 564 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 140 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleLambdaExpression(LambdaExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 1377 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 296 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleAllAnyExpression(AllAnyExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 565 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 140 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleLambdaExpression(LambdaExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 1377 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 296 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleWhereExpression(WhereExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 974 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 216 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 817 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 796 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 207 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 817 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 796 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 207 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSortClauseExpression(SortClauseExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 640 at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExpressionHandlers\GenericExpressionHandler.cs:line 213 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\LLBLGenProProviderBase.cs:line 189 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\LLBLGenProProviderBase.cs:line 129 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\LLBLGenProProviderBase.cs:line 679 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.Execute() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\LLBLGenProQuery.cs:line 86 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.SD.LLBLGen.Pro.LinqSupportClasses.ILLBLGenProQuery.ExecuteTResult in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\LLBLGenProQuery.cs:line 130 at SD.LLBLGen.Pro.LinqSupportClasses.QueryableExtensionMethods.Execute[TResult](IQueryable source) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\ExtensionMethods.cs:line 146 at ContentLibrary.MainForm.FilterButton_Click(Object sender, EventArgs e) in c:\PROJECTS\ContentLibrary\ContentLibrary\MainForm.cs:line 1282 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(Form mainForm) at ContentLibrary.Program.Main() in c:\PROJECTS\ContentLibrary\ContentLibrary\Program.cs:line 19 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException:

Upvotes: 0

Views: 366

Answers (1)

Carlos Nu&#241;ez
Carlos Nu&#241;ez

Reputation: 470

It is because keywordIds is a collection that exists in memory, while the other collections exist in your DB. Try passing the collection of ids to the closure as an array of integers instead.

var keywordIdsArray = keywordIds.Select(x => x.Id).ToArray();

Expression<Func<QuestionEntity, bool>> expression = 
    q => q.QuestionsKeywords.Any(qk => keywordIdsArray.Contains(qk.Keyword.Id));

Upvotes: 3

Related Questions