Pass LINQ where clause as Parameter

It really is a simple question yet finding so much trouble with Google finding how to do it. After the where clause I want to return the results from my strongly typed dataset table with a parameter passed from a parameter with vb.net and how to call it. If you can assist I would appreciate it.

Bolded is where I think it should go. But I clearly don't know how to say it. "test" would be something like
"row.Item(columnname) IsNot DBNull.Value AndAlso row.Item(columnname) = filter AndAlso row.TT_Rec_ID = Template_ID" just for example. But the way I see it we should be able to pass anything through that is LINQ compatible.

Function Global_Table_Query([b]ByVal test As Predicate(Of ds2.JOINEDDATADataTable[/b])) As DataTable
        ErrorMessage = ""

        Dim dt As New DataTable
        Dim Is_Good As Byte = 1
               Dim thread As New Task(
  Function()
      Try

          Dim lowNums = From row In Global_Table
                        [b]Where test
[/b]                        Select row


          dt = lowNums.CopyToDataTable
                    Return dt
      Catch ex As Exception
          ErrorMessage = ex.Message
          Return Nothing
      Finally

      End Try
  End Function
            )

        Dim tasks() = {thread}
        Array.ForEach(tasks, Sub(tx) tx.Start())
        Task.WaitAll(tasks)

        For Each tsk In tasks

            Select Case True

                Case tsk.IsCanceled
                    Is_Good = 1

                Case tsk.IsFaulted
                    Is_Good = 1

                Case tsk.IsCompleted
                    Is_Good = 0

                Case Else
                    Is_Good = 1

            End Select
        Next

        tasks = Nothing
        thread = Nothing

       
        'checking if something went wrong
        If ErrorMessage <> "" Then
            Is_Good = Nothing
            Return Nothing
        End If
        Return dt
       
       

    End Function

Open in new window

Kelly MartensAsked:
Who is Participating?
 
it_saigeDeveloperCommented:
Heres an example (expanded to provide more options):

Form1.cs -
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Windows.Forms;

namespace EE_Q29072960
{
    public partial class Form1 : Form
    {
        readonly List<Data> data = new List<Data>()
        {
            new Data() { ID = 1, Name = "John", Company = "Wal-Mart", City = "St. Petersburg", State = "WI", PhoneNumber = "555-0123", Email = "john@walmart.com" },
            new Data() { ID = 2, Name = "Paul", Company = "K-Mart", City = "McKinney", State = "WA", PhoneNumber = "555-1234", Email = "paul@kmart.com" },
            new Data() { ID = 3, Name = "George", Company = "Sears", City = "St. Louis", State = "MO", PhoneNumber = "555-2345", Email = "george@sears.com" },
            new Data() { ID = 4, Name = "Ringo", Company = "JC Penny", City = "Chicago", State = "IL", PhoneNumber = "555-3456", Email = "ringo@jcpenny.com" },
            new Data() { ID = 5, Name = "Peter", Company = "AutoZone", City = "Houston", State = "TX", PhoneNumber = "555-4567", Email = "peter@autozone.com" },
            new Data() { ID = 6, Name = "Kyle", Company = "3 Guys Plumbing", City = "Williamsburgh", State = "OH", PhoneNumber = "555-5678", Email = "kyle@plumbers3.com" },
            new Data() { ID = 7, Name = "George", Company = "Wal-Mart", City = "St. Petersburg", State = "WI", PhoneNumber = "555-6789", Email = "george@walmart.com" },
            new Data() { ID = 8, Name = "John", Company = "Ace Hardware", City = "Paulstown", State = "FL", PhoneNumber = "555-7890", Email = "john@acehardware.com" },
            new Data() { ID = 9, Name = "William", Company = "Ace Plumbing", City = "Malibu", State = "CA", PhoneNumber = "555-0987", Email = "william@aceplumbing.com" },
            new Data() { ID = 10, Name = "Shorty", Company = "Chicago Good Guys", City = "Chicago", State = "IL", PhoneNumber = "555-9876", Email = "shorty@mafia.com" },
        };

        bool changingState = false;
        bool checkingAll = false;

        public Form1()
        {
            InitializeComponent();
        }

