0

Hello and thanks for looking.

I have a DAL question for an application I'm working on. The app is going to extract some data from 5-6 tables from a production RDBMS that serves a much more critical role in the org. What the app has to do is use the data in these tables, analyze, apply some business logic/rules and then present.

The restrictions are that since the storage model is critical in nature to the org, I need to restrict how the app will request the data. Since the tables are relatively small, I created my data access to use DataTables to load the entirety of the db tables on a fixed interval using a timer.

My questions are really around my current design and the potential use of EF or LINQtoSQL

  1. Can EF/LS work around the restrictions of the RDBMS. Most tutorials I've seen, the storage exists solely for the application. Can access to the storage be controlled and/or can EF use DataTables rather than An RDBMS?

  2. Since the entirety of the tables are going to be loaded, is there a best practice for creating classes to consume the data within these tables? I will have to do in memory joins and querying/logic to get at the actual data I need.

Sorry if I'm being generic. I'm more just looking for thoughts and opinions as opposed to a solution to my problem. Please done hesitate to share your thoughts. Thanks.

FerrisUML
  • 11
  • 3

1 Answers1

0

For your first question, yes Entity Framework can use a existing DB as it's source, the term to search for when looking for Entity Framework tutorials on this topic is called "Database First"

For your second question let me first preface it with a warning: many ORMs are not designed around using it to load the entire data table and do bulk operations on them, especially if you will be modifying the result set and pushing the data back to the server in large quanties. The updates will be row based not set based because you did the modifications in C# code, not in a T-SQL query. Most ORMs are built around the expectation that you will be doing CRUD operations on the row level, not ETL operations or set level CRUD operations (except for Read which most ORMs will do as a set operation).

If you will not be updating the data, only pulling out using Entity Framework and building reports and whatnot off of the data you should be fine. If you are bulk inserting in to the database, things get more problematic. See this SO question for more information.

Community
  • 1
  • 1
Scott Chamberlain
  • 124,994
  • 33
  • 282
  • 431
  • Scott, first, thanks for responding. The application will not modify any data, only apply some logic on existing data and present. Can access be controlled (how/when) within an ORM like EF? Also, you say they're not designed to load the entirety of a table into memory, but can they use a DataTable as a source? – FerrisUML Dec 13 '13 at 18:21
  • I don't understand the question, you mean like security (who can access the database and who can't?) – Scott Chamberlain Dec 13 '13 at 18:24
  • I mean limit or restrict how often the ORM accesses the DB. The DB that supports this app is a real time critical business RDBMS and the client is concerned about limited access to reduce deadlocks and any IO contention. – FerrisUML Dec 13 '13 at 18:27
  • No, that is not built in to EF or any ORM. If you could connect with `SqlConnection` you could connect with Entity Framework, however you could easily put those restrictions in to the logic of the program yourself. – Scott Chamberlain Dec 13 '13 at 18:29
  • If responsiveness is a concern the normal way to handle this is you have a 2nd Read Only database on a different physical server that is a mirror of the primary database. You then that you run your intensive long running report queries against that 2nd database and it does not affect the load on the main database. There are many options for keeping the databases in sync [Replication](http://technet.microsoft.com/en-us/library/ms151198.aspx), [Log Shipping](http://technet.microsoft.com/en-us/library/ms187103.aspx), or even just simply restoring the backup the main makes every time it makes one. – Scott Chamberlain Dec 13 '13 at 18:34