Hello all,

I am trying to create a function that accepts an entity collection populated with certain data that will be used in turn to create a where clause that is based off the populated field/column name and its value. My current code receives a collection loop through it looking for fields that have data and extracting a field name as a string and the vale as a string as well. I cannot figure out how to create a where condition from a column name as a string or a value as a string as well. Any help / insight would be greatly appreciated...

1          Public Shared Function GetTblFinancialICLBCollection(ByVal budgetCollection As TblFinancialICLBCollection) As TblFinancialICLBCollection
2                Dim retTblFinancialICLBCollection As New TblFinancialICLBCollection
3                Dim colName As String
4                Dim colValue As String
5    
6                For index As Integer = 0 To budgetCollection.Meta.Columns.Count - 1
7                    colName = budgetCollection.Meta.Columns(index).Name
8                    If Not budgetCollection.Item(0).GetColumn(colName) Is DBNull.Value Then
9                        colValue = CStr(budgetCollection.Item(0).GetColumn(colName))
10   
11                       'Can't do this but not sure how...?
12                       'whereCondition = "retTblFinancialICLBCollection.Query." & Replace(colName, "_", "") & ".Equal(" & colValue & ")"
13   
14                       retTblFinancialICLBCollection.Query.Where(whereCondition)
15                    
16                   End If
17   
18               Next
19   
20               Return retTblFinancialICLBCollection
21           End Function

1 create a collection

2 add an entity to it

3 populate a few fields in the collection's entity with data

4 pass the collection to a function

5 function loops through the columns of the collection's single entity

6 Stores field names and values from collection's entity that have data

7 create a new collection of the same type of the source collection with the single entity

8 define an arbitrary number of 'and' where conditions using the extracted column names and values

9 load a query on the new collection based on the where conditions derived from the field name and value pairs from the source collection's entity

Clear as mud? I want to load a new collection based on the field values of a source collection. Basically, I could have a search that uses a data grid populated with all the fields of the table. A user then can put a few values in what ever field in the grid then they would get a list of budgets based on the search criteria they entered.

Take a look at our ASPX demo, specifically the search functionaltiy, you can find it HERE. You can download the source to those pages in the Forums > EntitySpaces Trial / Test Suite > Trial Version on the 2nd page. There is a special way to add the where operator, like this:

First we populate those search comboboxes next to each field like this:

protected ListItem[] PopulateSearchItemList()
{
	ListItem[] items = new ListItem[11];
	items[0 ] = new ListItem("", "");
	items[1 ] = new ListItem("Equal", "Equal");
	items[2 ] = new ListItem("Not Equal", "NotEqual");
	items[3 ] = new ListItem("Greater Than", "GreaterThan");
	items[4 ] = new ListItem("Greater Than Or Equal", "GreaterThanOrEqual");
	items[5 ] = new ListItem("Less Than", "LessThan");
	items[6 ] = new ListItem("Less Than Or Equal", "LessThanOrEqual");
	items[7 ] = new ListItem("Like", "Like");
	items[8 ] = new ListItem("Is Null", "IsNull");
	items[9 ] = new ListItem("Is Not Null", "IsNotNull");
	items[10] = new ListItem("Not Like", "NotLike");
	return items;
}

Then, when they hit "Search" we see if the combobox has a value (the criteria/operator) and then populate the Where() clause like this:

private void LoadEntityForSeach(VEmployeesCollection coll)
{
	esVEmployeesQuery q = coll.Query;


	if(this.vdEmployeeID.SelectedValue != "")
	{
		esWhereOperand op = (esWhereOperand)Enum.Parse(typeof(esWhereOperand), 
			vdEmployeeID.SelectedValue);
		q.Where(q.EmployeeID.OP(op, this.vtEmployeeID.Text, null));
	}

	if(this.vdLastName.SelectedValue != "")
	{
		esWhereOperand op = (esWhereOperand)Enum.Parse(typeof(esWhereOperand), 
			vdLastName.SelectedValue);
		q.Where(q.LastName.OP(op, this.vtLastName.Text, null));
	}

	if(this.vdFirstName.SelectedValue != "")
	{
		esWhereOperand op = (esWhereOperand)Enum.Parse(typeof(esWhereOperand), 
			vdFirstName.SelectedValue);
		q.Where(q.FirstName.OP(op, this.vtFirstName.Text, null));
	}

    // and so on ...
}

Notice the use of the OP operator .... and we turn the "value" of our ListItem, ie, new ListItem("Greater Than Or Equal", "GreaterThanOrEqual") into an esWhereOperand using Enum.Parse ...

Hope that helps, follow up if need be ...

Interesting. However the only operator I would be using is equal. I need to dynamically change the column not the operator. Notice in the code above you have three if statements. Now imagine having to check against 150 different fields. The one heck of a case / if statement. You could do it that way. I want to dynamically specify the field/colom name and use only one if statement.

if(this.vdFirstName.SelectedValue != "")
{
esWhereOperand op = (esWhereOperand)Enum.Parse(typeof(esWhereOperand),
vdFirstName.SelectedValue);
q.Where(q.FirstName.OP(op, this.vtFirstName.Text, null));
}

In the bold above you have hard coded the colomn name I want to be able to change this by specifing the name at run time.

Cast a es colomn OBJECT from "Specified Column Name String"

if(this.OBJECT.SelectedValue != "")
{
esWhereOperand op = (esWhereOperand)Enum.Parse(typeof(esWhereOperand),
OBJECT.SelectedValue);
q.Where(q.FirstName.OP(op, this.vtFirstName.Text, null));
}

I hope this makes my problem a little clearer. I feel if I only knew more I could explain myself better...

Hi

I've just taken a stab at this (if I'm understanding what you're trying to achieve) and the following seems to get you in the right direction although I'm sure it's not recommended as it's using the esQueryItem which is supposed to be used internally:

Public Sub CreateWhereClause(ByVal entityColl As EntitySpaces.Core.esEntityCollection)
Dim eq As EntitySpaces.Interfaces.esDynamicQuery
Dim e As EntitySpaces.Core.esEntity
e = entityColl(0) 'I've just grabbed the first entity in the collection, you would do as your app requires
eq = entityColl.es.Query
For Each o As Object In e.es.ModifiedColumns
Dim qi As New EntitySpaces.Interfaces.esQueryItem(eq, e.es.Meta.Columns.FindByPropertyName(o).Name)
eq.Where(qi.Equal(e.GetColumn(o.ToString)))
Next
eq.Load()
Debug.Print(eq.es.LastQuery)
End Sub

Naturally you'll need to adjust/tidy up as required but the code should give you the general gist of what you're looking for.  I've just mocked this up, called it with an entity containing a single (new) entity that had 2 properties changed, FirstName and Status and all appeared as it should

Hope that helps

Martin