        private void OnClick(object sender, System.EventArgs e)
        {
            if (sender is ToolStripMenuItem)
            {
                var mui = sender as ToolStripMenuItem;
                if (mui.Equals(tsTableExit) || mui.Equals(tsmiExit))
                    Close();
            }
            else if (sender is Button)
            {
                var btn = sender as Button;
                if (btn.Equals(button1))
                {
                    listBox1.DataSource = null;
                    listBox1.Items.Clear();
                    if (!string.IsNullOrEmpty(textBox1.Text))
                    {
                        var selected = new List<Filter>();
                        foreach (ToolStripMenuItem item in tsmiSelectFields.DropDownItems)
                        {
                            if (!item.Checked)
                                continue;

                            if (item.Text.IndexOf("All", StringComparison.OrdinalIgnoreCase) > -1)
                            {
                                selected.AddRange(from menuItem in tsmiSelectFields.DropDownItems.Cast<ToolStripDropDownItem>().ToList()
                                                  where menuItem.Text.IndexOf("All", StringComparison.OrdinalIgnoreCase) == -1
                                                  select new Filter()
                                                  {
                                                      PropertyName = menuItem.Text.Replace("by ", "").Replace(" ", ""),
                                                      Operation = ((menuItem.DropDownItems[0] as ToolStripComboBox).SelectedItem as DataSourceItem<Operation>).ValueMember,
                                                      Modifier = ((menuItem.DropDownItems[1] as ToolStripComboBox).SelectedItem as DataSourceItem<Modifier>).ValueMember,
                                                      Value = textBox1.Text
                                                  });
                            }
                            else
                                selected.Add(new Filter()
                                {
                                    PropertyName = item.Text.Replace("by ", "").Replace(" ", ""),
                                    Operation = ((item.DropDownItems[0] as ToolStripComboBox).SelectedItem as DataSourceItem<Operation>).ValueMember,
                                    Modifier = ((item.DropDownItems[1] as ToolStripComboBox).SelectedItem as DataSourceItem<Modifier>).ValueMember,
                                    Value = textBox1.Text
                                });
                        }

                        var deleg = selected.Count > 0 ? ExpressionBuilder.GetExpression<Data>(selected).Compile() : null;
                        var filtered = deleg != null ? data.Where(deleg).ToList() : new List<Data>();

                        if (filtered.Count == 0)
                            listBox1.Items.Add("No results found!!!");
                        else
                            listBox1.DataSource = filtered;
                    }
                    else
                        listBox1.DataSource = data;
                }
            }
        }

        private void OnCheckedChanged(object sender, EventArgs e)
        {
            if (sender is ToolStripMenuItem && !checkingAll)
            {
                changingState = true;
                var mui = sender as ToolStripMenuItem;
                if (mui != null && mui.OwnerItem != null && mui.OwnerItem is ToolStripMenuItem)
                {
                    var parent = mui.OwnerItem as ToolStripMenuItem;
                    var all = (from i in parent.DropDownItems.Cast<ToolStripMenuItem>() where i.Name.IndexOf("All", StringComparison.OrdinalIgnoreCase) > -1 select i).FirstOrDefault();

                    if (!checkingAll)
                        all.Checked = (from i in parent.DropDownItems.Cast<ToolStripMenuItem>() where i != all && i.Checked select i).Count() == parent.DropDownItems.Count - 1;
                }
                changingState = false;
            }
        }

        private void OnAllCheckedChanged(object sender, EventArgs e)
        {
            if (sender is ToolStripMenuItem && !changingState)
            {
                checkingAll = true;
                var mui = sender as ToolStripMenuItem;
                if (mui != null && mui.OwnerItem != null && mui.OwnerItem is ToolStripMenuItem)
                {
                    var parent = mui.OwnerItem as ToolStripMenuItem;
                    var all = (from i in parent.DropDownItems.Cast<ToolStripMenuItem>() where i.Name.IndexOf("All", StringComparison.OrdinalIgnoreCase) > -1 select i).FirstOrDefault();
                    if (all != null)
                    {
                        foreach (ToolStripMenuItem item in parent.DropDownItems)
                        {
                            if (item != null && item != mui && item != all && item.Checked)
                                item.Checked = all.Checked;
                        }
                    }

                }
                checkingAll = false;
            }
        }

