asked on

How do I use a column from a SQL table as an identifier in a LINQ Query?

I have a SQL table with columns that contain either "Required" or "Not Required." I want to build a table that will put a 1 in the column under the associated header, if the result of the query is required. I don't want to set up a query to check each column result in the main table, so I set up a reference table with the thought that I could scroll through it and check. I have attached a copy of the SQL Table, the reference table and an example of the outcome I'd like.

Here is the code I have now. I want to be able to use my reference table as a way to pick the column in the main table to find out if it's required in a for each/next statement.

dtable = New DataTable

dtable.Columns.Add("Project Name", GetType(String))

For Each rec In getTasks
Dim datarow As DataRow = dtable.NewRow
datarow.Item(0) = rec
dtable.Rows.Add(datarow)
Next

'add support to the table
For Each y In getServ
dtable.Columns.Add(y.reqService, GetType(String))
Next

For Each col In dtable.Columns
Dim dc As New DataGridViewTextBoxColumn
DataGridView1.Columns.Add(dc)
dc.HeaderText = col.ToString
dc.DataPropertyName = col.ToString
Next

For c As Integer = 1 To dtable.Columns.Count - 1

Dim col = dtable.Columns(c).ColumnName

Dim suppPick = (From id In getServ _
Where id.reqService = col _
Select id.sysPick).ToList

For i As Integer = 0 To dtable.Rows.Count - 1

Dim pName = dtable.Rows(i).Item(0)

