How to use ObjectQuery with Where filter separated by OR clause

Question

Hi guys,

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

Another solution for this - we can use Sub Query inside main Query it allows to make selection from subsequence with needed condition . For example :

var db = CustomEntity();

ObjectQuery<Categories> query1 = db.Categories.Where("it.CategoryName='Demo'").Select ("it.CategoryID");
var categorySQL = query1.ToTraceString().Replace("dbo", "CustomEntity"); // E-SQL need this syntax
ObjectQuery<Products> query2 = db.Categories.Where("it.CategoryID = (" + categorySQL + ")");

Some example is here :

http://msdn.microsoft.com/en-us/library/bb896238.aspx

Good luck!