        private void OnLoad(object sender, EventArgs e)
        {
            foreach (var property in typeof(Data).GetProperties(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static))
            {
                if (property.GetCustomAttribute<BrowsableAttribute>() != null && !property.GetCustomAttribute<BrowsableAttribute>().Browsable)
                    continue;

                var ts = new ToolStripMenuItem { CheckOnClick = true, Name = $"tsSelect{property.Name}", Text = $"by {property.Name}" };
                var operation = new ToolStripComboBox { Name = $"{ts.Name}Operation", Text = "Operation" };
                var modifier = new ToolStripComboBox { Name = $"{ts.Name}Modifier", Text = "Modifier" };

                operation.ComboBox.BindingContext = BindingContext;
                operation.ComboBox.DisplayMember = "DisplayMember";
                operation.ComboBox.ValueMember = "ValueMember";
                operation.ComboBox.DataSource = (from v in Enum.GetValues(typeof(Operation)).Cast<Operation>() select new DataSourceItem<Operation> { DisplayMember = v.ToString(), ValueMember = v }).ToList();

                modifier.ComboBox.BindingContext = BindingContext;
                modifier.ComboBox.DisplayMember = "DisplayMember";
                modifier.ComboBox.ValueMember = "ValueMember";
                modifier.ComboBox.DataSource = (from v in Enum.GetValues(typeof(Modifier)).Cast<Modifier>() select new DataSourceItem<Modifier> { DisplayMember = v.ToString(), ValueMember = v }).ToList();

                ts.DropDownItems.AddRange(new[] { operation, modifier });
                ts.CheckedChanged += OnCheckedChanged;
                tsmiSelectFields.DropDownItems.Add(ts);
            }
        }
    }

    class Data
    {
        [Browsable(false)]
        public int ID { get; set; }
        public string Name { get; set; }
        public string Company { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string PhoneNumber { get; set; }
        public string Email { get; set; }

        public override string ToString()
        {
            return string.Format("{0} [{1}] at {2} [{3}] in {4}, {5}", Name, Email, Company, PhoneNumber, City, State);
        }
    }

    class DataSourceItem<T>
    {
        public string DisplayMember { get; set; }
        public T ValueMember { get; set; }
    }

    enum Operation
    {
        Equals,
        GreaterThan,
        LessThan,
        GreaterThanOrEqual,
        LessThanOrEqual,
        Contains,
        StartsWith,
        EndsWith
    }

    enum Modifier
    {
        None,
        OrElse,
        AndAlso
    }

    class Filter
    {
        public string PropertyName { get; set; }
        public Operation Operation { get; set; }
        public Modifier Modifier { get; set; }
        public object Value { get; set; }
    }

    static class ExpressionBuilder
    {
        public static Expression<Func<T, bool>> GetExpression<T>(IList<Filter> filters)
        {
            if (filters.Count == 0)
                return null;

            ParameterExpression param = Expression.Parameter(typeof(T), "t");
            Expression exp = null;

            if (filters.Count == 1)
                exp = GetExpression<T>(param, filters[0]);
            else if (filters.Count == 2)
                exp = GetExpression<T>(param, filters[0], filters[1]);
            else
            {
                while (filters.Count > 0)
                {
                    var f1 = filters[0];
                    var f2 = filters[1];

                    if (exp == null)
                        exp = GetExpression<T>(param, filters[0], filters[1]);
                    else
                        exp = (filters[0].Modifier == Modifier.AndAlso) ? 
                            Expression.AndAlso(exp, GetExpression<T>(param, filters[0], filters[1])) : 
                            Expression.OrElse(exp, GetExpression<T>(param, filters[0], filters[1]));

                    filters.Remove(f1);
                    filters.Remove(f2);

                    if (filters.Count == 1)
                    {
                        exp = (filters[0].Modifier == Modifier.AndAlso) ?
                            Expression.AndAlso(exp, GetExpression<T>(param, filters[0])) :
                            Expression.OrElse(exp, GetExpression<T>(param, filters[0]));
                        filters.RemoveAt(0);
                    }
                }
            }

            return Expression.Lambda<Func<T, bool>>(exp, param);
        }

