CODEWITHSUNDEEP
c#linqlinq-to-sql
frenchie
frenchie

Reputation: 52017

Checking if a table contains values in a list

I have a parameter list of strings and I want to write a query that returns a list of strings that contain values of the parameter list that are present in the table. I have the following query but I can't get the where clause:

List<string> TheListParameter = new List<string>{"string1", "string2", "string3"};

var TheOutput = (from t in MyDC.SomeTable        
                 where t.SomeColumn.Contains(TheListParameter) 
                 select t.SomeColumn).ToList();

How do I write the where clause for this search query?

Upvotes: 2

Views: 2346

Answers (4)

Thomas Andre&#232; Wang
Thomas Andre&#232; Wang

Reputation: 3429

LINQ Oneliner

List<string> TheListParameter = new List<string>{"string1", "string2", "string3"};

var foundElements = MyDC.SomeTable.Where(a=> TheListParameter.Contains(a.SomeColumn)).Select(a=> a.SomeColumn);

Why everyone throws on the ToList() is beside me. It all depends on if you need all the data realized or not.

Take this example. 

using(dbCtx myDc = new dbCtx()){
    var unrealizedList = myDc.someEntity;
    var realizedList = myDc.someEntity.ToList();

    var a1 = unrealizedList.First() //Works 
    var b1 = realizedList.First() //Works 
}
var a2 = unrealizedList.First() //Fails as unrealizedList is empty (not loaded and context is gone)
var b2 = realizedList.First() //Works because the realizedList was loaded completely by the ToList()

Now if you have a global or local (not a using) Ctx then you might never need ToList() albeit using using is a nice and clean way of gaining control over your context which can be a problem when you have several contexts working on eachother.

A tiny pointer on ToList() mid query.

var a = myDc.someEntity.Where(a=> a.someDate.DayOfYear == 123); 
// Fails as DayOfYear cannot be translated to  SQL expression

Here you need to project the data before you filter it, and sadly all the data needs to be loaded to do so

var a = myDc.someEntity.ToList().Where(a=> a.someDate.DayOfYear == 123); 
// Works as the data no longer is filtered in SQL (the SQL is "Select * from someEntity")

So if you can, perform your filtering before the ToList() to limit the amount of data pulled from the DB.

Instead if ToList() you can use other lists aswell like ToArray()

Upvotes: 1

Alper Tunga Arslan
Alper Tunga Arslan

Reputation: 579

Try this:

List<string> TheListParameter = new List<string>{"string1", "string2", "string3"};
var result = MyDC.SomeTable.ToList().Select(l => l.SomeColumn)
                           .Where(l =>TheListParameter.Contains(l.SomeColumn)).ToList();

Upvotes: 0

Salah Akbari
Salah Akbari

Reputation: 39966

Based on Contain's Documentation the parameter for Contains could be string not a list of string. So it should be like this:

List<string> TheListParameter = new List<string> {"string1", "string2", "string3"};

var TheOutput = (from t in MyDC.SomeTable
                 where TheListParameter.Contains(t.SomeColumn.ToString())
                 select t.SomeColumn).ToList();

Or if you want to return a boolean result if MyDC.SomeTable.SomeColumn contains one of the TheListParameter's strings you can try something like this:

bool hasSameElements = MyDC.SomeTable.ToList()
                      .Select(c => c.SomeColumn)
                      .Intersect(TheListParameter).Any(); //True or False

Upvotes: 1

tchelidze
tchelidze

Reputation: 8318

Try following 

  List<string> TheListParameter = new List<string>{"string1", "string2", "string3"};

  var TheOutput = (from t in MyDC.SomeTable
                   where TheListParameter.Any(e => e == t.SomeColumn)
                   select t.SomeColumn).ToList();

Upvotes: 1

Related Questions