LINQ - combining multiples criterias with form fields

Question

Hi,

 

I'm new to .net (VB2008) . I saw a bunch of LINQ tutorial videos, but not this kind of situation.

 

How can move this VB6 query to LINQ TO SQL. There's a lot of input coming from a searching form's fields (text and combo boxes) and uses multiples combining criterias.

 

Example:

 

'Begins SELECT.
    strSqlCrit = "SELECT query1.* FROM query1 " _
                 & "WHERE query1.CodigoDoProcesso Is Not Null"
   
'Testing and adding criteria to WHERE.
    With Me
        If .cboUnidade.Text <> "" Then
            strSqlCrit = strSqlCrit & " AND LocalizacaoAtual=" & "'" & .cboUnidade.Text & "'"
        End If

        If .cboStatus.Text <> "" Then
            strSqlCrit = strSqlCrit & " AND StatusDoProcesso=" & "'" & .cboStatus.Text & "'"
        End If

        If Not IsNothing(.cboAno.Text) Then
            If Not IsNothing(.cboMes.Text) Then
                strMesAno = .cboMes.Text & .cboAno.Text
                strSqlCrit = strSqlCrit & " AND MesAno=" & "'" & strMesAno & "'"
            Else
                strSqlCrit = strSqlCrit & " AND AnoDoc=" & "'" & .cboAno.Text & "'"
            End If
        End If

 

        If Not IsNothing(.txtAssunto.Text) Then
            strSqlCrit = strSqlCrit & " AND DocumentacaoTipo Like " _
                             & Chr(39) & Chr(37) _
                            & .txtAssunto.Text & Chr(37) & Chr(39)
        End If

  End with

'Open recordset

   Dim rst As ADODB.Recordset

    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.Open strSqlCrit, conLocal, adOpenKeyset, adLockReadOnly, adCmdText

           do something....

    rst.Close
    Set rst = Nothing

 

Thanks in advance

Answer 1

Hallo  Flaviomd!

 

I'v posted a message just in 1 minute after yours.

It can be an answer. It refers to

http://www.codeproject.com/KB/linq/rewrite_linq_expressions2.aspx

I see no problem to use my c# libraries from your vb project.

 

The idea is:

1. you write a "normala" query and add one additional dummy condition to the wrere clause.

2. For each condition you may need to insert, you write a function that returns

   Nothing (if the cond is not needed) or a lambda expression.

3. Call FilterBuilder.And() from my lib to combine all lambdas just obtained. .

4.. call WhereRewriter.Rewrite() to replace the dummy condition for the result of FilterBuilder.And().

 

All my examples are in c# -- well, vb is for me also not that transparent.

I mean,it's difficult for me to answer you in vb.

I can't promiss you that you solve the problem in 5 min, but it should be makeable.

 

Regards, Dima.

Answer 2

Thanks, Dima

 

I'll check it out