        private static Expression GetExpression<T>(ParameterExpression param, Filter filter)
        {
            MemberExpression member = Expression.Property(param, filter.PropertyName);
            ConstantExpression constant = Expression.Constant(filter.Value);

            switch (filter.Operation)
            {
                case Operation.Equals:
                    return Expression.Equal(member, constant);

                case Operation.GreaterThan:
                    return Expression.GreaterThan(member, constant);

                case Operation.GreaterThanOrEqual:
                    return Expression.GreaterThanOrEqual(member, constant);

                case Operation.LessThan:
                    return Expression.LessThan(member, constant);

                case Operation.LessThanOrEqual:
                    return Expression.LessThanOrEqual(member, constant);

                case Operation.Contains:
                    return Expression.Call(member, typeof(string).GetMethod("Contains"), constant);

                case Operation.StartsWith:
                    return Expression.Call(member, typeof(string).GetMethod("StartsWith", new Type[] { typeof(string) }), constant);

                case Operation.EndsWith:
                    return Expression.Call(member, typeof(string).GetMethod("EndsWith", new Type[] { typeof(string) }), constant);
            }

            return null;
        }

        private static BinaryExpression GetExpression<T>(ParameterExpression param, Filter filter1, Filter filter2)
        {
            return (filter1.Modifier == Modifier.AndAlso) ?
                Expression.AndAlso(GetExpression<T>(param, filter1), GetExpression<T>(param, filter2)) :
                Expression.OrElse(GetExpression<T>(param, filter1), GetExpression<T>(param, filter2));
        }
    }
}

Open in new window

Form1.Designer.cs -
namespace EE_Q29072960
{
    partial class Form1
    {
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows Form Designer generated code
        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.menuStrip1 = new System.Windows.Forms.MenuStrip();
            this.tsmiTable = new System.Windows.Forms.ToolStripMenuItem();
            this.tsTableExit = new System.Windows.Forms.ToolStripMenuItem();
            this.tsmiSelectFields = new System.Windows.Forms.ToolStripMenuItem();
            this.tsSelectAll = new System.Windows.Forms.ToolStripMenuItem();
            this.tsmiExit = new System.Windows.Forms.ToolStripMenuItem();
            this.listBox1 = new System.Windows.Forms.ListBox();
            this.textBox1 = new System.Windows.Forms.TextBox();
            this.button1 = new System.Windows.Forms.Button();
            this.menuStrip1.SuspendLayout();
            this.SuspendLayout();
            // 
            // menuStrip1
            // 
            this.menuStrip1.Items.AddRange(new System.Windows.Forms.ToolStripItem[] {
            this.tsmiTable,
            this.tsmiSelectFields,
            this.tsmiExit});
            this.menuStrip1.Location = new System.Drawing.Point(0, 0);
            this.menuStrip1.Name = "menuStrip1";
            this.menuStrip1.Size = new System.Drawing.Size(452, 24);
            this.menuStrip1.TabIndex = 10;
            this.menuStrip1.Text = "menuStrip1";
            // 
            // tsmiTable
            // 
            this.tsmiTable.DropDownItems.AddRange(new System.Windows.Forms.ToolStripItem[] {
            this.tsTableExit});
            this.tsmiTable.Name = "tsmiTable";
            this.tsmiTable.Size = new System.Drawing.Size(47, 20);
            this.tsmiTable.Text = "Table";
            // 
            // tsTableExit
            // 
            this.tsTableExit.Name = "tsTableExit";
            this.tsTableExit.Size = new System.Drawing.Size(152, 22);
            this.tsTableExit.Text = "Exit";
            this.tsTableExit.Click += new System.EventHandler(this.OnClick);
            // 
            // tsmiSelectFields
            // 
            this.tsmiSelectFields.DropDownItems.AddRange(new System.Windows.Forms.ToolStripItem[] {
            this.tsSelectAll});
            this.tsmiSelectFields.Name = "tsmiSelectFields";
            this.tsmiSelectFields.Size = new System.Drawing.Size(83, 20);
            this.tsmiSelectFields.Text = "Select Fields";
            // 
            // tsSelectAll
            // 
            this.tsSelectAll.CheckOnClick = true;
            this.tsSelectAll.Name = "tsSelectAll";
            this.tsSelectAll.Size = new System.Drawing.Size(171, 22);
            this.tsSelectAll.Text = "All Fields";
            this.tsSelectAll.CheckedChanged += new System.EventHandler(this.OnAllCheckedChanged);
            // 
            // tsmiExit
            // 
            this.tsmiExit.Name = "tsmiExit";
            this.tsmiExit.Size = new System.Drawing.Size(37, 20);
            this.tsmiExit.Text = "Exit";
            this.tsmiExit.Click += new System.EventHandler(this.OnClick);
            // 
            // listBox1
            // 
            this.listBox1.FormattingEnabled = true;
            this.listBox1.Location = new System.Drawing.Point(12, 62);
            this.listBox1.Name = "listBox1";
            this.listBox1.Size = new System.Drawing.Size(428, 186);
            this.listBox1.TabIndex = 11;
            // 
            // textBox1
            // 
            this.textBox1.Location = new System.Drawing.Point(12, 28);
            this.textBox1.Name = "textBox1";
            this.textBox1.Size = new System.Drawing.Size(347, 20);
            this.textBox1.TabIndex = 12;
            // 
            // button1
            // 
            this.button1.Location = new System.Drawing.Point(365, 26);
            this.button1.Name = "button1";
            this.button1.Size = new System.Drawing.Size(75, 23);
            this.button1.TabIndex = 13;
            this.button1.Text = "Find";
            this.button1.UseVisualStyleBackColor = true;
            this.button1.Click += new System.EventHandler(this.OnClick);
            // 
            // Form1
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(452, 261);
            this.Controls.Add(this.button1);
            this.Controls.Add(this.textBox1);
            this.Controls.Add(this.listBox1);
            this.Controls.Add(this.menuStrip1);
            this.MainMenuStrip = this.menuStrip1;
            this.Name = "Form1";
            this.Text = "Form1";
            this.Load += new System.EventHandler(this.OnLoad);
            this.menuStrip1.ResumeLayout(false);
            this.menuStrip1.PerformLayout();
            this.ResumeLayout(false);
            this.PerformLayout();

        }

