Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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.
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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.
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-
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-
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 ....
Heres an example (expanded to provide more options):
Form1.cs -
Initial Load -
Each of the fields provide an option to choose an operation -
And a modifier -
To 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:
Type in your search term (an improvement would be to add more term fields as term fields are chosen):
And select 'Find':
-saige-
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));
}
}
}
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;
}
}
Initial Load -
Each of the fields provide an option to choose an operation -And a modifier -To 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:Type in your search term (an improvement would be to add more term fields as term fields are chosen):And select 'Find':-saige-
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/
https://kellyschronicles.wordpress.com/2017/12/16/dynamic-predicate-for-a-linq-query/
Premium Content
You need an Expert Office subscription to comment.Start Free Trial