we have relational db (sql server 2014, 30 tables, normalized). application built using web.api/angularjs
we need implement dynamic search - user may select few fields several entities; resulting search pseudo-code might like:
searchconditions ( {company.name} <contains> "enterprise" ) , ( {customer.state} <equals> "mo" or {customer.state} <equals> "tx" ) , ( {company.annualsales} <lessthan> "123456.00" ) return company.name,customer.name group state, company the structure of search stay same - bunch of conditions connected "and"; each condition might have few "or" items inside.
we using entity framework;
so far see several ways implement functionality:
1) build sql query , run against db (quite cumbersome)
2) create ef iqueryable object , data using ef (somewhat easier, afraid resulting sql awful)
3) use lucene.net (it require solr server, i'd avoid if possible)
4) create denormalized olap instance of our db , use approach (1) or (2)
i pretty sure there must other ways; after all, ef/.net/sql combination not rare; don't want reinvent wheel if can avoid it. ideal solution parse search string, search , required fields (paging plus well)
would ssas/ssrs help?
although solutions have mentioned work, recommend elasticsearch.
elasticsearch document store built on top of lucene (you not need run separate solr server though) , easy run , install. there nice .net api called nest. name mentions built searching. , has paging built in among many other features.
you can quite store search terms in db , return ids applicable search query. use store our data documents there lots of people using searching.
you literally copy exe on machine , run it. have rest api allows communicate it. nest abstracts this.
you can built dynamic queries using nest adding onto search object or if want can built json object pass in.
