Karen Wilson
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("Projec t 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.reqSe rvice, 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).ColumnNa me
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.DataSourc e = 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
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("Projec
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.reqSe
Next
For Each col In dtable.Columns
Dim dc As New DataGridViewTextBoxColumn
DataGridView1.Columns.Add(
dc.HeaderText = col.ToString
dc.DataPropertyName = col.ToString
Next
For c As Integer = 1 To dtable.Columns.Count - 1
Dim col = dtable.Columns(c).ColumnNa
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.DataSourc
I'll even take a better way of doing this if I'm way off course.
Many thanks!
SuppServTable.jpg
SupportServRequire.jpg
SuppServRefTable.jpg
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.DataSourc e = 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
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.DataSourc
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
You could use reflection to get the field or property you want.
And id.GetType().GetField(suppPick.Item(0)).GetValue(id).Equals("Required")
orAnd id.GetType().GetProperty(suppPick.Item(0)).GetValue(id).Equals("Required")
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.NotSupportedExcept ion' occurred in System.Data.Linq.dll
Additional information: Method 'System.Reflection.FieldIn fo 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.
I tried the first one and got an error message.
An unhandled exception of type 'System.NotSupportedExcept
Additional information: Method 'System.Reflection.FieldIn
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.
We'll need to create the "where" part by hand.
Something like
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
Replace both occurrence of THETYPE by the type of the items of tbl645s
ASKER
I will give it a try! I tried something similar but couldn't get it to stick. Thanks.
ASKER
It's getting much closer! Here is the error.
LambdaError.jpg
LambdaError.jpg
ASKER
Code I used:
Dim whereParameter = Expression.Parameter(GetTy pe(String) )
Dim wherePart = Expression.Lambda(Of Func(Of String, Boolean))(Expression.And(E xpression. Equal(Expr ession.Pro pertyOrFie ld(wherePa rameter, "projName1"),
Expression.Constant(CStr(p Name))), Expression.Equal(Expressio n.Property OrField(wh ereParamet er, suppPick.Item(0)),
Expression.Constant("Requi red"))), whereParameter)
Dim whereParameter = Expression.Parameter(GetTy
Dim wherePart = Expression.Lambda(Of Func(Of String, Boolean))(Expression.And(E
Expression.Constant(CStr(p
Expression.Constant("Requi
Replace GetType(String) with GetType(EnvrDataEntry.tbl6 45)
And replace OfFunc(Of String, Boolean) with OfFunc(EnvrDataEntry.tbl64 5, Boolean)
And replace OfFunc(Of String, Boolean) with OfFunc(EnvrDataEntry.tbl64
ASKER
It's telling me that tbl645 cannot be used as an expression.
Where?
ASKER
Okay, I got that fixed.
In the wherePart, it doesn't like this: OfFunc(EnvrDataEntry.tbl64 5, 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))
In the wherePart, it doesn't like this: OfFunc(EnvrDataEntry.tbl64
Dim wherePart = Expression.Lambda(Of Func(EnvrDataEntry.tbl645,
ASKER
Here is the whereParameter code:
Dim whereParameter = Expression.Parameter(GetTy pe(EnvrDat aEntry.tbl 645))
Dim whereParameter = Expression.Parameter(GetTy
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
Dim req = (From id In d.tbl645s _
Where id.projName1 = CStr(pName) _
And wherePart IsNot Nothing _
Select id).ToList
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.
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
To further explain, this form
From item in list Where condition Select item
is compiled into
list.Where(Function(item) condition)
The Expression.Lambda call creates the equivalent of the lambda expression
Function(item) condition
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.
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(GetTy pe(EnvrDat aEntry.tbl 645))
This is the result of the whereParameter {System.Linq.Expressions.T ypedParame terExpress ion}
This is my wherePart
Dim wherePart = Expression.Lambda(Of Func(Of String, Boolean))(Expression.And(E xpression. Equal(Expr ession.Pro pertyOrFie ld(wherePa rameter, "projName1"),
Expression.Constant(CStr(p Name))), Expression.Equal(Expressio n.Property OrField(wh ereParamet er, suppPick.Item(0)),
Expression.Constant("Requi red"))), 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.
Here is how I have the whereParameter typed in: Dim whereParameter = Expression.Parameter(GetTy
This is the result of the whereParameter {System.Linq.Expressions.T
This is my wherePart
Dim wherePart = Expression.Lambda(Of Func(Of String, Boolean))(Expression.And(E
Expression.Constant(CStr(p
Expression.Constant("Requi
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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(E xpression. Equal(Expr ession.Pro pertyOrFie ld(wherePa rameter, getStuff.Item(0).sysPick), Expression.Constant("Requi red")),
Expression.NotEqual(Expres sion.Prope rtyOrField (wherePara meter, "ApprovedDate"), Expression.Constant(Nothin g)),
Expression.Equal(Expressio n.Property OrField(wh ereParamet er, "closedDate"), Expression.Constant(""))), whereParameter)
Working:
Dim wherePart = Expression.Lambda(Of Func(Of EnvrDataEntry.tbl645, Boolean))(Expression.And(E xpression. Equal(Expr ession.Pro pertyOrFie ld(wherePa rameter, getStuff.Item(0).sysPick), Expression.Constant("Requi red")),
Expression.NotEqual(Expres sion.Prope rtyOrField (wherePara meter, "ApprovedDate"), Expression.Constant(Nothin g))), whereParameter)
Not working:
Dim wherePart = Expression.Lambda(Of Func(Of EnvrDataEntry.tbl645, Boolean))(Expression.And(E
Expression.NotEqual(Expres
Expression.Equal(Expressio
Working:
Dim wherePart = Expression.Lambda(Of Func(Of EnvrDataEntry.tbl645, Boolean))(Expression.And(E
Expression.NotEqual(Expres
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.
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.
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(E xpression. Equal(Expr ession.Pro pertyOrFie ld(wherePa rameter, getStuff.Item(0).sysPick), Expression.Constant("Requi red")),
Expression.And(Expression. Equal(Expr ession.Pro pertyOrFie ld(wherePa rameter, "closedDate"), Expression.Constant("")),
Expression.NotEqual(Expres sion.Prope rtyOrField (wherePara meter, "ApprovedDate"), Expression.Constant(Nothin g)))), whereParameter)
Here is the code if someone is looking for the solution.
Dim wherePart = Expression.Lambda(Of Func(Of EnvrDataEntry.tbl645, Boolean))(Expression.And(E
Expression.And(Expression.
Expression.NotEqual(Expres
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.