VB.net LINQ filter on multiple columns

Question

Hi please can you advise me on the best way to achieve filtering on multiple columns in LINQ

Table:

CREATE TABLE [dbo].[user] (
[id] [int] IDENTITY(1,1) NOT NULL,
[firstName] [nvarchar](50) NULL,
[surname] [nvarchar](50) NULL,
[fullAddress] [nvarchar](1050) NULL

I would normally use SQL for this

Dim firstname as string = 'bob'
Dim surname as String = 'holdness'
Dim address as String = 'blockbuster street'
Dim Stmquery as string = 'Select * from users '
if not String.isnullorEmpty(firstname) or not String.isnullorEmpty(surname) or not String.isnullorEmpty(address) then 
Stmquery = Stmquery & "where"
end if
if not String.isnullorEmpty(firstname) then
Stmquery = Stmquery & " firstname = " & firstname
end if
    if not String.isnullorEmpty(surname) then
Stmquery = Stmquery & " surname = " & surname
end if
    if not String.isnullorEmpty(address) then
Stmquery = Stmquery & " address = " & address
end if

So basically if the string is empty it will show all records for that column

Can someone show me how to do this in LINQ

Thanks Paul

MarcinJuraszek · Accepted Answer · 2013-03-26 10:15:07Z

1

I assume you already have LINQ to SQL DBContext prepared, with Users table mapped.

You can easily extend your query, because it's not going to be executed against database until you call ToList(), ToArray(), First(), Last(), etc.

Dim query = dbContext.Users;

If Not String.IsNullOrEmpty(firstname) Then
    query = query.Where(Function(u) u.FirstName = firstname)
End If

If Not String.IsNullOrEmpty(surname) Then
    query = query.Where(Function(u) u.Surname = surname)
End If

If Not String.IsNullOrEmpty(address) Then
    query = query.Where(Function(u) u.Address = address)
End If

' query execution is here, after next line '
Dim results = query.ToList()

answered Mar 26, 2013 at 10:15

MarcinJuraszek

126k15 gold badges200 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VB.net LINQ filter on multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related