We have relational db (SQL Server 2014, about 30 tables, very normalized). The application is built using Web.API/AngularJS
We need to implement dynamic search - user may select a few fields from several entities; the resulting search pseudo-code might look like:
SearchConditions
(
{Company.Name} <Contains> "Enterprise"
)
AND
(
{Customer.State} <Equals> "MO"
OR
{Customer.State} <Equals> "TX"
)
AND
(
{Company.AnnualSales} <LessThan> "123456.00"
)
Return
Company.Name,Customer.Name
Group By State, Company
The structure of the search will stay the same - bunch of conditions connected by "AND"; each condition might have a few "OR" items inside.
We are using Entity Framework;
so far I see several ways to implement this functionality:
1) build SQL query and run it against db (quite cumbersome)
2) create EF IQueryable object and get data by using EF (somewhat easier, but I am afraid that resulting SQL will be awful)
3) use Lucene.NET (it will require Solr server, I'd like to avoid it if possible)
4) create denormalized OLAP instance of our db and use approach (1) or (2)
I am pretty sure there must be other ways; after all, EF/.NET/SQL combination is not so rare; I don't want to reinvent the wheel if I can avoid it. The ideal solution would parse search string, do the search and get back required fields (paging would be a good plus as well)
Would SSAS/SSRS help?
