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

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?

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 + "}");