1

I am in charge of design an application (.NET) that has these features:

  • It´s a desktop app (maybe in the future will be web-based)
  • It´s functionality is to provide users analyzed data retrieved from database. It is done by performing heavy queries generating , graphs and grid-tables to show the information. Users analyze the data to make decisions. The data is stored in relational tables and needs to be treated before being shown. The information is related to statistics, accounting, etc
  • Queries are not too much complicated. Multiple Joins with GroupBy,Sum and other calculations will be the worst situation.
  • Database will be migrated from Oracle => SQL Server
  • The model is related to assembling cars. It stores information about the items that compose a car (price, model, etc). Also stores info about providers, disccounts.
  • Domain logic will be able to calculate budgets to some specific cars in one click, perfoming a lot of queries and calculations.

My doubts/questions are:

A) I want to put all the logic inside the code, avoiding the use of stored procedures in the database. I don't like he fact they can not get tested with "generic code" (which will work despite the db engine) and their appearance of being a black-box outputting data. Also i dont want , changes of database engine affects the app too much. I am right? I prefer to have them implemented in LINQ or other approach you think could fit. Do you see some disadvantage on that? Probably memory issues? ( The managed data contains thousands of records with dozens of tables)

B) The queries logic should go in the domain layer. Which will be a good pattern/approach to place them in a scalable way? The number of queries will increase during app maintenance.

C) Do you know a nice approach to unit test the queries in LINQ? Fake data, moqs, etc? Some advice on that?

Improve this question
10
  • I think we need a bit more details : the query will obvisouly run against thousands of records, but what do the user get ? A grid with rows that you can load only partially and get the rest on the fly ? A report generated with all the data ? And does the complexity of the requests is high enough to eventually rely only on raw SQL or are you sure LINQ and not too complex queries will do the job ?
    – Walfrat
    Commented May 16, 2018 at 11:22
  • 1
    No answer to your question, but it is wrong that you can't test stored procedures. For example, in some companies their ETL resides mostly inside the database and is done with stored procedures. You can bet they are tested as well. Just directly on the database with SQL code (for example).
    – kutschkem
    Commented May 16, 2018 at 11:22
  • 1
    It is in fact possible to unit test stored procedures in SQL Server
    – Robbie Dee
    Commented May 16, 2018 at 11:57
  • But that aside, I suspect the bottleneck will be getting the data over to the mid tier or wherever you're going to be doing your number crunching.
    – Robbie Dee
    Commented May 16, 2018 at 12:07
  • Few users doing heavy queries to a grid does not sound like a big problem. I would try to make a naive implementation and make certain the grid supports paging. It also sounds like you can read uncomitted. I would also not use stored procedures, and my personal experience is that linq to entities is faster in .net core than 4.6
    – Thomas Koelle
    Commented May 16, 2018 at 12:10

1 Answer 1

Reset to default
1

For any kind of reporting you should create a data warehouse with cubes and like and query that rather than the underlying transactional databases.

Often these data warehouses will come with their own reporting tools, it can often be best just to use those rather than develop a system on top of them.

However, If you need to push those reports out to external customers then you will want to restrict access. In this case you application can run the MDX (or alternate tech) query against the cue and display the report in a customer charting component or grid.

In either case you are reducing the required logic by using a data warehouse which will flatten the data first, and keeping that logic in the form of a query rather than in code.

Improve this answer
10
  • the database is not only for reporting. As an example: the database will store wich pieces compose a CAR . Each model of car will have their own set of pieces, each one with its price, provider, etc etc. We want to query that data for generating budgets with one click. The budgets should query price of each item, also apply some disccounts based on some calculations of other facts like driver experience, accidents, relations with providers etc (huge etc). Do you think that fits a data wharehouse?
    – X.Otano
    Commented May 16, 2018 at 12:50
  • hmm it could do "budget" is an odd word to use if you mean "the cost of a single car assembled from these parts"
    – Ewan
    Commented May 16, 2018 at 13:21
  • yes , you are right with the meaning of budget
    – X.Otano
    Commented May 16, 2018 at 13:22
  • if you mean "the average cost of cars by model calculated by summing the part cost from various suppliers, broken down per month" then yes definitely
    – Ewan
    Commented May 16, 2018 at 13:22
  • No, we want the budget to be generated for a new car driver with the actual data (not historical, or reports, just the active data today) . Reporting is another piece of the app. But main work will be to analize and provide an instant budget
    – X.Otano
    Commented May 16, 2018 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.