Hod Do I change a where in a linq by code?

Question

0.00/5 (No votes)

See more:

Hi guys,
I need something that allow me to change a where clause in code.
For example, in the follow code,
if IDtipo <>0, i can do the second linq directly.
Any helps?
Thank you

Shared Function getTipologiaCanone(ByVal IDTipo As Integer) As List(Of ConfiguratoreCanoniFissi.CanoniTipo)
    Dim lista_canoni As List(Of ConfiguratoreCanoniFissi.CanoniTipo)
    Using dbContext As New P2000ProdwareEntities
        lista_canoni = (From c In dbContext.CanoniTipoes
                        Select c).ToList

        If IDTipo <> 0 Then
            lista_canoni = (From c In lista_canoni
                          Where c.id_tipo = IDTipo
                          Select c).ToList
        End If

    End Using
    Return lista_canoni
End Function

Posted 27-Jul-15 2:14am

gregorio89

Add a Solution

2 solutions

Solution 1

One way is that you isolate the changing part of the where clause to your own method where you control if the item is included in the result or not. In this kind of approach the LINQ query structure stays the same.

Consider the following example:

Data class

VB

Public Class MyItem
   Public Property BoolValue As Boolean
   Public Property TextValue As String
End Class

Extension method to decide if the item is included or not

VB

Module ItemExtension
   <System.Runtime.CompilerServices.Extension()>
   Public Function IsValid(ByVal myItem As MyItem, textStart As String) As Boolean
      IsValid = myItem.TextValue.StartsWith(textStart) And myItem.BoolValue = False
   End Function
End Module

And a test run

VB

Dim myList As List(Of MyItem) = New List(Of MyItem)
Dim myItem As MyItem
Dim resultList As System.Collections.Generic.IEnumerable(Of MyItem)

myItem = New MyItem
With myItem
   .BoolValue = False
   .TextValue = "First"
End With
myList.Add(myItem)
myItem = New MyItem
With myItem
   .BoolValue = True
   .TextValue = "Second"
End With
myList.Add(myItem)

resultList = From i In myList
             Where i.IsValid("Fi") = True
             Select i

So basically you could pass whatever parameters you like to the extension method and apply the changing logic inside of it.

Posted 27-Jul-15 3:29am

Wendelius

Updated 27-Jul-15 3:30am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2015-07-27T03:38:00

Solution 2

One of the benefits of LINQ is that you can build your query up in stages. If you avoid calling ToList until the end, then your query will be translated into an efficient database query, and only the relevant records will be loaded into memory.

VB.NET

Shared Function getTipologiaCanone(ByVal IDTipo As Integer) As List(Of ConfiguratoreCanoniFissi.CanoniTipo)
    Using dbContext As New P2000ProdwareEntities
        Dim lista_canoni As IQueryable(Of ConfiguratoreCanoniFissi.CanoniTipo)
        lista_canoni = dbContext.CanoniTipoes
        
        If IDTipo <> 0 Then
            lista_canoni = From c In lista_canoni
                Where c.id_tipo = IDTipo
                Select c
        End If
        
        Return lista_canoni.ToList()
    End Using
End Function

Posted 27-Jul-15 3:38am

Richard Deeming

Comments

gregorio89 28-Jul-15 6:01am

thank you Richard, and what if I have another condition?
for example
If IDTipo <> 0 Then
lista_canoni = From c In lista_canoni
Where c.id_tipo = IDTipo
Select c
End If

if idCasa<> 0
previus linq + condition
End If

Richard Deeming 28-Jul-15 7:46am

That's easy:

If IDTipo <> 0 Then
lista_canoni = From c In lista_canoni
Where c.id_tipo = IDTipo
Select c
End If

if idCasa<> 0
lista_canoni = From c In lista_canoni
Where c.id_casa = idCasa
Select c
End If

The final result will only include records where all of the conditions are true.

Since the list_canoni variable is IQueryable, all of the conditions will be passed to SQL. You'll end up with a query that looks something like:

SELECT ...
FROM CanoniTipoes
WHERE id_tipo = @p0
AND id_casa = @p1

gregorio89 29-Jul-15 2:36am

ok this,
I mean some like

If IDTipo <> 0 Then
lista_canoni = From c In lista_canoni
Where c.id_tipo = IDTipo
Select c
End If

if idCasa<> 0
lista_canoni + WHERE id_tipo = @p0.

how can I do This?

Richard Deeming 29-Jul-15 7:20am

In exactly the same way that I showed you in my previous comment.

gregorio89 29-Jul-15 8:04am

sorry I haven't seen.
thank you