Dim req = (From id In d.tbl645s _
Where id.projName1 = CStr(pName) _
And id.(where I want to put (suppPick.Item(0)) = "Required" _
Select id).ToList

Dim reqAnswer = req.Count

If reqAnswer >= 1 Then
dtable.Rows(i).Item(c) = reqAnswer
Else
dtable.Rows(i).Item(c) = 0
End If

Next
Next

Me.DataGridView1.DataSource = dtable.AsDataView

I'll even take a better way of doing this if I'm way off course.

Many thanks!
SuppServTable.jpg
SupportServRequire.jpg
SuppServRefTable.jpg

MikeToole

Adding columns to a table to indicate the presence/absence of a service is not a good approach - unless there's a requirement that isn't obvious in your question.
Instead, create a table with two columns, ServiceName and Required, where the ServiceName is what you now add as a column name and Required is a Boolean/bit field.
Then, instead of adding a column to a table for a service, insert a row in the new table.
It will be much easier to handle.

Karen Wilson

ASKER

It's not obvious in my question. I have a SQL Table where data is stored with either a Required or Not Required answer. There are over 20 of them. I want to loop through them based upon a reference table I set up.

I finally went in and just coded each one of them like this.

For c As Integer = 1 To dtable.Columns.Count - 1

For i As Integer = 0 To dtable.Rows.Count - 1

Dim pName = dtable.Rows(i).Item(0)

If c = 1 Then

Dim req = (From id In d.tbl645s _
Where id.projName1 = CStr(pName) _
And id.suppPickBO44 = "Required" _
Select id).ToList
Dim reqCount = req.Count

If reqCount >= 1 Then
dtable.Rows(i).Item(c) = reqCount
Else
dtable.Rows(i).Item(c) = 0
End If

ElseIf c = 2 Then

Dim req = (From id In d.tbl645s _
Where id.projName1 = CStr(pName) _
And id.suppPickCal31 = "Required" _
Select id).ToList
Dim reqCount = req.Count

If reqCount >= 1 Then
dtable.Rows(i).Item(c) = reqCount
Else
dtable.Rows(i).Item(c) = 0
End If
Next
Me.DataGridView1.DataSource = dtable.AsDataView

What I am trying to code systematically, is in bold. I just ElseIf'd all 23 of them. It should be easier but I can't figure out how to insert the bolded statement in a LINQ query. I've attached my end result.
suppEndResult.jpg

louisfr

You could use reflection to get the field or property you want.

And id.GetType().GetField(suppPick.Item(0)).GetValue(id).Equals("Required")

Open in new window

And id.GetType().GetProperty(suppPick.Item(0)).GetValue(id).Equals("Required")

Open in new window

Karen Wilson

ASKER

Thanks for the response. I apologize for the delay in responding.

I tried the first one and got an error message.

An unhandled exception of type 'System.NotSupportedException' occurred in System.Data.Linq.dll

Additional information: Method 'System.Reflection.FieldInfo GetField(System.String)' has no supported translation to SQL.

The second one had an overload resolution from the get-go.

It looks like the LINQ portion is the problem. Hmmmm.

Thanks again for the response.

louisfr

We'll need to create the "where" part by hand.
Something like

        Dim whereParameter = Expression.Parameter(GetType(THETYPE))
        Dim wherePart = Expression.Lambda(Of Func(Of THETYPE, Boolean))(
                Expression.And(
                    Expression.Equal(Expression.PropertyOrField(whereParameter, "projName1"), Expression.Constant(CStr(pName))),
                    Expression.Equal(Expression.PropertyOrField(whereParameter, suppPick.Item(0)), Expression.Constant("Required"))
                ), whereParameter
            )

        Dim req2 = d.tbl645s.AsQueryable.Where(wherePart).ToList

Open in new window

Replace both occurrence of THETYPE by the type of the items of tbl645s

Karen Wilson

ASKER

I will give it a try! I tried something similar but couldn't get it to stick. Thanks.

Karen Wilson

ASKER

It's getting much closer! Here is the error.
LambdaError.jpg

Karen Wilson

ASKER

Code I used:
Dim whereParameter = Expression.Parameter(GetType(String))

Dim wherePart = Expression.Lambda(Of Func(Of String, Boolean))(Expression.And(Expression.Equal(Expression.PropertyOrField(whereParameter, "projName1"),
Expression.Constant(CStr(pName))), Expression.Equal(Expression.PropertyOrField(whereParameter, suppPick.Item(0)),
Expression.Constant("Required"))), whereParameter)

louisfr

Replace GetType(String) with GetType(EnvrDataEntry.tbl645)
And replace OfFunc(Of String, Boolean) with OfFunc(EnvrDataEntry.tbl645, Boolean)

Karen Wilson

ASKER

It's telling me that tbl645 cannot be used as an expression.

louisfr

Where?

Karen Wilson

ASKER

Okay, I got that fixed.

In the wherePart, it doesn't like this: OfFunc(EnvrDataEntry.tbl645, Boolean) It says that Array bounds cannot appear in type specifiers. Here is how I have it typed in:
Dim wherePart = Expression.Lambda(Of Func(EnvrDataEntry.tbl645, Boolean))

Karen Wilson

ASKER

Here is the whereParameter code:
Dim whereParameter = Expression.Parameter(GetType(EnvrDataEntry.tbl645))

Karen Wilson

ASKER

Also, this is how I wrote the query part:

Dim req = (From id In d.tbl645s _
Where id.projName1 = CStr(pName) _
And wherePart IsNot Nothing _
Select id).ToList

louisfr

Right. I didn't see I had forgot the space between "Of" and the type.
The query is not right. You cannot use wherePart like that. What you're checking is that the variable wherePart contains something, and that is always true since you put something in it in the lines above.
You must not use the query expression form. I don't think there's a way to use a hand-crafted expression tree in a query.

Dim req = d.tbl645s.Where(wherePart).ToList

Open in new window

louisfr

To further explain, this form

From item in list Where condition Select item

Open in new window

is compiled into

list.Where(Function(item) condition)

Open in new window

The Expression.Lambda call creates the equivalent of the lambda expression

Function(item) condition

Open in new window

You can see that there's no place in the query to put the lambda expression. That's why you need to use a call to the Where method instead.

Karen Wilson

ASKER

Sorry for the delayed response. I was out of the area.

Here is how I have the whereParameter typed in: Dim whereParameter = Expression.Parameter(GetType(EnvrDataEntry.tbl645))

This is the result of the whereParameter {System.Linq.Expressions.TypedParameterExpression}

This is my wherePart
Dim wherePart = Expression.Lambda(Of Func(Of String, Boolean))(Expression.And(Expression.Equal(Expression.PropertyOrField(whereParameter, "projName1"),
Expression.Constant(CStr(pName))), Expression.Equal(Expression.PropertyOrField(whereParameter, suppPick.Item(0)),
Expression.Constant("Required"))), whereParameter)

It throws an error.
Unhandled except of type "System.ArgumentExcept' occurred in System.Core.dll

ParameterExpression of type 'EnvrData.tbl645' cannot be used for delegate parameter of type 'System.String'

I have never written this type of code so thank you for your patience.

ASKER CERTIFIED SOLUTION

louisfr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Wilson

ASKER

That did it!! Thanks so much for being patient with me. I was researching to understand and kept thinking, it's where that darn string is, but what should I put?? This opens a whole new world for me! I can even begin to tell you how many times I've performed a complicated work around because I couldn't figure out how to write a "where" statement and insert it. You're the best. Thanks again.

Karen Wilson

ASKER

I thought I had awarded the points already. Glad I am back. But meanwhile, I'm trying to add a third line to the code and it is throwing an error. If you have time, could you look at it and tell me what I'm doing wrong? If not, I'll ask on another question.

Not working:
Dim wherePart = Expression.Lambda(Of Func(Of EnvrDataEntry.tbl645, Boolean))(Expression.And(Expression.Equal(Expression.PropertyOrField(whereParameter, getStuff.Item(0).sysPick), Expression.Constant("Required")),
Expression.NotEqual(Expression.PropertyOrField(whereParameter, "ApprovedDate"), Expression.Constant(Nothing)),
Expression.Equal(Expression.PropertyOrField(whereParameter, "closedDate"), Expression.Constant(""))), whereParameter)

Working:

Dim wherePart = Expression.Lambda(Of Func(Of EnvrDataEntry.tbl645, Boolean))(Expression.And(Expression.Equal(Expression.PropertyOrField(whereParameter, getStuff.Item(0).sysPick), Expression.Constant("Required")),
Expression.NotEqual(Expression.PropertyOrField(whereParameter, "ApprovedDate"), Expression.Constant(Nothing))), whereParameter)

louisfr

Expression.And only takes two parameters.
You have to combine two of them.

Expression.And( 1st, Expression.And( 2nd, 3rd ))

By the way, you may want to use AndAlso instead of And.

Karen Wilson

ASKER

Thanks!! Worked perfect - after I figured out all the parenthesis placements.

Here is the code if someone is looking for the solution.
Dim wherePart = Expression.Lambda(Of Func(Of EnvrDataEntry.tbl645, Boolean))(Expression.And(Expression.Equal(Expression.PropertyOrField(whereParameter, getStuff.Item(0).sysPick), Expression.Constant("Required")),
Expression.And(Expression.Equal(Expression.PropertyOrField(whereParameter, "closedDate"), Expression.Constant("")),
Expression.NotEqual(Expression.PropertyOrField(whereParameter, "ApprovedDate"), Expression.Constant(Nothing)))), whereParameter)