Hello, how do I move an active table to archives with entity framework? Is this even practical?

Question

0.00/5 (No votes)

See more:

I am new to ASP.NET MVC development and one of the projects I am working on has the requirement that after data has been captured - the table needs to be moved to another view (archived) and then the index must be cleared to allow for new data to be captured.

What I have tried:

I am going to be completely honest in saying that I have spent the past week trying to find useful links on Google, but that has not really worked out well for me.

Posted 25-Sep-19 23:06pm

mhlongosbusiso

Updated 24-Oct-19 2:25am

Add a Solution

Comments

F-ES Sitecore 26-Sep-19 5:16am

Doing bulk operations isn't really what EF is intended for, you'd probably find it incredibly slow. You're better using stored procedures for these sorts of tasks.

mhlongosbusiso 11-Oct-19 3:56am

Hi and thank you for the reply,
I have gotten around to working with stored procedures and I am getting the required result - on the MS SQL side. It is only when I try to access my stored procedure result from the ASP.NET MVC web application upon running it that I get an error and I understand the reason that I am getting the error I just got stuck trying to fix it. I want to select only specific columns from my original database table but for some reason my model - on the MVC side - expects me to select all the columns from the original table. The error is that there's a mismatch of columns. Is there a way I can pass only the selected columns onto my MVC application without getting the error?

Thanking you in advance.

F-ES Sitecore 11-Oct-19 5:09am

When you add a reference to the stored proc in the EF designer it should create a return object for you with the right columns. If that's not working for you you can specify your own class to be used as a return type and make sure that has the right things.

https://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx

Dave Kreskowiak 26-Sep-19 8:58am

Just because you're using Entity Framework for your normal database operations, that does not mean you have to use EF for everything in the database.

You cannot use EF to do what you want. You have to execute normal SQL statements to "move" a table, or whatever your requirements for "archiving" are, and resetting the ID values.

Like F-ES said, you would be doing much better to do this archive operation in a stored procedure in the database and wrapping it all in a transaction to make sure it either app happens, or none of it happens if something goes wrong.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gerry Schmitz · Answer 1 · 2019-09-26T04:33:00

Same entity type, just another "instance" of that table / record (the "archive").

(You typically archive a "record" or records; not the whole table).

Create a "production" and an "archive" data context, and "move" data between them (while "truncating" the production table(s) for the next iteration).

Inevitably, someone wants to "restore" an archive (to production) for whatever reason.

I would suggest as an extra step, separate production and archive "data bases" (besides the separate data contexts); that way you "keep all the names the same".

mhlongosbusiso · Answer 2 · 2019-10-24T02:25:00

Good day all,

After spending a lot of time trying to understand how stored procedures work, I am now able to get the desired results from the stored procedure and onto my ASP.NET application. There is just one little issue, though, it's actually a new question more than an issue - I understand that I can have more than one SELECT statements within the same stored procedure and I have played around with that functionality (on MSSQL) and the result came back as expected but now - when I call my stored procedure onto the ASP.NET application it seems to fetch only the result of the first select statement and just ignores the other. Both my SELECT statements get data from the same table and just manipulate it differently, now what I want is to display the results from both my SELECT statements on the same view. Is this is even practical? If so, I would appreciate pointers and if not I'd be happy to know and then that way I can perhaps start looking for new ways of achieving my goal.

Thanking you in advance,
S'busiso

Hello, how do I move an active table to archives with entity framework? Is this even practical?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0