How to use ObjectQuery with Where filter separated by OR clause

Question

Could somebody help me to answer how to rewrite raw SQL filter WHERE (...) OR (...) with ObjectQuery bilder, please?

String queryRaw = "SELECT ls.LocaleName, ls.IsActive, ls.LocaleDescription " +
                  "FROM RoutesEntities.Locales AS ls ";

                  //" WHERE ls.LocaleName = 'en' OR ls.LocaleName = 'de' "

this._queryData = new ObjectQuery<DbDataRecord>(queryRaw, routesModel);

I would use Where() method, but it generates where clauses separated by AND, though i want to use OR instead. Is it possible with QueryBilder? I mean how to use this to generate "OR separated" filter :

Where("it.LocaleName IN (@localeName)", new ObjectParameter("localeName", String.Join(",", localeName)))

Thanks, Artem

Answer 1

It is happening again, i answer my question myself. Thanks for this.

Here is the answer :

ObjectQuery as EntityCommand DO NOT SUPPORT "IN" CLAUSE YET ... it means that there is no opportunity to use WHERE IN filter for query sending to DB until you use already selected DataSet from DB. So only LINQ methods can do this and only with selected List<>

Though, there is an alternative not so clear but effective decision - you can use multiple OR conditions in your query and they work fine for me :

ObjectQuery<DbDataRecord> query = query.Where("it.RouteID=1 OR it.RouteID=2");

Hope this will help someone ... enjoy :)

Answer 2

ObjectQuery as EntityCommand SUPPORT "IN" CLAUSE.

Logical operators of ObjectQuery:

ObjectQuery allows you to join together multiple where conditions.

• By default, the conditions are combined using an AND parameter. To place an OR operator between conditions, call the Or() method between the given Where methods. You can also explicitly call the And() method if you wish to make the code of your conditions clearer and easier to read.

Ex: // Retrieves users whose first name is "Joe" or last name is "Smith".

var whereQuery = UserInfoProvider.GetUsers()

.Where("FirstName", QueryOperator.Equals, "Joe")
.Or()
.Where("LastName", QueryOperator.Equals, "Smith");

Refer More: https://docs.kentico.com/display/K82/Retrieving+database+data+using+ObjectQuery+API

Answer 3

ObjectQuery as EntityCommand SUPPORT "IN" CLAUSE.The syntex is

ObjectQuery<SampleProduct> s = db.SampleProducts;
s.Name = "SampleProductName";
string strIDList = "10,20,30";
s= s.Where("SampleProductName.ID IN {" + strIDList + "}");

Answer 4

You can use the IN clause in an ObjectQuery. You just need to use { instead of (.

E.g."it.ID IN {4,5,6}" instead of "it.ID IN (4,5,6)"

All credit for this answer comes from 'Contains()' workaround using Linq to Entities?