How to make a where in clause similar to one in SQL Server?

I made one by myself but can anyone please improve this?

 public List<State> Wherein(string listofcountrycodes) { string[] countrycode = null; countrycode = listofcountrycodes.Split(','); List<State> statelist = new List<State>(); for (int i = 0; i < countrycode.Length; i++) { _states.AddRange( from states in _objdatasources.StateList() where states.CountryCode == countrycode[i].ToString() select new State { StateName = states.StateName }); } return _states; }

0

8 Answers

This expression should do what you want to achieve.

dataSource.StateList.Where(s => countryCodes.Contains(s.CountryCode))

2

This will translate to a where in clause in Linq to SQL...

var myInClause = new string[] {"One", "Two", "Three"};
var results = from x in MyTable where myInClause.Contains(x.SomeColumn) select x;
// OR
var results = MyTable.Where(x => myInClause.Contains(x.SomeColumn));

In the case of your query, you could do something like this...

var results = from states in _objectdatasource.StateList() where listofcountrycodes.Contains(states.CountryCode) select new State { StateName = states.StateName };
// OR
var results = _objectdatasource.StateList() .Where(s => listofcountrycodes.Contains(s.CountryCode)) .Select(s => new State { StateName = s.StateName});

1

I like it as an extension method:

public static bool In<T>(this T source, params T[] list)
{ return list.Contains(source);
}

Now you call:

var states = _objdatasources.StateList().Where(s => s.In(countrycodes));

You can pass individual values too:

var states = tooManyStates.Where(s => s.In("x", "y", "z"));

Feels more natural and closer to sql.

7

public List<Requirement> listInquiryLogged()
{ using (DataClassesDataContext dt = new DataClassesDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString)) { var inq = new int[] {1683,1684,1685,1686,1687,1688,1688,1689,1690,1691,1692,1693}; var result = from Q in dt.Requirements where inq.Contains(Q.ID) orderby Q.Description select Q; return result.ToList<Requirement>(); }
}

2

The "IN" clause is built into linq via the .Contains() method.

For example, to get all People whose .States's are "NY" or "FL":

using (DataContext dc = new DataContext("connectionstring"))
{ List<string> states = new List<string>(){"NY", "FL"}; List<Person> list = (from p in dc.GetTable<Person>() where states.Contains(p.State) select p).ToList();
}

from state in _objedatasource.StateList()
where listofcountrycodes.Contains(state.CountryCode)
select state

This little bit different idea. But it will useful to you. I have used sub query to inside the linq main query.

Problem:

Let say we have document table. Schema as follows schema : document(name,version,auther,modifieddate)composite Keys : name,version

So we need to get latest versions of all documents.

soloution

 var result = (from t in Context.document where ((from tt in Context.document where t.Name == tt.Name orderby tt.Version descending select new {Vesion=tt.Version}).FirstOrDefault()).Vesion.Contains(t.Version) select t).ToList();

public List<State> GetcountryCodeStates(List<string> countryCodes)
{ List<State> states = new List<State>(); states = (from a in _objdatasources.StateList.AsEnumerable() where countryCodes.Any(c => c.Contains(a.CountryCode)) select a).ToList(); return states;
}

1