CODEWITHSUNDEEP
c#asp.netvb.netlinqc#-4.0
Losbear
Losbear

Reputation: 3315

linq query with dynamic where clause

I'm need to do the equivalent of a t-sql statement like this using LINQ:

SELECT * FROM mytable WHERE idnumber IN('1', '2', '3')

so in LINQ I have:

Dim _Values as String = "1, 2, 3"
Dim _Query = (From m In mytable Where idnumber = _Values Select m).ToList()

Not sure what to do with _Values to make idnumber evaluate each value in the string.
Thanks in advance.

Upvotes: 0

Views: 2028

Answers (2)

Pankaj
Pankaj

Reputation: 10095

I will go for Inner Join in this context. If I would have used Contains, it would Iterate unnecessarily 6 times despite of the fact that there is just one match.

C# Version

var desiredNames = new[] { "Pankaj", "Garg" }; 

var people = new[]  
{  
    new { FirstName="Pankaj", Surname="Garg" },  
    new { FirstName="Marc", Surname="Gravell" },  
    new { FirstName="Jeff", Surname="Atwood" }  
}; 

var records = (from p in people 
               join filtered in desiredNames on p.FirstName equals filtered  
               select p.FirstName
              ).ToList();

VB.Net Version

Dim desiredNames = New () {"Pankaj", "Garg"}

Dim people = New () {New With { _
    .FirstName = "Pankaj", _
    .Surname = "Garg" _
}, New With { _
    .FirstName = "Marc", _
    .Surname = "Gravell" _
}, New With { _
    .FirstName = "Jeff", _
    .Surname = "Atwood" _
}}

Dim records = ( _
    Join filtered In desiredNames On p.FirstName = filtered).ToList()

Disadvantages of Contains

Suppose I have two list objects.

List 1      List 2
  1           12
  2            7
  3            8
  4           98
  5            9
  6           10
  7            6

Using Contains, it will search for each List-1 item in List-2 that means iteration will happen 49 times !!!

Upvotes: 3

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

Dim _Values as String = "1, 2, 3"
Dim _Query = (From m In mytable Where _Values.Split(", ").Contains(m.idnumber) Select m).ToList()

Upvotes: 1

Related Questions