        #endregion

        private System.Windows.Forms.MenuStrip menuStrip1;
        private System.Windows.Forms.ToolStripMenuItem tsmiTable;
        private System.Windows.Forms.ToolStripMenuItem tsmiSelectFields;
        private System.Windows.Forms.ToolStripMenuItem tsmiExit;
        private System.Windows.Forms.ToolStripMenuItem tsTableExit;
        private System.Windows.Forms.ToolStripMenuItem tsSelectAll;
        private System.Windows.Forms.ListBox listBox1;
        private System.Windows.Forms.TextBox textBox1;
        private System.Windows.Forms.Button button1;
    }
}

Open in new window

Which produces the following output -
Initial Load -Capture.PNGEach of the fields provide an option to choose an operation -Capture.PNGAnd a modifier -Capture.PNGTo find a term in the dataset, you would select the field(s) to look in, the operation to use and the modifier of the operation:Capture.PNGType in your search term (an improvement would be to add more term fields as term fields are chosen):Capture.PNGAnd select 'Find':Capture.PNG
-saige-
0
 
Fernando SotoRetiredCommented:
Hi Kelly;

In answer to your question is that this can not be done right out of the box with VB.Net or any .Net language. The reason is that .Net is a type safe system and objects like this are checked at compile time so "This means you get compile-time checking of your LINQ queries, and full intellisense and refactoring support over your code:". If you want to do something like this you can use a library such as Dynamic LINQ which has been imported into a NuGet package called System.Linq.Dynamic.Core. Using this library the Where clause becomes a string it also means that you do not get compile time checking of the Linq query and can now get run time exceptions, so you will need to validate the string to make sure that it is correct.
0
 
Miguel OzSoftware EngineerCommented:
You could pass the LINQ expression as a parameter of your function, thus you can still reuse the function logic for different LINQ expressions that provide the same result type.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
it_saigeDeveloperCommented:
Miguel is correct, you could pass an expression that is generated during run-time; refer to this previous EE PAQ.

That being said, in order for the expression to be generated in the way you want to use it, you will need to collect the meta-data/schema of the database in order to dynamically build your expressions.  If you are dealing with stronly-typed classes which represent your meta-data/schema, this shouldn't pose a problem.

-saige-
0
 
Kelly MartensAuthor Commented:
Miguel and it-sage .... can you show me how you would do the different approaches you have talked about here? Fernando I will check out your idea ....
0
 
Kelly MartensAuthor Commented:
Thank you all for your efforts. This is the answer I came up with if your interested....

https://kellyschronicles.wordpress.com/2017/12/16/dynamic-predicate-for-a-linq-query/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.